Excel form (Block fields)

Question

I have a question about what formula I can use.

I have a box with 3 options (Example: 1 , 2 , 3) and if I select the option 1 I can fill in the field 1 and are blocked options 2 and 3. If you select the option 2 I can fill in the field 2 and are blocked options 1 and 3.

I know it’s something simple, but I can’t remember how to do it. In case you can give me some ideas I’d like to thank you.

Answer 1

Wouldn’t it be simpler to replace the box with a cell that only accepts options 1, 2 or 3? Below there could be a small table explaining what the 3 options are.

Answer 2

This is possible with the use of VBA.

After inserting the Optionbutton1, Optionbutton2 and Optionbutton3 option buttons. The following code can be inserted in the form:

Private Sub UserForm_Initialize()
    OptionButton1 = False
    OptionButton2 = False
    OptionButton3 = False
End Sub

Private Sub OptionButton1_Change()
  TextBox1.Locked = False
  TextBox1.Visible = True
  Label1.Visible = True

  TextBox2.Locked = True
  TextBox2.Visible = False
  Label2.Visible = False

  TextBox3.Locked = True
  TextBox3.Visible = False
  Label3.Visible = False
End Sub

Private Sub OptionButton2_Change()
  TextBox2.Locked = False
  TextBox2.Visible = True
  Label2.Visible = True

  TextBox1.Locked = True
  TextBox1.Visible = False
  Label1.Visible = False

  TextBox3.Locked = True
  TextBox3.Visible = False
  Label3.Visible = False
End Sub

Private Sub OptionButton3_Change()
  TextBox3.Locked = False
  TextBox3.Visible = True
  Label3.Visible = True

  TextBox2.Locked = True
  TextBox2.Visible = False
  Label2.Visible = False

  TextBox1.Locked = True
  TextBox1.Visible = False
  Label1.Visible = False
End Sub

The form has 3 Labels, 3 Textboxes and 3 Optionbuttons, as in the following image:

EDIT:

To block the cell with option button in the Spreadsheet, each macro must be assigned to each option button.

Sub Botãodeopção1_Clique()
  Worksheets("Planilha1").Range("B1").Locked = False
  Worksheets("Planilha1").Range("B2").Locked = True
  Worksheets("Planilha1").Range("B3").Locked = True
End Sub
Sub Botãodeopção2_Clique()
  Worksheets("Planilha1").Range("B1").Locked = True
  Worksheets("Planilha1").Range("B2").Locked = False
  Worksheets("Planilha1").Range("B3").Locked = True
End Sub
Sub Botãodeopção3_Clique()
  Worksheets("Planilha1").Range("B1").Locked = True
  Worksheets("Planilha1").Range("B2").Locked = True
  Worksheets("Planilha1").Range("B3").Locked = False
End Sub

EDIT2:

Using an Activex Control Combination Box and Changing the Property ListFillRange for the data location, use this code:

Private Sub ComboBox1_Change()
    'Declarar Planilha
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Planilha1")

    If ComboBox1.Value = 1 Then
        With ws
            .Unprotect Password:="SuaSenha"
            .Cells.Locked = False
            .Cells.FormulaHidden = False
            Range("B2").Locked = True
            Range("B2").Value = 0
            Range("B3").Locked = True
            Range("B3").Value = 0
            .Protect Password:="SuaSenha"
        End With
    ElseIf ComboBox1.Value = 2 Then
        With ws
            .Unprotect Password:="SuaSenha"
            .Cells.Locked = False
            .Cells.FormulaHidden = False
            Range("B1").Locked = True
            Range("B1").Value = 0
            Range("B3").Locked = True
            Range("B3").Value = 0
            .Protect Password:="SuaSenha"
        End With
    ElseIf ComboBox1.Value = 3 Then
        With ws
            .Unprotect Password:="SuaSenha"
            .Cells.Locked = False
            .Cells.FormulaHidden = False
            Range("B1").Locked = True
            Range("B1").Value = 0
            Range("B2").Locked = True
            Range("B2").Value = 0
            .Protect Password:="SuaSenha"
        End With
    End If
End Sub

Where Locks desired cells depending on the value entered in the Combobox.