Excel form (Block fields)

Navigation
-1

I have a question as to which 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 the > options 2 and 3 . If you select option 2 , I can fill in the field 2 and the options 1 and 3 options are blocked.

I know it's something simple but I do not remember how to do it. If you can give me some ideas, thank you.

    
asked by anonymous 21.12.2017 / 13:13

2 answers

0

This is possible with the use of VBA.

After you insert the OptionButton1, OptionButton2, and OptionButton3 radio buttons. The following code can be inserted into 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:

Toblockthecellwiththeoptionbuttonontheworksheet,eachmacromustbeassignedtoeachradiobutton.

SubBotã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 ListFillRange property to 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

Lock the desired cells depending on the value entered in the ComboBox.

    
21.12.2017 / 16:06
0

Would not it be simpler to replace the box with a cell that only accepted options 1, 2, or 3? Below could be a small table explaining what the 3 options are.

    
27.12.2017 / 19:46
Change next word style via css I wanted to make a magic square, where I put 9 numbers, and the sum of them has to result 15 (in the horizontal and vertical)