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_fro
If .Value = "RT239" The
Call ThisProcedur
ElseIf .Value = "JY457" The
Call ThisOtherProcedur
ElseIf .Value = "WN2345" The
Call AnotherProcedur
End I
End Wit
End Su

Thank you for any assistance

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

I glanced through your post and I think I spotted a typo:

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 ' << wrong?
Next

Untested, but I think it would make more sense as follows:

For Each tmpctl In Me.Controls
If TypeOf tmpctl Is msforms.ComboBox Then
Set ctl = New FormCboxClass
Set ctl.FormCbox = tmpctl
myControls.Add ctl ' << corrected
End If
Next

--

"Doug Glancy" wrote in message ...
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

Thanks. That certainly makes more sense, although in my limited testing the
results were the same, perhaps because it was just adding the same control
again if not a combobox.

Also Oak, my suggestion of putting the sheet class code in the worksheet
activate event is not good, since this event doesn't fire when you open the
workbook. Perhaps the workbook open event - whatever makes sense for your
application.

hth,

Doug

"onedaywhen" wrote in message
om...
I glanced through your post and I think I spotted a typo:

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 ' << wrong?
Next

Untested, but I think it would make more sense as follows:

For Each tmpctl In Me.Controls
If TypeOf tmpctl Is msforms.ComboBox Then
Set ctl = New FormCboxClass
Set ctl.FormCbox = tmpctl
myControls.Add ctl ' << corrected
End If
Next

--

"Doug Glancy" wrote in message
...
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

Thanks for assitance, however, I have not been able to get the code to work. Is anyone able to tell me why

After creating the ComboBox Item Class as follows, Class Module Property Name = CcboIte

Public WithEvents cboItm As MSForms.ComboBo

