workbook open event

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
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"

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


cmdbarcombo.ListIndex = 1

cmdbar.Visible = True
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Add the following code to the Thisworkbook code module

Private Sub Workbook_Open()
CreateCustomToolbar
End Sub


Ciao
 

Whisperer14

Well-known Member
Joined
Nov 6, 2002
Messages
589
Your own Sub CreateCustomToolbar is placed in a standard module that is called by chiello's example
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
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"

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

Well-known Member
Joined
Jun 12, 2004
Messages
957

ADVERTISEMENT

Copy this portion of what Chiello gave you:

Code:
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:

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCustomToolbar
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,551
Messages
5,625,457
Members
416,107
Latest member
AVaes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top