PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Custom toolbar adding and deleting

Reply
Thread Tools Rate Thread

Custom toolbar adding and deleting

 
 
Palpha32
Guest
Posts: n/a
 
      8th Jul 2008
I have a custom toolbar which I want available only when a particular
worksheet is displayed. After searching the forums I came up with the
following code -

Private Sub Workbook_Activate()
CreateMenu
End Sub
Private Sub Workbook_Open()
CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Calculate").Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("Calculate").Delete
On Error GoTo 0
End Sub

Sub CreateMenu()

Dim cmdToolbar As CommandBar
Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
Temporary:=True)

With cmdToolbar
..Position = msoBarTop
..left = (Application.CommandBars("Formatting").left +
Application.CommandBars("Formatting").Width)
..RowIndex = Application.CommandBars("Formatting").RowIndex
..Visible = True
End With

With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
..FaceId = 5
..Caption = "Calculate"
..Style = msoButtonIconAndCaption
..OnAction = "Cutting35"
End With

End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Calculate").Delete
End Sub

This works as it should when I manually run it from the VBA editor but won't
run when I open, close, deactivate or activate the workbook. What am I doing
wrong?

Also, this is a protected workbook with protected worksheets so I will have
to unprotect the sheet and protect it again.

Thanks
Peter
 
Reply With Quote
 
 
 
 
Tim Zych
Guest
Posts: n/a
 
      8th Jul 2008
This slight modification worked for me.

Private Sub Workbook_Activate()
CreateMenu
End Sub
Private Sub Workbook_Open()
CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
Private Sub Workbook_Deactivate()
Call DeleteMenu
End Sub

Sub CreateMenu()

Call DeleteMenu

Dim cmdToolbar As CommandBar
Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
Temporary:=True)

With cmdToolbar
..Position = msoBarTop
..Left = (Application.CommandBars("Formatting").Left +
Application.CommandBars("Formatting").Width)
..RowIndex = Application.CommandBars("Formatting").RowIndex
..Visible = True
End With

With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
..FaceId = 5
..Caption = "Calculate"
..Style = msoButtonIconAndCaption
..OnAction = "Cutting35"
End With
End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Calculate").Delete
End Sub

--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Palpha32" <(E-Mail Removed)> wrote in message
news:65C6ABA0-54CC-496C-BEFF-(E-Mail Removed)...
>I have a custom toolbar which I want available only when a particular
> worksheet is displayed. After searching the forums I came up with the
> following code -
>
> Private Sub Workbook_Activate()
> CreateMenu
> End Sub
> Private Sub Workbook_Open()
> CreateMenu
> End Sub
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> On Error Resume Next
> Application.CommandBars("Calculate").Delete
> On Error GoTo 0
> End Sub
> Private Sub Workbook_Deactivate()
> On Error Resume Next
> Application.CommandBars("Calculate").Delete
> On Error GoTo 0
> End Sub
>
> Sub CreateMenu()
>
> Dim cmdToolbar As CommandBar
> Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
> Temporary:=True)
>
> With cmdToolbar
> .Position = msoBarTop
> .left = (Application.CommandBars("Formatting").left +
> Application.CommandBars("Formatting").Width)
> .RowIndex = Application.CommandBars("Formatting").RowIndex
> .Visible = True
> End With
>
> With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
> .FaceId = 5
> .Caption = "Calculate"
> .Style = msoButtonIconAndCaption
> .OnAction = "Cutting35"
> End With
>
> End Sub
> Sub DeleteMenu()
> On Error Resume Next
> Application.CommandBars("Calculate").Delete
> End Sub
>
> This works as it should when I manually run it from the VBA editor but
> won't
> run when I open, close, deactivate or activate the workbook. What am I
> doing
> wrong?
>
> Also, this is a protected workbook with protected worksheets so I will
> have
> to unprotect the sheet and protect it again.
>
> Thanks
> Peter
>
>
 
Reply With Quote
 
Palpha32
Guest
Posts: n/a
 
      8th Jul 2008
Thanks Tim, however it is still the same for me - it doesn't add or remove
the toolbar when I open or close the workbook or switch between open
workbooks.

Peter

"Tim Zych" wrote:

> This slight modification worked for me.
>
> Private Sub Workbook_Activate()
> CreateMenu
> End Sub
> Private Sub Workbook_Open()
> CreateMenu
> End Sub
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Call DeleteMenu
> End Sub
> Private Sub Workbook_Deactivate()
> Call DeleteMenu
> End Sub
>
> Sub CreateMenu()
>
> Call DeleteMenu
>
> Dim cmdToolbar As CommandBar
> Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
> Temporary:=True)
>
> With cmdToolbar
> ..Position = msoBarTop
> ..Left = (Application.CommandBars("Formatting").Left +
> Application.CommandBars("Formatting").Width)
> ..RowIndex = Application.CommandBars("Formatting").RowIndex
> ..Visible = True
> End With
>
> With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
> ..FaceId = 5
> ..Caption = "Calculate"
> ..Style = msoButtonIconAndCaption
> ..OnAction = "Cutting35"
> End With
> End Sub
>
> Sub DeleteMenu()
> On Error Resume Next
> Application.CommandBars("Calculate").Delete
> End Sub
>
> --
> Tim Zych
> www.higherdata.com
> Compare data in Excel and find differences with Workbook Compare
> A free, powerful, flexible Excel utility
>
>
> "Palpha32" <(E-Mail Removed)> wrote in message
> news:65C6ABA0-54CC-496C-BEFF-(E-Mail Removed)...
> >I have a custom toolbar which I want available only when a particular
> > worksheet is displayed. After searching the forums I came up with the
> > following code -
> >
> > Private Sub Workbook_Activate()
> > CreateMenu
> > End Sub
> > Private Sub Workbook_Open()
> > CreateMenu
> > End Sub
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > On Error Resume Next
> > Application.CommandBars("Calculate").Delete
> > On Error GoTo 0
> > End Sub
> > Private Sub Workbook_Deactivate()
> > On Error Resume Next
> > Application.CommandBars("Calculate").Delete
> > On Error GoTo 0
> > End Sub
> >
> > Sub CreateMenu()
> >
> > Dim cmdToolbar As CommandBar
> > Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
> > Temporary:=True)
> >
> > With cmdToolbar
> > .Position = msoBarTop
> > .left = (Application.CommandBars("Formatting").left +
> > Application.CommandBars("Formatting").Width)
> > .RowIndex = Application.CommandBars("Formatting").RowIndex
> > .Visible = True
> > End With
> >
> > With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
> > .FaceId = 5
> > .Caption = "Calculate"
> > .Style = msoButtonIconAndCaption
> > .OnAction = "Cutting35"
> > End With
> >
> > End Sub
> > Sub DeleteMenu()
> > On Error Resume Next
> > Application.CommandBars("Calculate").Delete
> > End Sub
> >
> > This works as it should when I manually run it from the VBA editor but
> > won't
> > run when I open, close, deactivate or activate the workbook. What am I
> > doing
> > wrong?
> >
> > Also, this is a protected workbook with protected worksheets so I will
> > have
> > to unprotect the sheet and protect it again.
> >
> > Thanks
> > Peter
> >
> >
>
>
>
 
Reply With Quote
 
Tim Zych
Guest
Posts: n/a
 
      8th Jul 2008
