VBA to launch userform by selecting a cell

[tynawg]

Hello,

I have data sets I want to have code for that if I select a specific column, cell it opens a userform.

The data set starts with a number such as 9334231/1 below. It will always vary in length. then another number, just different, indicates start of a new data set.

If I was to select an Item code, Qty/Hrs etc I want that action to open a userform. I dont have a clue which way would be best?

The userform has been made in which I can select the trade in the first box, then codes associated with that trade populate the second box. On selection of a Item code the Qty/Hrs, Description and Location/Asset display in a textbox so the user can see if that is the code they want.

Essentially a data validation.

Often the client will send the order with incorrect Item code or Qty/Hrs and this is trying to automate changing the code.

<colgroup><col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" width="82" span="2"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:14262;width:293pt" width="390"> <col style="mso-width-source:userset;mso-width-alt:6070;width:125pt" width="166"> </colgroup><tbody>
[TD="width: 82"]9334231/1[/TD]
[TD="width: 82"][/TD]
[TD="width: 61"][/TD]
[TD="width: 390"][/TD]
[TD="width: 166"][/TD]

[TD="align: right"]191[/TD]

[TD="class: xl73, width: 82"]Trade[/TD]
[TD="class: xl73, width: 82"]Item Code[/TD]
[TD="class: xl73, width: 61"]Qty/Hrs[/TD]
[TD="class: xl73, width: 390"]Description[/TD]
[TD="class: xl73, width: 166"]Location/Asset[/TD]

[TD="class: xl74, width: 82"]CABCARP[/TD]
[TD="class: xl72, width: 82"]CACU01[/TD]
[TD="class: xl72, width: 61, align: right"]1[/TD]
[TD="class: xl72, width: 390"]Feature = N/A, Ease, rehang and adjust cupboard or meter box door, hinges and catch
Hinges to LHS door[/TD]
[TD="class: xl72, width: 166"]Location: Bathroom [/TD]

[TD="class: xl74, width: 82"]CABCARP[/TD]
[TD="class: xl72, width: 82"]CADB02[/TD]
[TD="class: xl72, width: 61, align: right"]1[/TD]
[TD="class: xl72, width: 390"]Feature = DOOR, Replace or SAI satin chrome wall mounted door buffer stop
Bathroom 1: Door Buffer: Replace: Missing[/TD]
[TD="class: xl72, width: 166"]Location: Bathroom [/TD]

[TD="class: xl74, width: 82"]CABCARP[/TD]
[TD="class: xl72, width: 82"]CARL02[/TD]
[TD="class: xl72, width: 61, align: right"]1[/TD]
[TD="class: xl72, width: 390"]Feature = N/A, Replace or SAI 1200 mm long x 19 mm diameter chrome towel rail
Bathroom 1: Towel Rail: Replace: Cracking[/TD]
[TD="class: xl72, width: 166"]Location: Bathroom [/TD]

[TD="class: xl74, width: 82"]CABCARP[/TD]
[TD="class: xl72, width: 82"]CARL05[/TD]
[TD="class: xl72, width: 61, align: right"]1[/TD]
[TD="class: xl72, width: 390"]Feature = N/A, Replace or SAI toilet paper holder
Bathroom 1: Toilet Roll Holder: Replace: Rusted[/TD]
[TD="class: xl72, width: 166"]Location: Bathroom [/TD]

[TD="class: xl74, width: 82"]CABPAIN[/TD]
[TD="class: xl72, width: 82"]PTRC02[/TD]
[TD="class: xl72, width: 61, align: right"]1[/TD]
[TD="class: xl72, width: 390"]Feature = CEIL, Paint room complete(wet room up to 5 m2)[/TD]
[TD="class: xl72, width: 166"]Location: Bathroom [/TD]

[TD="class: xl74, width: 82"]CABTILE[/TD]
[TD="class: xl72, width: 82"]CAFJ01[/TD]
[TD="class: xl72, width: 61, align: right"]8[/TD]
[TD="class: xl72, width: 390"]Feature = SHOW, Seal fixture joint or gap (minimum 2 m per site)
Bathroom 1: Shower: Replace: Fixture joint is mouldy - to also include fixture joint along bottom of screen outside the shower[/TD]
[TD="class: xl72, width: 166"]Location: Bathroom [/TD]

