Combo Box Events - Self-Referencing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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_ComboBox_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 said:
Would anyone know how to code a ComboBox self-reference, so that it could
Click to expand...
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
Click to expand...
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?:
 
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

--
 
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
 
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_ComboBox_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 said:
Thanks for assitance, however, I have not been able to get the
Click to expand...
code to work. Is anyone able to tell me why?
After creating the ComboBox Item Class as follows, Class
Click to expand...
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
Click to expand...
Highlighted, 'Method or data member not found'
Set cboTemp.CcboItem = objOle.Object
colCBOs.Add cboTemp 'Don't see why this line is here,
Click to expand...
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,
Click to expand...
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
Click to expand...
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?:
 
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
With cboIte
If .Value <> "" Or .Value = Null The
DoThisCode(
End I
End Wit
Option Explici
Dim colCBOs As Collectio
'Declare variables for use in CBO Collection initialization procedur
Dim objOle As Object, cboTemp As 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
Click to expand...
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
calleduse 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?
 
Oak
Method or data member not found with the highlighted focus on the '.CcboItem ='
Click to expand...

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.
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.
 
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 said:
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,
Click to expand...
the error remains the same "Compile Error: Method or data member
not found with the highlighted focus on the '.CcboItem ='
 
Thank you much everyone. I do appreciate your help, and understand it much better now and it works.
 
Back
Top