Validaciónde combox con optionbutton en userform
Buen día.Sr Dante, le escribo para que me ayude en la siguiente validación pendiente en userform, me explico tengo un userform de búsqueda y lo que necesito es que el combox1 y combox2 aparecescan bloqueados y una vez haciendo click el el optionbutton1 se desbloquee combox1, (permaneciendo el combox2 bloqueado), y haciendo click en optionbutton 2 se desbloquee combox2 y se bloquee combox 1 y viceversa,, no se si me explique bien,, muchas gracias saludos
esta la macro hecha por dante:
Private Sub ComboBox1_Change()
'Act.Por.Dante Amor
If ComboBox1 = "" Then Exit Sub
cargar ComboBox1.ListIndex + 2
End Sub
Private Sub ComboBox2_Change()
'Act.Por.Dante Amor
If ComboBox2 = "" Then Exit Sub
cargar1 ComboBox2.ListIndex + 2
End Sub
Sub cargar(fila)
'Act.Por.Dante Amor
Set h1 = Sheets("alumnos")
TextBox1 = h1.Cells(fila, 1)
TextBox2 = h1.Cells(fila, 2)
TextBox3 = h1.Cells(fila, 3)
TextBox4 = h1.Cells(fila, 4)
TextBox5 = h1.Cells(fila, 5)
TextBox6 = h1.Cells(fila, 6)
TextBox7 = h1.Cells(fila, 7)
TextBox8 = h1.Cells(fila, 8)
TextBox9 = h1.Cells(fila, 9)
TextBox10 = h1.Cells(fila, 10)
TextBox11 = h1.Cells(fila, 11)
TextBox12 = h1.Cells(fila, 12)
TextBox13 = h1.Cells(fila, 13)
TextBox14 = h1.Cells(fila, 14)
TextBox15 = h1.Cells(fila, 15)
TextBox16 = h1.Cells(fila, 16)
TextBox17 = h1.Cells(fila, 17)
TextBox18 = h1.Cells(fila, 18)
End Sub
Sub cargar1(fila)
Set h2 = Sheets("alumnos")
TextBox1 = h2.Cells(fila, 1)
TextBox2 = h2.Cells(fila, 2)
TextBox3 = h2.Cells(fila, 3)
TextBox4 = h2.Cells(fila, 4)
TextBox5 = h2.Cells(fila, 5)
TextBox6 = h2.Cells(fila, 6)
TextBox7 = h2.Cells(fila, 7)
TextBox8 = h2.Cells(fila, 8)
TextBox9 = h2.Cells(fila, 9)
TextBox10 = h2.Cells(fila, 10)
TextBox11 = h2.Cells(fila, 11)
TextBox12 = h2.Cells(fila, 12)
TextBox13 = h2.Cells(fila, 13)
TextBox14 = h2.Cells(fila, 14)
TextBox15 = h2.Cells(fila, 15)
TextBox16 = h2.Cells(fila, 16)
TextBox17 = h2.Cells(fila, 17)
TextBox18 = h2.Cells(fila, 18)
End Sub
Private Sub CommandButton3_Click()
'Act.Por.Dante Amor
Set h1 = Sheets("alumnos")
If TextBox1 = "" Or TextBox2 = "" Or TextBox3 = "" Or TextBox4 = "" Then
MsgBox "Campos requeridos vacíos favor complete"
TextBox2.SetFocus
Exit Sub
End If
'
If ComboBox1 <> "" Then f = ComboBox1.ListIndex + 2
If ComboBox2 <> "" Then f = ComboBox2.ListIndex + 2
h1.Cells(f, "A") = TextBox1
h1.Cells(f, "B") = TextBox2
h1.Cells(f, "C") = TextBox3
h1.Cells(f, "D") = TextBox4
h1.Cells(f, "E") = TextBox5
h1.Cells(f, "F") = TextBox6
h1.Cells(f, "G") = TextBox7
h1.Cells(f, "H") = TextBox8
h1.Cells(f, "I") = TextBox9
h1.Cells(f, "J") = TextBox10
h1.Cells(f, "K") = TextBox11
h1.Cells(f, "l") = TextBox12
h1.Cells(f, "M") = TextBox13
h1.Cells(f, "N") = TextBox14
h1.Cells(f, "O") = TextBox15
h1.Cells(f, "P") = TextBox16
h1.Cells(f, "Q") = TextBox17
h1.Cells(f, "R") = TextBox18
MsgBox "Datos actualizados correctamente", vbInformation, "ALUMNOS"
limpia
End Sub
Sub limpia()
'Por.Dante Amor
ComboBox1 = ""
ComboBox2 = ""
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox14 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
TextBox2.Locked = True
TextBox3.Locked = True
TextBox4.Locked = True
TextBox5.Locked = True
TextBox6.Locked = True
TextBox7.Locked = True
TextBox8.Locked = True
TextBox9.Locked = True
TextBox10.Locked = True
TextBox11.Locked = True
TextBox12.Locked = True
TextBox13.Locked = True
TextBox14.Locked = True
TextBox15.Locked = True
TextBox16.Locked = True
TextBox17.Locked = True
End Sub
Private Sub UserForm_Activate()
'Act.Por.Dante Amor
OptionButton1 = True
Set h1 = Sheets("alumnos")
uf = h1.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To h1.Range("A" & Rows.Count).End(xlUp).Row
ComboBox1.AddItem h1.Cells(i, "A")
Next
Set h2 = Sheets("alumnos")
uf = h2.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To h2.Range("B" & Rows.Count).End(xlUp).Row
ComboBox2.AddItem h2.Cells(i, "B")
Next
End Sub
Private Sub CommandButton2_Click()
Unload Me
Application.DisplayAlerts = False
'Application.Quit
End Sub
1 Respuesta
Cambia el commandbutton3
Private Sub CommandButton3_Click()
'Act.Por.Dante Amor
Set h1 = Sheets("alumnos")
If TextBox1 = "" Or TextBox2 = "" Or TextBox3 = "" Or TextBox4 = "" Then
MsgBox "Campos requeridos vacíos favor complete"
TextBox2.SetFocus
Exit Sub
End If
'
If ComboBox1 <> "" Then f = ComboBox1.ListIndex + 2
If ComboBox2 <> "" Then f = ComboBox2.ListIndex + 2
h1.Cells(f, "A") = TextBox1
h1.Cells(f, "B") = TextBox2
h1.Cells(f, "C") = TextBox3
h1.Cells(f, "D") = TextBox4
h1.Cells(f, "E") = TextBox5
h1.Cells(f, "F") = TextBox6
h1.Cells(f, "G") = TextBox7
h1.Cells(f, "H") = TextBox8
MsgBox "Datos actualizados correctamente", vbInformation, "ALUMNOS"
limpia
OptionButton1 = False
OptionButton2 = False
End SubCambia el evento activate:
Private Sub UserForm_Activate()
'Act.Por.Dante Amor
OptionButton1 = False
OptionButton2 = False
Set h1 = Sheets("alumnos")
uf = h1.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To h1.Range("A" & Rows.Count).End(xlUp).Row
ComboBox1.AddItem h1.Cells(i, "A")
ComboBox2.AddItem h1.Cells(i, "A")
Next
ComboBox1.Enabled = False
ComboBox2.Enabled = False
End SubY agrega lo siguiente:
Private Sub OptionButton1_Click() 'Por.Dante Amor ComboBox1.Enabled = True ComboBox2.Enabled = False End Sub Private Sub OptionButton2_Click() 'Por.Dante Amor ComboBox1.Enabled = False ComboBox2.Enabled = True End Sub