[TD="class: xl74, width: 82"]CABTILE[/TD]
[TD="class: xl72, width: 82"]CATW01[/TD]
[TD="class: xl72, width: 61, align: right"]0.25[/TD]
[TD="class: xl72, width: 390"]Feature = WALL, Replace or SAI 150 mm x 150 mm or 200 mm x 200 mm glazed wall tiles to wet area
for new bench top[/TD]
[TD="class: xl72, width: 166"]Location: Bathroom [/TD]

[TD="class: xl74, width: 82"]CABCARP[/TD]
[TD="class: xl72, width: 82"]CACU02[/TD]
[TD="class: xl72, width: 61, align: right"]2[/TD]
[TD="class: xl72, width: 390"]Replace or SAI cupboard door catch or safety catch[/TD]
[TD="class: xl72, width: 166"]Location: Bedroom [/TD]

[TD="class: xl74, width: 82"]CABCARP[/TD]
[TD="class: xl72, width: 82"]CADB02[/TD]
[TD="class: xl72, width: 61, align: right"]1[/TD]
[TD="class: xl72, width: 390"]Feature = RF, Replace or SAI satin chrome wall mounted door buffer stop
Bedroom 1: Right Front:Door Buffer: Replace: Missing[/TD]
[TD="class: xl72, width: 166"]Location: Bedroom [/TD]

[TD="class: xl74, width: 82"]CABCARP
[/TD]
[TD="class: xl72, width: 82"]CADH04[/TD]
[TD="class: xl72, width: 61, align: right"]2[/TD]
[TD="class: xl72, width: 390"]Feature = RF, Replace or SAI dummy trim door knob door
Bedroom 1: Right Front:Built in Robes: Replace: Dummy sets missing[/TD]
[TD="class: xl72, width: 166"]Location: Bedroom





[/TD]

</tbody>

<colgroup><col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" width="82" span="2"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:14262;width:293pt" width="390"> <col style="mso-width-source:userset;mso-width-alt:6070;width:125pt" width="166"> </colgroup><tbody>

Private Sub UserForm_Initialize()
    
    Me.ComboBox1.RowSource = ""
    Me.ComboBox2.RowSource = ""
    Me.TextBox1.MultiLine = True
    Me.TextBox1.BackColor = "&H80000004"
    
    'This code pulls the trade names
    With Sheets("GMSORs")
    Me.ComboBox1.List = .Range("K2", .Cells(.Rows.Count, "K").End(xlUp)).Value
    End With

End Sub


Private Sub ComboBox2_Enter()
Dim vList, i As Long

With Sheets("GMSORs")
vList = .Range("A1:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

Me.ComboBox2.Clear
    
    For i = LBound(vList) To UBound(vList)
    If UCase(vList(i, 1)) = UCase(Me.ComboBox1.Value) Then Me.ComboBox2.AddItem vList(i, 2)
    Next

End Sub


Private Sub ComboBox1_Change()
Me.ComboBox2.Clear
End Sub


Private Sub ComboBox2_Change()
Dim c As Range
Dim fm

TextBox1 = ""
With Sheets("GMSORs")
fm = Application.Match(Me.ComboBox2, .Range("B1", .Cells(.Rows.Count, "B").End(xlUp)), 0)
    If IsNumeric(fm) Then
        For Each c In .Range("C" & fm & ":E" & fm)
            tx = tx & " - " & c
        Next
        TextBox1 = Right(tx, Len(tx) - 3)
    End If
 
End With
End Sub

Private Sub TextBox1_Enter()
Me.ComboBox2.SetFocus
End Sub

</tbody>

 

[Logit]

.
Download : https://www.amazon.com/clouddrive/share/8VRyKjsZmuDcqyPt6rcppLkjDt5tzwaTDt6wZKb0HSD