i have attached the file trying to create a user form that uses two combo boxes namely: Name & State that will populate a result in two Text Boxes Namely: Licence# and Expiry Date:
User form also contains three buttons namely Update, Clear and Exit
if some can can help on this. See the attached file
Best regards,
Re: Using two combo boxes in a user form populate results in two text boxes & Update
Try this. :cool:
Private Sub CboNames_Change()
Me.TextBox1.Text = Me.CboNames.Value
End Sub
Private Sub CommandButton1_Click()
Dim b As Boolean, strMessage As String, btns As Long
Dim r As Double, c As Double
strMessage = "Both combo boxes must have a value."
btns = vbOKOnly + vbInformation
b = CheckBoxes
If Not b Then
MsgBox strMessage, btns
Exit Sub
End If
With Worksheets("Res and NonRes Licenses")
r = WorksheetFunction.Match(CboNames.Value, .Columns("B:B"), 0)
c = WorksheetFunction.Match(CboStates.Value, .Rows(4), 0)
Me.TextBox2.Value = .Cells(r + 1, c)
End With
End Sub
Private Sub CommandButton2_Click()
Me.CboNames.Value = ""
Me.CboStates.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.CboNames.SetFocus
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim Names As Range
Dim ws As Worksheet
Set ws = Worksheets("Res and NonRes Licenses")
For Each Names In ws.Range("Names")
With Me.CboNames
.AddItem Names.Value
End With
Next Names
For Each States In ws.Range("States")
With Me.CboStates
.AddItem States.Value
End With
Next States
End Sub
Function CheckBoxes() As Boolean
CheckBoxes = True
If Me.CboNames.Value = "" Then
CheckBoxes = False
End If
If Me.CboStates.Value = "" Then
CheckBoxes = False
End If
End FunctionRe: Using two combo boxes in a user form populate results in two text boxes & Update
Bruce
Thanks a lot it worked awesome by slight changes that i made just one thing missing by update i meant to update the record in the worksheet like update the expiry date or lic # i am attaching the new file with slight changes that i made would really appreciate if you can help me with update button as well to update the record in the original worksheet.
Warmest regards
Re: Using two combo boxes in a user form populate results in two text boxes & Update
Use this code. :cool:
Private Sub CommandButton1_Click()
Search_Update 1
End Sub
Private Sub CommandButton2_Click()
Me.CboNames.Value = ""
Me.CboStates.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.CboNames.SetFocus
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub CommandButton4_Click()
Search_Update 0
End Sub
Private Sub UserForm_Initialize()
Dim Names As Range, ws As Worksheet
Set ws = Worksheets("Res and NonRes Licenses")
For Each Names In ws.Range("Names")
With Me.CboNames
.AddItem Names.Value
End With
Next Names
For Each States In ws.Range("States")
With Me.CboStates
.AddItem States.Value
End With
Next States
End Sub
Function CheckBoxes() As Boolean
CheckBoxes = True
If Me.CboNames.Value = "" Then
CheckBoxes = False
End If
If Me.CboStates.Value = "" Then
CheckBoxes = False
End If
End Function
Sub Search_Update(a As Long)
Dim b As Boolean, strMessage As String, btns As Long
Dim r As Double, c As Double
strMessage = "Both combo boxes must have a value."
btns = vbOKOnly + vbInformation
b = CheckBoxes
If Not b Then
MsgBox strMessage, btns
Exit Sub
End If
With Worksheets("Res and NonRes Licenses")
r = WorksheetFunction.Match(CboNames.Value, .Columns("B:B"), 0)
c = WorksheetFunction.Match(CboStates.Value, .Rows(4), 0)
If a = 1 Then
Me.TextBox1.Value = .Cells(r + 0, c)
Me.TextBox2.Value = .Cells(r + 1, c)
Else
.Cells(r + 0, c).Value = TextBox1.Value
.Cells(r + 1, c).Value = TextBox2.Value
End If
End With
End SubRe: Using two combo boxes in a user form populate results in two text boxes & Update
Bruce
Thanks a lot its working perfectly fine really appreciate your support.
Best Regards,:thumbcoo:
Re: Using two combo boxes in a user form populate results in two text boxes & Update
My pleasure, thanks for the feedback. :cool:
Don’t have an account yet? Register yourself now and be a part of our community!