Multiple comboxes

[deltabravo]

I have used the following code on a userform for 3 comboboxes, I have 16 similar comboboxes,

SSFRID1A,

SSFRID1B,

SSFRID1C etc. Its there a way to write the code once and use it for each cbox rather that creating a new sub for each.​

​

​

---------------------------------
Sub Fault_ComboBox_SSFRID1A()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
'If the is an error continue with code
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie1sfrida.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row

With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie1sscida.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie1typea.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie1desca.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie1suba.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie1classa.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie1pfda.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0

End Sub

------------------------------------

Sub Fault_ComboBox_SSFRID1B()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie1sfridb.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row

With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie1sscidb.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie1typeb.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie1descb.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie1subb.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie1classb.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie1pfdb.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub

---------------------------------------
Sub Fault_ComboBox_SSFRID2A()
Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range
On Error Resume Next
lastrow = ThisWorkbook.Sheets("Engineering Data").Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = ThisWorkbook.Sheets("Engineering Data").Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(faultsfrm.ie2sfrida.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row

With ThisWorkbook.Sheets("Engineering Data")
faultsfrm.ie2sscida.Value = .Range("B" & FindRow.Row).Value
faultsfrm.ie2typea.Value = .Range("F" & FindRow.Row).Value
faultsfrm.ie2desca.Value = .Range("G" & FindRow.Row).Value
faultsfrm.ie2suba.Value = .Range("H" & FindRow.Row).Value
faultsfrm.ie2classa.Value = .Range("AE" & FindRow.Row).Value
faultsfrm.ie2pfda.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0
End Sub

 

[sykes]

You could write a Public Function, with arguments for your variables (looks like you'd need 7 arguments), then call the function for each ComboBox, passing the variables to it, and it'll pass the data back to the calling procedure.

BTW, you could trim the existing code down a little, by shifting the With statement up a few rows:

Dim lastrow As Integer
Dim SearchRange As Range
Dim FindRow As Range

With ThisWorkbook.Sheets("Engineering Data")

'If the is an error continue with code
On Error Resume Next
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
Set SearchRange = .Range("A1:A" & lastrow)
Set FindRow = SearchRange.Find(Faultsfrm.ie1sfrida.Value, LookIn:=xlValues, LookAt:=xlWhole)
'MsgBox FindRow.Row

Faultsfrm.ie1sscida.Value = .Range("B" & FindRow.Row).Value
Faultsfrm.ie1typea.Value = .Range("F" & FindRow.Row).Value
Faultsfrm.ie1desca.Value = .Range("G" & FindRow.Row).Value
Faultsfrm.ie1suba.Value = .Range("H" & FindRow.Row).Value
Faultsfrm.ie1classa.Value = .Range("AE" & FindRow.Row).Value
Faultsfrm.ie1pfda.Value = .Range("AH" & FindRow.Row).Value
End With
On Error GoTo 0