Tom,

You were so gracious in giving me generic code to identify and activate
Contol Tool Comboboxes on a sheet. (works a treat!)

Now could I get your indulgence for generic code to capture any of the
Comboboxes change.

What I would like is something like for any combobox, on change, the
next cell is selected.
Sub ComboBox_change
target.offset(1,0).select
end sub

You have already saved me a ton of code and I am greedy...

thanks...
--
sb

you can get an example at John Walkenbach's site.

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

this is for commandbuttons on a userform, but you should be able to easily
adapt it to comboboxes on a worksheet.

If you have trouble, post back.

--
Regards,
Tom Ogilvy

steve wrote in message
...
Tom,

You were so gracious in giving me generic code to identify and activate
Contol Tool Comboboxes on a sheet. (works a treat!)

Now could I get your indulgence for generic code to capture any of the
Comboboxes change.

What I would like is something like for any combobox, on change, the
next cell is selected.
Sub ComboBox_change
target.offset(1,0).select
end sub

You have already saved me a ton of code and I am greedy...

thanks...
--
sb

Tom,

You're being great...

made these 2 macros and get a Run time error '13', Type
Mismatch at the start of the For loop

Once this code is working, how do I get it to return the
name of the combobox??? And what triggers the macro?

(Working with controls is not my best...)

Your help is much appreciated...

Standard -
Option Explicit
Dim CBox() As New Class1

Sub ShowDialog()
Dim CBcount As Integer
Dim oleObj As OLEObjects

' Create the ComboBox objects
CBcount = 0
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
CBcount = CBcount + 1
ReDim Preserve CBox(1 To CBcount)
Set CBox(CBcount).CmboBoxGroup = oleObj
End If
Next oleObj

End Sub

Class -
Option Explicit

Public WithEvents CmboBoxGroup As ComboBox

Private Sub CmboBoxGroup_Change()
MsgBox "Hello from " & CmboBoxGroup.Name
End Sub

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Option Explicit
Dim cbox() As New Class1

Sub ShowDialog()
Dim CBcount As Integer
Dim oleObj As OLEObject ' <= OleObject without the s
Dim cbox1 As MSForms.ComboBox

' Create the ComboBox objects
CBcount = 0
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
CBcount = CBcount + 1
ReDim Preserve cbox(1 To CBcount)
Set cbox1 = oleObj.Object
Set cbox(CBcount).CmboBoxGroup = oleObj.Object
oleObj.Name = cbox1.Name
End If
Next oleObj

End Sub



-- Class

Option Explicit
' use MSforms.ComboBox to be sure
Public WithEvents CmboBoxGroup As MSForms.ComboBox

Private Sub CmboBoxGroup_Click()
Dim cbox1 As MSForms.ComboBox
Dim Target As Range
Set cbox1 = CmboBoxGroup
MsgBox "Hello from " & CmboBoxGroup.Name
Set Target = ActiveSheet. _
OLEObjects(cbox1.Name).TopLeftCell
Target.Offset(1, 0).Select
End Sub



This worked for me

--
Regards,
Tom Ogilvy

steve bell wrote in message
...
Tom,

You're being great...

made these 2 macros and get a Run time error '13', Type
Mismatch at the start of the For loop

Once this code is working, how do I get it to return the
name of the combobox??? And what triggers the macro?

(Working with controls is not my best...)

Your help is much appreciated...

Standard -
Option Explicit
Dim CBox() As New Class1

Sub ShowDialog()
Dim CBcount As Integer
Dim oleObj As OLEObjects

' Create the ComboBox objects
CBcount = 0
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
CBcount = CBcount + 1
ReDim Preserve CBox(1 To CBcount)
Set CBox(CBcount).CmboBoxGroup = oleObj
End If
Next oleObj

End Sub

Class -
Option Explicit

Public WithEvents CmboBoxGroup As ComboBox

Private Sub CmboBoxGroup_Change()
MsgBox "Hello from " & CmboBoxGroup.Name
End Sub

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Tom,

Your continued support and patience is muchly appreciated!

But something isn't working. Copied and pasted the 2 macros as
instructed. (The first into a standard module, the second into a class
module). Double checked that I am using MsForms combobox (your previous
code for drop down uses this reference and it works great).

Nothing happened.