Private Sub cboItm_Change(
With cboIt
If .Value < "" Or .Value = Null The
DoThisCode(
End I
End Wit

And in the Workbook module
Option Explici
Dim colCBOs As Collectio

Private Sub Workbook_Open(
'Declare variables for use in CBO Collection initialization procedur
Dim objOle As Object, cboTemp As CcboIte

'Initialize ComboBox Collection - Will use Class CcboIte
For Each objOle In Worksheets(1).OLEObject
If TypeOf objOle.Object Is MSForms.ComboBox The
Set cboTemp = New CcboIte
' ERROR on line below: CcboItem Highlighted, 'Method or data member not found'
Set cboTemp.CcboItem = objOle.Objec
colCBOs.Add cboTemp 'Don't see why this line is here, as the Collection added to, does not seem used?
End I
Next objOl

End Su


----- Oak wrote: ----

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_fro
If .Value = "RT239" The
Call ThisProcedur
ElseIf .Value = "JY457" The
Call ThisOtherProcedur
ElseIf .Value = "WN2345" The
Call AnotherProcedur
End I
End Wit
End Su

Thank you for any assistance

You need to change

Set cboTemp.CcboItem = objOle.Object
to
Set cboTemp.cboItm = objOle.Object


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Oak" wrote in message
...
Thanks for assitance, however, I have not been able to get the
code to work. Is anyone able to tell me why?

After creating the ComboBox Item Class as follows, Class
Module Property Name = CcboItem

Public WithEvents cboItm As MSForms.ComboBox

Private Sub cboItm_Change()
With cboItm
If .Value < "" Or .Value = Null Then
DoThisCode()
End If
End With

And in the Workbook module:
Option Explicit
Dim colCBOs As Collection

Private Sub Workbook_Open()
'Declare variables for use in CBO Collection initialization
procedure
Dim objOle As Object, cboTemp As CcboItem

'Initialize ComboBox Collection - Will use Class CcboItem
For Each objOle In Worksheets(1).OLEObjects
If TypeOf objOle.Object Is MSForms.ComboBox Then
Set cboTemp = New CcboItem
' ERROR on line below: CcboItem
Highlighted, 'Method or data member not found'
Set cboTemp.CcboItem = objOle.Object
colCBOs.Add cboTemp 'Don't see why this line is here,
as the Collection added to, does not seem used?
End If
Next objOle

End Sub


----- Oak wrote: -----

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_procedur
e_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

Thank you, but my reference to 'itm' instead of 'item' was my typo in forum question only,
the corrected message below here is more accurate. Therefore, the error remains the same "Compile Error: Method or data member not found with the highlighted focus on the '.CcboItem =

Any ideas what the problem is
Thank you much

After creating the ComboBox Item Class as follows, Clas
Module Property Name = CcboIte
Public WithEvents cboItem As MSForms.ComboBo
Private Sub cboItem_Change(
With cboIte
If .Value < "" Or .Value = Null The
DoThisCode(
End I
End Wit
And in the Workbook module
Option Explici
Dim colCBOs As Collectio
Private Sub Workbook_Open(
'Declare variables for use in CBO Collection initialization procedur
Dim objOle As Object, cboTemp As CcboIte
'Initialize ComboBox Collection - Will use Class CcboIte
For Each objOle In Worksheets(1).OLEObject
If TypeOf objOle.Object Is MSForms.ComboBox The
Set cboTemp = New CcboIte
' ERROR on line below: CcboItem Highlighted, 'Method or data member not found
Set cboTemp.CcboItem = objOle.Objec
colCBOs.Add cboTemp 'Don't see why this line is here, as the Collection added to, does not seem used
End I
Next objOl
End Su
----- Oak wrote: ----
Would anyone know how to code a ComboBox self-reference
so that it could be used in a Combo Box processing procedure tha
was called from multiple ComboBox Change events, in each cas
referring to the specific ComboBox from which it was bein
called
For instance, in the below procedure I want to be able t
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
self-reference that can be used in its code, but that is cod
that can be used in a procedure to be called from within multipl
ComboBoxes?
Public Sub CBOItemProcess(
with (WhatGoesHere?_that_could_reference_whatever_Combo Box_the_procedure_was_being_called_from.
If .Value = "RT239" The
Call ThisProcedur
ElseIf .Value = "JY457" The
Call ThisOtherProcedur
ElseIf .Value = "WN2345" The
Call AnotherProcedur
End I
End Wit
End Su
Thank you for any assistanc

Oak

Method or data member not found with the highlighted focus on the
'.CcboItem ='


You don't have a property (read: public variable) in your class called
CcboItm, the property is cboItem, so that's what you need to use.

colCBOs.Add cboTemp 'Don't see why this line is here, as the
Collection added to, does not seem used?

You store the instance of your class in a collection so that it persists.
If you didn't, all references to the class would fall out of scope when the
procedure ends and the class would self-terminate (and the event wouldn't
fire, which is bad). By storing it in a public collection, the reference to
the class in the public collection is there until you destroy it or until
you close the workbook.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Oak,

Change the line of code

Set cboTemp.CcboItem = objOle.Object
to
Set cboTemp.cboItem = objOle.Object

In your code cboItem is the variable name, and CcboItem is the
class name. You want to use the variable name, not the class
name.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Oak" wrote in message
...
Thank you, but my reference to 'itm' instead of 'item' was my
typo in forum question only,
the corrected message below here is more accurate. Therefore,
the error remains the same "Compile Error: Method or data member
not found with the highlighted focus on the '.CcboItem ='

Any ideas what the problem is?
Thank you much.

After creating the ComboBox Item Class as follows, Class
Module Property Name = CcboItem
Public WithEvents cboItem As MSForms.ComboBox
Private Sub cboItem_Change()
With cboItem
If .Value < "" Or .Value = Null Then
DoThisCode()
End If
End With
And in the Workbook module:
Option Explicit
Dim colCBOs As Collection
Private Sub Workbook_Open()
'Declare variables for use in CBO Collection initialization
procedure
Dim objOle As Object, cboTemp As CcboItem
'Initialize ComboBox Collection - Will use Class CcboItem
For Each objOle In Worksheets(1).OLEObjects
If TypeOf objOle.Object Is MSForms.ComboBox Then
Set cboTemp = New CcboItem
' ERROR on line below: CcboItem
Highlighted, 'Method or data member not found'
Set cboTemp.CcboItem = objOle.Object
colCBOs.Add cboTemp 'Don't see why this line is here,
as the Collection added to, does not seem used?
End If
Next objOle
End Sub
----- Oak wrote: -----
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_Combo Box_the_procedu
re_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

Thank you much everyone. I do appreciate your help, and understand it much better now and it works.