Solved: Adding same items to combo boxes on multiple sheets

[tony2501]

Thanks in advance for any help.

In this workbook I have several(about 25) worksheets, each of which has 3 combo boxes on them, which I want to use to navigate between the different worksheets. So the items in each combobox will be the same on each sheet.

What I want to do is run some module based code that will propulate the active sheets combo box, the comboboxes are named the same on each sheet.

The code I was thinking about would be something like:


SheetName.cboOne.Additem "Item1"

Where SheetName woud change depending upon the active sheet.

The problem I have is assigning the sheet name to some sort of variable which would achieve this.:banghead:

[GTO]

Rather than worrying about ea sheet's name, maybe we can use the Workbook's SheetActivate event.

In ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim wks As Worksheet
Dim objMeComboBox As OLEObject

If TypeName(Sh) = "Worksheet" Then

Set wks = ActiveSheet

On Error Resume Next
Set objMeComboBox = wks.OLEObjects("ComboBox1")
On Error GoTo 0

If Not objMeComboBox Is Nothing Then
With objMeComboBox.Object
.Clear
.AddItem "Item1"
End With
End If
End If
End Sub
Does that help?

Mark

[tony2501]

:mbounce2: Thanks for this, it was just what I need. Sorry for delay getting back been working.

[GTO]

:mbounce2: Thanks for this, it was just what I need. Sorry for delay getting back been working.

You are most welcome and I'm glad it helped:beerchug:

[sale]

I want to use combobox (first combobox) to choose between multiple worksheets. So when i chose in first combobox some of my sheets, other data in form needs to be placed into the worksheet that i picked in first combo box.
is that posible?
i tried many ways to do it but it doesnt work. plz help