Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Custom toolbar adding and deleting
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Custom toolbar adding and deleting
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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 389
Default Custom toolbar adding and deleting
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" wrote in message
...
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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Custom toolbar adding and deleting
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" wrote in message
...
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 QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 389
Default Custom toolbar adding and deleting
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" wrote in message
...
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" wrote in message
...
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 QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Custom toolbar adding and deleting
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" wrote in message
...
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" wrote in message
...
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 QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding an input box to a custom toolbar Charles Excel Programming 3 March 28th 08 07:36 AM
Adding and Deleting custom commandbars whylite Excel Programming 3 September 10th 07 06:14 PM
adding custom images to a button on custom toolbar Mousam Excel Programming 1 July 13th 07 04:28 PM
Adding a Custom Toolbar to an .xla, not to Excel globally RJ Lohan Excel Programming 4 May 5th 04 09:03 AM
Using BeforeClose for deleting a custom toolbar daithimcc[_5_] Excel Programming 3 February 1st 04 10:57 PM


All times are GMT +1. The time now is 08:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement