ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Custom toolbar adding and deleting (https://www.excelbanter.com/excel-programming/413685-custom-toolbar-adding-deleting.html)

Palpha32
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

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

Palpha32
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

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

Palpha32
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


All times are GMT +1. The time now is 06:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com