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

 

[acw]

Hi

Yep.


Tony

 

[d0wnt0wn]

tony any idea how i would adjust that so that the list populates based on a range on my worksheet instead?

 

[acw]

Hi

What is the worksheet range?


Tony

 

[d0wnt0wn]

sheet3 a1:a24

 

[Norie]

Try this:

For I = 1 To 24
    cmdbarcombo.AddItem Worksheets("Sheet3").Range("A" & I)
Next I

 

[d0wnt0wn]

hmmm for some reason im getting an error now even before i change that bit of code norie....

run time error 5 invalid procedure call or argument at this line

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





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

Sub DeleteCustomToolbar()
CommandBars("Colour Teams").Delete
End Sub

Sub ChangeColour()
Dim cmdcombo As CommandBarComboBox
Dim cmdbtn As CommandBarButton

Set cmdcombo = CommandBars("Colour Teams").FindControl(, , "TeamCombo")
Set cmdbtn = CommandBars("Colour Teams").FindControl(, , "TeamColour")

cmdbtn.Caption = "Colour" & cmdcombo.ListIndex

End Sub

Sub ColourCells()
Dim cmdcombo As CommandBarComboBox
Dim rng As Range

Set cmdcombo = CommandBars("Colour Teams").FindControl(, , "TeamCombo")

Set rng = Selection

rng.Interior.ColorIndex = cmdcombo.ListIndex
End Sub

 

[d0wnt0wn]

ok i got that part working....

 

[Norie]

You are probably getting that error because the toolbar already exists.

 

[d0wnt0wn]

yes thats what happened I went and deleted all of the test macros i had made on different sheets and it worked..... is there any way i can make the combobox a bit wider???