Update label of Userform doing Vlookup from comboboxes

[kevindorward_dfsdanf]

Hi,

I'm trying to get a userform label to update after selecting a bunch of comboboxes to give me a file name.
The file name is made up of acronyms that the vlookup is searching for e.g. General = GEN so when I click General in my dropdown combobox, that portion of the filename is entered in Label 8 and so on.

The problem is that I need to click on the label to show the resulting file name. How can I update this automatically? I have tried Me.Repaint & DoEvents to no avail.

Private Sub Label8_Click()

If Me.ComboBox1 = "" Or Me.ComboBox2 = "" Or Me.ComboBox3 = "" Or Me.ComboBox4 = "" Or Me.ComboBox5 = "" Or Me.ComboBox6 = "" Then Exit Sub

Label8.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox1, Sheet3.Range("A2:B3"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox2, Sheet4.Range("A2:B83"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox3, Sheet2.Range("A2:B9"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox4, Sheet5.Range("A2:B131"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox5, Sheet6.Range("A2:B70"), 2, 0) & Formatted_Num

End Sub

I also had this but it does the same thing

Private Sub Label8_Click()

If Me.ComboBox1 = "" Or Me.ComboBox2 = "" Or Me.ComboBox3 = "" Or Me.ComboBox4 = "" Or Me.ComboBox5 = "" Or Me.ComboBox6 = "" Then Exit Sub
With Me

.Label8 = Application.WorksheetFunction.VLookup(Me.ComboBox1, Sheet3.Range("A2:B3"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox2, Sheet4.Range("A2:B83"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox3, Sheet2.Range("A2:B9"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox4, Sheet5.Range("A2:B131"), 2, 0) & "-" & Application.WorksheetFunction.VLookup(Me.ComboBox5, Sheet6.Range("A2:B70"), 2, 0) & Formatted_Num

End With

End Sub

 

[dmt32]

Hi,
one way would be to make the code a common code & call it from your comboboxes change events

Untested by try placing following codes in your userform & see if does what you want

Private Sub ComboBox1_Change()
    GetFileName
End Sub
Private Sub ComboBox2_Change()
    GetFileName
End Sub

Private Sub ComboBox3_Change()
    GetFileName
End Sub
Private Sub ComboBox4_Change()
    GetFileName
End Sub
Private Sub ComboBox5_Change()
    GetFileName
End Sub

Private Sub GetFileName()
    Dim i As Integer
    
    Me.Label8.Caption = ""
    For i = 1 To 6
        If Len(Me.Controls("ComboBox" & i).Value) = 0 Then Exit Sub
    Next i
    
    With Application.WorksheetFunction
        Label8.Caption = .VLookup(Me.ComboBox1, Sheet3.Range("A2:B3"), 2, 0) & "-" & _
                        .VLookup(Me.ComboBox2, Sheet4.Range("A2:B83"), 2, 0) & "-" & _
                        .VLookup(Me.ComboBox3, Sheet2.Range("A2:B9"), 2, 0) & "-" & _
                        .VLookup(Me.ComboBox4, Sheet5.Range("A2:B131"), 2, 0) & "-" & _
                        .VLookup(Me.ComboBox5, Sheet6.Range("A2:B70"), 2, 0) & Formatted_Num
    End With

End Sub

Dave