View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.programming
Chip Pearson Chip Pearson is offline
external usenet poster
 
Posts: 7,247
Default Combo Box Events - Self-Referencing

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