IThanks for this post. I've tried out your example and it works beautifully.
Class Modules look very useful, but I'm not very comfortable manipulating
them at present. I'll definitely look into experimenting with them in the
future though. So more questions about them
may appear on the group! :)
Thanks again.
Darren

From: "onedaywhen"
Newsgroups: microsoft.public.excel.programming
Sent: Thursday, November 06, 2003 9:34 AM
Subject: Identify Forms DropDown as it is triggered


For a small demo, carry out the following steps:

Close all other workbooks (just on case something goes wrong!)
Create a new blank workbook (don't forget to save it).
On Sheet1, add some values to range A1:A10.
From the Controls toolbar, add two comboboxes to Sheet1.
By default, they will be called ComboBox1 and ComboBox2 respectively.
Ensure the Control toolbar is not in design mode (the 'setsquare' button).
In the VBE, add a class module to the project (it will be called Class1 by
default).
Add the following code to Class1:


Option Explicit

Private WithEvents m_oCombo As MSForms.ComboBox

Public Function InitProperties(ByVal Combo As MSForms.ComboBox, _
ByVal ListFillRange As Range) As Boolean
Set m_oCombo = Combo
Combo.List = ListFillRange.Value
End Function

Private Sub m_oCombo_Change()
MsgBox "You changed " & m_oCombo.Name
End Sub


Add the following code to the code module of Sheet1:


Option Explicit

Private m_oClass1Instance1 As Class1
Private m_oClass1Instance2 As Class1

Public Sub InitializeClasses()

Set m_oClass1Instance1 = New Class1
Set m_oClass1Instance2 = New Class1

m_oClass1Instance1.InitProperties ComboBox1, Me.Range("A1:A10")
m_oClass1Instance2.InitProperties ComboBox2, Me.Range("A1:A10")

End Sub

Public Sub KillClasses()

Set m_oClass1Instance1 = Nothing
Set m_oClass1Instance2 = Nothing

End Sub

Run the InitializeClasses macro.
Dropdown the combos and select a value from each.
Don't forget to run the KillClasses sub before you close the workbook.

"Darren Hill" wrote in message
...
Class Modules are a bit of a mystery to me. Can you show me how I would
do
that?

Darren

"onedaywhen" wrote in message
m...
You should consider using the equivalent control from the Controls
toolbar i.e. the ActiveX ComboBox control. These controls are
notoriously buggy when used on a worksheet, and rightly so in most
cases, but I've used them extensively and I've never had a problem
with the ComboBox control. The big advantage of using an ActiveX
control is that you can declare one as a WithEvents object variable in
a class module and trap its events. This means that multiple ComboBox
controls can have a common event handler which could test the name of
the ComboBox for that particular instance of the class.

"Darren Hill" wrote in message
...
Oops - forgot to mention I'm using Excel2000 on WinXP.
--
Darren

"Darren Hill" wrote in message
...
I'm not sure how to manipulate the DropDown boxes from the Forms
toolbar.
Here's my problem:

I have a set of dropdowns in cells A5:A20.
I have second set of dropdowns in cells B5:B20.

When a selection is made in the Set A dropdowns, I want to change
the
listfillrange displayed in the adjacent dropdown in Set B.

I could create a macro linked to each of the dropdowns in column
A,
but
I'm
sure there's a way to have a single macro, and in that macro
identify
which
row I'm on, and then select the dropdown in column B on the same
row.

Thanks in Advance.

Darren