Inserted a new worksheet and added a couple of combo boxes and still
nothing happened. Set some breakpoints and the macros aren't being
fired. (double checked events abled to make sure). Even tried a forms
combo box.

Something is happening though I'm not sure what it is. When I make a
selection in any of the combo boxes and click the VB icon - the screen
flashes and returns to Excel. Click it again and it indicates a VB
minimize. Click it again and I finally get to the VBE. Alt+Tab
switches just fine. So something is being triggered, but I can't tell
what.

And now when opening the file I am getting 'The file is being modified
by me and is Read Only'. This is also happening on my Personal.xls (at
work and at home). Remember seeing some discussion on this before but
could'nt find it - except reference to cleaning Temp folders (which I
have done). Am now going to reboot and see what happens...

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I assume you ran ShowDialog after the comboboxes were added.

As I said, I copied the code out of a workbook where it was working very
well.

Can't say why you are having a problem. I have used this approach with
comboboxes, checkboxes, commandbuttons etc. Never a problem.



--
Regards,
Tom Ogilvy



steve bell wrote in message
...
Tom,

Your continued support and patience is muchly appreciated!

But something isn't working. Copied and pasted the 2 macros as
instructed. (The first into a standard module, the second into a class
module). Double checked that I am using MsForms combobox (your previous
code for drop down uses this reference and it works great).

Nothing happened.

Inserted a new worksheet and added a couple of combo boxes and still
nothing happened. Set some breakpoints and the macros aren't being
fired. (double checked events abled to make sure). Even tried a forms
combo box.

Something is happening though I'm not sure what it is. When I make a
selection in any of the combo boxes and click the VB icon - the screen
flashes and returns to Excel. Click it again and it indicates a VB
minimize. Click it again and I finally get to the VBE. Alt+Tab
switches just fine. So something is being triggered, but I can't tell
what.

And now when opening the file I am getting 'The file is being modified
by me and is Read Only'. This is also happening on my Personal.xls (at
work and at home). Remember seeing some discussion on this before but
could'nt find it - except reference to cleaning Temp folders (which I
have done). Am now going to reboot and see what happens...

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Tom,

Dumb me!!! Overlooked the need to run the macro first!!!
Will have to build this into the workbook open...

Now I'm back on track...

Got rid of the phantom Excel by rebooting my PC. Not sure how it got
there but it did. The same at home. Now the Read Only thing is gone...

Now to figure out why my workbook crashes after I add 6 or 7 comboboxes.
It may tie into all the code that I can now get rid of...

Thanks Very Much for your time, help and patience!!!

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Tom,

The macros are now incorporated into the selection change event and are
working great!!!!!!!!!!!!!!!

You have saved me much anguish and a lot of code!!!!!!!!!!!!!

My form is now working and it will soon be finished for my user to
peruse and critic...

I can't thank you enough!!!!!!!!!!!!!!!!

The crashes are gone also...

Fought this thing for many weeks and now there is light...........

--
sb
"Tom Ogilvy" wrote in message
...
I assume you ran ShowDialog after the comboboxes were added.

As I said, I copied the code out of a workbook where it was working very
well.

Can't say why you are having a problem. I have used this approach with
comboboxes, checkboxes, commandbuttons etc. Never a problem.



--
Regards,
Tom Ogilvy



steve bell wrote in message
...
Tom,

Your continued support and patience is muchly appreciated!

But something isn't working. Copied and pasted the 2 macros as
instructed. (The first into a standard module, the second into a class
module). Double checked that I am using MsForms combobox (your previous
code for drop down uses this reference and it works great).

Nothing happened.

Inserted a new worksheet and added a couple of combo boxes and still
nothing happened. Set some breakpoints and the macros aren't being
fired. (double checked events abled to make sure). Even tried a forms
combo box.

Something is happening though I'm not sure what it is. When I make a
selection in any of the combo boxes and click the VB icon - the screen
flashes and returns to Excel. Click it again and it indicates a VB
minimize. Click it again and I finally get to the VBE. Alt+Tab
switches just fine. So something is being triggered, but I can't tell
what.

And now when opening the file I am getting 'The file is being modified
by me and is Read Only'. This is also happening on my Personal.xls (at
work and at home). Remember seeing some discussion on this before but
could'nt find it - except reference to cleaning Temp folders (which I
have done). Am now going to reboot and see what happens...

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!