The code must go in ThisWorkbook module.


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Palpha32" <(E-Mail Removed)> wrote in message
news:56818A4A-BFC9-4D15-8D18-(E-Mail Removed)...
> Thanks Tim, however it is still the same for me - it doesn't add or remove
> the toolbar when I open or close the workbook or switch between open
> workbooks.
>
> Peter
>
> "Tim Zych" wrote:
>
>> This slight modification worked for me.
>>
>> Private Sub Workbook_Activate()
>> CreateMenu
>> End Sub
>> Private Sub Workbook_Open()
>> CreateMenu
>> End Sub
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> Call DeleteMenu
>> End Sub
>> Private Sub Workbook_Deactivate()
>> Call DeleteMenu
>> End Sub
>>
>> Sub CreateMenu()
>>
>> Call DeleteMenu
>>
>> Dim cmdToolbar As CommandBar
>> Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
>> Temporary:=True)
>>
>> With cmdToolbar
>> ..Position = msoBarTop
>> ..Left = (Application.CommandBars("Formatting").Left +
>> Application.CommandBars("Formatting").Width)
>> ..RowIndex = Application.CommandBars("Formatting").RowIndex
>> ..Visible = True
>> End With
>>
>> With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
>> ..FaceId = 5
>> ..Caption = "Calculate"
>> ..Style = msoButtonIconAndCaption
>> ..OnAction = "Cutting35"
>> End With
>> End Sub
>>
>> Sub DeleteMenu()
>> On Error Resume Next
>> Application.CommandBars("Calculate").Delete
>> End Sub
>>
>> --
>> Tim Zych
>> www.higherdata.com
>> Compare data in Excel and find differences with Workbook Compare
>> A free, powerful, flexible Excel utility
>>
>>
>> "Palpha32" <(E-Mail Removed)> wrote in message
>> news:65C6ABA0-54CC-496C-BEFF-(E-Mail Removed)...
>> >I have a custom toolbar which I want available only when a particular
>> > worksheet is displayed. After searching the forums I came up with the
>> > following code -
>> >
>> > Private Sub Workbook_Activate()
>> > CreateMenu
>> > End Sub
>> > Private Sub Workbook_Open()
>> > CreateMenu
>> > End Sub
>> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> > On Error Resume Next
>> > Application.CommandBars("Calculate").Delete
>> > On Error GoTo 0
>> > End Sub
>> > Private Sub Workbook_Deactivate()
>> > On Error Resume Next
>> > Application.CommandBars("Calculate").Delete
>> > On Error GoTo 0
>> > End Sub
>> >
>> > Sub CreateMenu()
>> >
>> > Dim cmdToolbar As CommandBar
>> > Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
>> > Temporary:=True)
>> >
>> > With cmdToolbar
>> > .Position = msoBarTop
>> > .left = (Application.CommandBars("Formatting").left +
>> > Application.CommandBars("Formatting").Width)
>> > .RowIndex = Application.CommandBars("Formatting").RowIndex
>> > .Visible = True
>> > End With
>> >
>> > With
>> > Application.CommandBars("Calculate").Controls.Add(msoControlButton)
>> > .FaceId = 5
>> > .Caption = "Calculate"
>> > .Style = msoButtonIconAndCaption
>> > .OnAction = "Cutting35"
>> > End With
>> >
>> > End Sub
>> > Sub DeleteMenu()
>> > On Error Resume Next
>> > Application.CommandBars("Calculate").Delete
>> > End Sub
>> >
>> > This works as it should when I manually run it from the VBA editor but
>> > won't
>> > run when I open, close, deactivate or activate the workbook. What am I
>> > doing
>> > wrong?
>> >
>> > Also, this is a protected workbook with protected worksheets so I will
>> > have
>> > to unprotect the sheet and protect it again.
>> >
>> > Thanks
>> > Peter
>> >
>> >
>>
>>
>>
 
Reply With Quote
 
Palpha32
Guest
Posts: n/a
 
      8th Jul 2008
Tricky ins't it! That works perfectly, thanks Tim

Peter

"Tim Zych" wrote:

