adjust this code ???

[d0wnt0wn]

hi i have the following fantastic code supplied by Norie..... it does very nearly exactly what i want except for a couple of things... firstly how do adjust the code to create the team list in the combobox from a list on a worksheet? from what I can gather from the code this is the part that creates the current list that i have

{ For I = 1 To 24
cmdbarcombo.AddItem "Team" & I
Next I
}

is that correct???

Sub CreateCustomToolbar()
Dim cmdbar As CommandBar
Dim cmdbarcombo As CommandBarComboBox
Dim cmdbarbtn As CommandBarButton
Dim I As Integer

Set cmdbar = CommandBars.Add(Name:="Colour Teams")

Set cmdbarcombo = cmdbar.Controls.Add(msoControlComboBox)

cmdbarcombo.Tag = "TeamCombo"

cmdbarcombnAction = "ChangeColour"

Set cmdbarbtn = cmdbar.Controls.Add(msoControlButton)

cmdbarbtn.Caption = "Team Colour"

cmdbarbtn.Tag = "TeamColour"

cmdbarbtn.Style = msoButtonCaption

Set cmdbarbtn = cmdbar.Controls.Add(msoControlButton)

cmdbarbtn.Caption = "Colour Cells"

cmdbarbtn.Style = msoButtonCaption

cmdbarbtn.OnAction = "ColourCells"
For I = 1 To 24
cmdbarcombo.AddItem "Team" & I
Next I

cmdbarcombo.ListIndex = 1

cmdbar.Visible = True

End Sub

 

[Norie]

Take a look at the Width property of the command bar combo.

 

[d0wnt0wn]

sorry i have looked everywhere but i cant seem to get a properties menu for this toolbar

 

[Norie]

In the code.

cmdbarcombo.Width = 10000

Obviously you'll need to play about with the actual value.

 

[d0wnt0wn]

ok I admit it im stupid but I do not see that in the code anywhere

 

[Norie]

You need to add it to the code.

Sub CreateCustomToolbar()
Dim cmdbar As CommandBar
Dim cmdbarcombo As CommandBarComboBox
Dim cmdbarbtn As CommandBarButton
Dim I As Integer

Set cmdbar = CommandBars.Add(Name:="Colour Teams")

Set cmdbarcombo = cmdbar.Controls.Add(msoControlComboBox)

cmdbarcombo.Width = 10000

cmdbarcombo.Tag = "TeamCombo"

cmdbarcombo.OnAction = "ChangeColour"

Set cmdbarbtn = cmdbar.Controls.Add(msoControlButton)

cmdbarbtn.Caption = "Team Colour"

cmdbarbtn.Tag = "TeamColour"

cmdbarbtn.Style = msoButtonCaption

Set cmdbarbtn = cmdbar.Controls.Add(msoControlButton)

cmdbarbtn.Caption = "Colour Cells"

cmdbarbtn.Style = msoButtonCaption

cmdbarbtn.OnAction = "ColourCells"
For I = 1 To 24
cmdbarcombo.AddItem "Team" & I
Next I

cmdbarcombo.ListIndex = 1

cmdbar.Visible = True

End Sub

 

[d0wnt0wn]

norie many thanks for all of your help on this... i finally got it to work the way i want it and it was well worth thte time it took... this little toolbar is going to make my life a whole lot easier