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.

[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]

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

[GTO]

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

[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