> The code must go in ThisWorkbook module.
>
>
> --
> Tim Zych
> www.higherdata.com
> Compare data in Excel and find differences with Workbook Compare
> A free, powerful, flexible Excel utility
>
>
> "Palpha32" <(E-Mail Removed)> wrote in message
> news:56818A4A-BFC9-4D15-8D18-(E-Mail Removed)...
> > Thanks Tim, however it is still the same for me - it doesn't add or remove
> > the toolbar when I open or close the workbook or switch between open
> > workbooks.
> >
> > Peter
> >
> > "Tim Zych" wrote:
> >
> >> This slight modification worked for me.
> >>
> >> Private Sub Workbook_Activate()
> >> CreateMenu
> >> End Sub
> >> Private Sub Workbook_Open()
> >> CreateMenu
> >> End Sub
> >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> Call DeleteMenu
> >> End Sub
> >> Private Sub Workbook_Deactivate()
> >> Call DeleteMenu
> >> End Sub
> >>
> >> Sub CreateMenu()
> >>
> >> Call DeleteMenu
> >>
> >> Dim cmdToolbar As CommandBar
> >> Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
> >> Temporary:=True)
> >>
> >> With cmdToolbar
> >> ..Position = msoBarTop
> >> ..Left = (Application.CommandBars("Formatting").Left +
> >> Application.CommandBars("Formatting").Width)
> >> ..RowIndex = Application.CommandBars("Formatting").RowIndex
> >> ..Visible = True
> >> End With
> >>
> >> With Application.CommandBars("Calculate").Controls.Add(msoControlButton)
> >> ..FaceId = 5
> >> ..Caption = "Calculate"
> >> ..Style = msoButtonIconAndCaption
> >> ..OnAction = "Cutting35"
> >> End With
> >> End Sub
> >>
> >> Sub DeleteMenu()
> >> On Error Resume Next
> >> Application.CommandBars("Calculate").Delete
> >> End Sub
> >>
> >> --
> >> Tim Zych
> >> www.higherdata.com
> >> Compare data in Excel and find differences with Workbook Compare
> >> A free, powerful, flexible Excel utility
> >>
> >>
> >> "Palpha32" <(E-Mail Removed)> wrote in message
> >> news:65C6ABA0-54CC-496C-BEFF-(E-Mail Removed)...
> >> >I have a custom toolbar which I want available only when a particular
> >> > worksheet is displayed. After searching the forums I came up with the
> >> > following code -
> >> >
> >> > Private Sub Workbook_Activate()
> >> > CreateMenu
> >> > End Sub
> >> > Private Sub Workbook_Open()
> >> > CreateMenu
> >> > End Sub
> >> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> > On Error Resume Next
> >> > Application.CommandBars("Calculate").Delete
> >> > On Error GoTo 0
> >> > End Sub
> >> > Private Sub Workbook_Deactivate()
> >> > On Error Resume Next
> >> > Application.CommandBars("Calculate").Delete
> >> > On Error GoTo 0
> >> > End Sub
> >> >
> >> > Sub CreateMenu()
> >> >
> >> > Dim cmdToolbar As CommandBar
> >> > Set cmdToolbar = Application.CommandBars.Add(Name:="Calculate",
> >> > Temporary:=True)
> >> >
> >> > With cmdToolbar
> >> > .Position = msoBarTop
> >> > .left = (Application.CommandBars("Formatting").left +
> >> > Application.CommandBars("Formatting").Width)
> >> > .RowIndex = Application.CommandBars("Formatting").RowIndex
> >> > .Visible = True
> >> > End With
> >> >
> >> > With
> >> > Application.CommandBars("Calculate").Controls.Add(msoControlButton)
> >> > .FaceId = 5
> >> > .Caption = "Calculate"
> >> > .Style = msoButtonIconAndCaption
> >> > .OnAction = "Cutting35"
> >> > End With
> >> >
> >> > End Sub
> >> > Sub DeleteMenu()
> >> > On Error Resume Next
> >> > Application.CommandBars("Calculate").Delete
> >> > End Sub
> >> >
> >> > This works as it should when I manually run it from the VBA editor but
> >> > won't
> >> > run when I open, close, deactivate or activate the workbook. What am I
> >> > doing
> >> > wrong?
> >> >
> >> > Also, this is a protected workbook with protected worksheets so I will
> >> > have
> >> > to unprotect the sheet and protect it again.
> >> >
> >> > Thanks
> >> > Peter
> >> >
> >> >
> >>
> >>
> >>
>
>
>
 
Reply With Quote
 
 
 
Reply

« VBA for pivot tables | User-defined function to give blank cell? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding custom buttons to the Standard toolbar versus a custom toolbar cainrandom@gmail.com Microsoft Outlook Program Addins 2 9th Oct 2008 05:13 PM
adding custom images to a button on custom toolbar Mousam Microsoft Excel Programming 1 13th Jul 2007 04:28 PM
Adding Custom Toolbar =?Utf-8?B?VG9kZA==?= Microsoft Word Document Management 11 18th Oct 2006 11:49 PM
Adding a custom Toolbar in PowerPoint =?Utf-8?B?S3JvY2tlcg==?= Microsoft Powerpoint 2 7th Jul 2006 09:27 PM
Using BeforeClose for deleting a custom toolbar daithimcc Microsoft Excel Programming 6 1st Feb 2004 10:57 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 07:01 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top