Hi everybody,

I have the following code which is implemented on 35 comboboxes in on
sheet .. I wrote the same code 35 times .. only changing the comboxbo
number.

Can I implement the same code on 35 comboboxes without repeating it 3
times?


Code
-------------------
Private Sub ComboBox1_Change()
On Error GoTo X
ComboBox1 = Application.WorksheetFunction.Match(ComboBox1, [D2:D6], 0)
Exit Sub
X:
If Err = 1004 Then
Me.Activate
Else
Resume
End If
End Su
-------------------


I hope that my question is clear for all,

Thank you

--
LoveCandl
-----------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...fo&userid=2861
View this thread: http://www.excelforum.com/showthread.php?threadid=56420

You can pass an object as an argument to function/sub:
Function MatchCombo(argCombo As ComboBox) As Variant
'add error trap for unMatched values
With argCombo
MatchCombo= Application.WorksheetFunction.Match(.Value, [D2:D6], 0)
End With
End Function

Private Sub ComboBox1_Change()
Dim RetVal as Long
RetVal = MatchCombo(ComboBox1)
'Check for a valid return value.
'...etc
Exit Sub

NickHK

"LoveCandle" wrote
in message ...

Hi everybody,

I have the following code which is implemented on 35 comboboxes in one
sheet .. I wrote the same code 35 times .. only changing the comboxbox
number.

Can I implement the same code on 35 comboboxes without repeating it 35
times?


Code:
--------------------
Private Sub ComboBox1_Change()
On Error GoTo X
ComboBox1 = Application.WorksheetFunction.Match(ComboBox1, [D2:D6], 0)
Exit Sub
X:
If Err = 1004 Then
Me.Activate
Else
Resume
End If
End Sub
--------------------


I hope that my question is clear for all,

Thank you,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=564205

Usually, this would be done by having the multiple controls call a single
routine which contains the code. Only one code line ("Call XYZ") is therefore
required for each of the multiple event handlers.

However, with 35 combo boxes I'd go with a Class Module instead: Add a
class module the same as you would a new user form. By default it will be
named Class1. Enter the following code into the class module:

Public WithEvents Combo As ComboBox
Private Sub Combo_Change()
MsgBox "Test"
End Sub

Add the following code to your user form's code module. This assumes that
the combo boxes involved are named ComboBox1 to ComboBox35. Add an offset to
the i variable if necessary to reference the correct names:

Dim ComboArr(1 To 35) As New Class1
Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 35
Set ComboArr(i).Combo = Me.Controls("ComboBox" & i)
Next
End Sub

According to plan, when you load the user form and change the contents of
any of the combo boxes you should receive the message "Test". Your code
should go in place of this message box code. However, I don't believe a user
form can accept focus at run time. I think I'd pass the focus to another
control, say a command button. Perhaps this:

Private Sub Combo_Change()
On Error GoTo X
Combo = Application.WorksheetFunction.Match(Combo, [D2:D6], 0)
Exit Sub
X:
If Err.Number = 1004 Then UserForm1.Controls("CommandButton1").SetFocus
End Sub

Regards,
Greg

Thank you all for your help,,

Mr. Greg Wilson your codes seems to be great but, my comboboxes are
not put on a userform .. they are put on the worksheet itself

so can you please edit the code to work on the worksheet not on a user
form.

Thank you again,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=564205

Paste to the ThisWorkbook module. Assumed is that the sheet containing the
combo boxes is named "Sheet1". Change to suit. Also, assumed is that there
are no other combo boxes on the sheet except the 35 you meantioned:-

Dim ComboArr(1 To 35) As New Class1

Private Sub Workbook_Open()
Dim OLEObj As OLEObject
Dim i As Integer
For Each OLEObj In Sheets("Sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
i = i + 1
Set ComboArr(i).Combo = OLEObj.Object
End If
Next
End Sub

Paste to the Class Module:-

Public WithEvents Combo As MSForms.ComboBox
Private Sub Combo_Change()
MsgBox "Test abcde"
End Sub

Regards,
Greg

"LoveCandle" wrote:


Thank you all for your help,,

Mr. Greg Wilson your codes seems to be great but, my comboboxes are
not put on a userform .. they are put on the worksheet itself

so can you please edit the code to work on the worksheet not on a user
form.

Thank you again,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=564205