Check Existing Button
I'm using the following VBA code to create a button in Excel that runs a
macro. The code is in the Workbook_Open procedure. I use the .Delete method in the Workbook_Beforeclose procedure to remove the button when the workbook is closed. Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ Temporary:=True) oCtl.Caption = "My Button." oCtl.OnAction = "My Macro" oCtl.FaceId = 270 oCtl.Style = msoButtonIconAndCaption End With Is there a way using VBA to see if the button already exists so as not to create a duplicate button? Thanks in advance.. Mike |
Check Existing Button
Don't bother, just delete it regardless
On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("myButton").Delete On Error Goto 0 before this code -- HTH RP (remove nothere from the email address if mailing direct) "Mike" wrote in message ... I'm using the following VBA code to create a button in Excel that runs a macro. The code is in the Workbook_Open procedure. I use the .Delete method in the Workbook_Beforeclose procedure to remove the button when the workbook is closed. Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ Temporary:=True) oCtl.Caption = "My Button." oCtl.OnAction = "My Macro" oCtl.FaceId = 270 oCtl.Style = msoButtonIconAndCaption End With Is there a way using VBA to see if the button already exists so as not to create a duplicate button? Thanks in advance.. Mike |
All times are GMT +1. The time now is 09:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com