workbook open event

[d0wnt0wn]

how do i ut this code in a workbook open event please

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 Worksheets("Sheet2").Range("A" & I)
Next I


cmdbarcombo.ListIndex = 1

cmdbar.Visible = True

 

[chiello]

Add the following code to the Thisworkbook code module

Private Sub Workbook_Open()
CreateCustomToolbar
End Sub


Ciao

 

[Whisperer14]

Your own Sub CreateCustomToolbar is placed in a standard module that is called by chiello's example

 

[d0wnt0wn]

man im so confused..... i was given this code by Norie ut i cant figure out what to do with it... could someone please explain it to me step by step


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 Worksheets("Sheet2").Range("A" & 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

 

[Justinlabenne]

Copy this portion of what Chiello gave you:

Private Sub Workbook_Open() 
CreateCustomToolbar 
End Sub

Go to the Excel icon in the top left corner > right-click > view code >

this should put you in the "ThisWorkbook" code module

Paste the above code in there.

Paste this in there too:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCustomToolbar
End Sub

 

[d0wnt0wn]

thanks justinne

 

[d0wnt0wn]

err justin