Oak,
You need to create a combobox Class to which you can add all the Comboboxes.
Not sure if you're talking UserForm or not, so I'll try both.
The following will add all comboboxes in a userform to a class called
FormCboxClass:
First create a class module in the VBE by Insert- Class Module. Name it
FormCboxClass. In this module paste the following code:
Option Explicit
Public WithEvents FormCbox As msforms.ComboBox
Private Sub FormCbox_Change()
If FormCbox.Value = "1" Then
Call sub_1
ElseIf FormCbox.Value = "2" Then
Call sub_2
ElseIf FormCbox.Value = "3" Then
Call sub_3
End If
End Sub
Then in your Userform put the following code:
Dim myControls As Collection ' note this is above any subroutines
Option Explicit
Private Sub UserForm_Initialize()
Dim tmpctl As Control
Dim ctl As FormCboxClass
Set myControls = New Collection
For Each tmpctl In Me.Controls
If TypeOf tmpctl Is msforms.ComboBox Then
Set ctl = New FormCboxClass
Set ctl.FormCbox = tmpctl
End If
myControls.Add ctl
Next
End Sub
Put your Sub_1, etc. in a regular module and you should be good to go.
If you were talking worksheet comboboxes, then it's similar.
First create a class module in the VBE by Insert- Class Module. Name it
SheetCboxClass. In this module paste the following code:
Option Explicit
Public WithEvents SheetCbox As msforms.ComboBox
Private Sub SheetCbox_Change()
If SheetCbox.Value = "1" Then
Call sub_1
ElseIf SheetCbox.Value = "2" Then
Call sub_2
ElseIf SheetCbox.Value = "3" Then
Call sub_3
End If
End Sub
Then paste the following into the Sheet Module for the worksheet that
contains your comboboxes:
Dim myControls As Collection ' note this is above any subroutines
Option Explicit
Private Sub Worksheet_Activate()
Dim tmpctl As OLEObject
Dim ctl As SheetCboxClass
Set myControls = New Collection
For Each tmpctl In Sheet1.OLEObjects
If TypeOf tmpctl.Object Is msforms.ComboBox Then
Set ctl = New SheetCboxClass
Set ctl.SheetCbox = tmpctl.Object
End If
myControls.Add ctl
Next
End Sub
Again put your Sub_1's, etc. into a regular module.
This is basically copied from other posts, and has provided me much more
understanding than I previously had. Also see this link at John
Walkenbach's site
http://j-walk.com/ss/excel/tips/tip44.htm
Hopefully, there are no errors in the above, but if so, one of the experts
will come along and set us straight.
hth,
Doug
"Oak" wrote in message
...
Would anyone know how to code a ComboBox self-reference, so that it could
be used in a Combo Box processing procedure that was called from multiple
ComboBox Change events, in each case referring to the specific ComboBox from
which it was being called?
For instance, in the below procedure I want to be able to use it in
multiple ComboBox Change events and in each ComboBox, it will being making
reference to its own value (ComboBox.Value), but not sure how to code to
have each ComboBox make a self-reference that can be used in its code, but
that is code that can be used in a procedure to be called from within
multiple ComboBoxes?:
Public Sub CBOItemProcess()
with
WhatGoesHere?_that_could_reference_whatever_ComboB ox_the_procedure_was_being
_called_from
If .Value = "RT239" Then
Call ThisProcedure
ElseIf .Value = "JY457" Then
Call ThisOtherProcedure
ElseIf .Value = "WN2345" Then
Call AnotherProcedure
End If
End With
End Sub
Thank you for any assistance