Toolbar Macro Assignment Gets Whacked

[selus]

I have created a macro that does some processing on a main worksheet and puts the results on a different worksheet in the same workbook. This macro is activated by a custom menu bar entry I created (Excel 2003) using the Customize functionality.

At the end of the macro, I issue a command to save the entire workbook. I then activate the results worksheet and issue a SaveAs command in the macro to prompt for the location and name of the CSV file to be exported.

This all works great.

The problem occurs when I open the original workbook (not the one created in the SaveAs). When I go to run the macro again from the menu bar, I get a message telling me the macro can't be found. When I look at the set up of the menu bar entry it now shows the name of the macro with the filename and path to the CSV file appended to the beginning (e.g. "c:\grnt0809.csv!macro3" instead of "macro3").

What's up and how do I stop this from happening?


Thanks in advance.

 

[pbornemeier]

The button points to the file where the macro it refers to was last saved (in this case the .csv file). Unfortunately, there is no code in a .csv file.

Instead of having a permanent toolbar, add a macro which creates the toolbar and buttons when you open the workbook and another which deletes the toolbar when the file is closed.

I use the following code in one of my workbooks for that purpose:

'The commented code below must be uncommented and
'added to the 'ThisWorkbook' code page
'---------------------------------------------------------------
'Option Explicit
'Private Sub Workbook_Open()
'    AddNewToolBar
'End Sub
'
'Private Sub Workbook_BeforeClose(Cancel As Boolean)
'    'The toolbar was created with Temporary:=True it remains until Excel
'    'is closed.  This routine removes the toolbar when the worksheet that
'    'create the toolbar is closed.
'    DeleteToolbar
'End Sub
'---------------------------------------------------------------
'The commented code above must be uncommented and
'added to the 'ThisWorkbook' code page

'Pictures of most buttons by faceid can be found at:
'http://www.kebabshopblues.co.uk/2007/01/04/visual-studio-2005-tools-for-office-commandbarbutton-faceid-property/

Sub AddNewToolBar()

    'This procedure creates a new temporary toolbar.
    Dim comBar As CommandBar, comBarContrl As CommandBarControl
    
    'Delete the toolbar if it already exists
    On Error Resume Next
    CommandBars("TOOLBAR NAME").Delete
    On Error GoTo ErrorHandler
    
    'Create a new toolbar and make it visible.
    Set comBar = CommandBars.Add(Name:="TOOLBAR NAME", Position:= _
    msoBarTop, Temporary:=True)
    comBar.Visible = True
    
    'Create a button
    Set comBarContrl = comBar.Controls.Add(Type:=msoControlButton)
    With comBarContrl
        .FaceId = 1657                             'Picture on the button
        .Caption = "Process 'PROCESSED Worksheet'" 'What buttomn text reads
        .Style = msoButtonIconAndCaption           'How button looks
        .TooltipText = "Look up WAF values"        'Button tooltip
        .OnAction = "ProcessProcessedList"         'Bacro triggered by button
    End With
    
    'Create a button
    Set comBarContrl = comBar.Controls.Add(Type:=msoControlButton)
    With comBarContrl
        .FaceId = 29
        .Style = msoButtonIconAndCaption
        .Caption = "Process 'TESTS Worksheet'"
        .TooltipText = "Lookup Test info for WAF #s listed on the TESTS worksheet"
        .OnAction = "ProcessTestsWorksheet"
    End With
Exit Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & vbCr & Err.Description
    Exit Sub
End Sub
 
 'Code to delete the toolbar called by workbook close:
Sub DeleteToolbar()
    On Error Resume Next
    CommandBars("TOOLBAR NAME").Delete
End Sub

 

[bowlinbd]

I've been trying this code and get the following error:

Error 91: Object Variable or With Block Variable Not Set.

Am I doing something wrong?


'The commented code below must be uncommented and
'added to the 'ThisWorkbook' code page
'---------------------------------------------------------------
Option Explicit
Private Sub Workbook_Open()
AddNewToolBar
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' 'The toolbar was created with Temporary:=True it remains until Excel
' 'is closed. This routine removes the toolbar when the worksheet that
' 'create the toolbar is closed.
DeleteToolbar
End Sub
'---------------------------------------------------------------
'The commented code above must be uncommented and
'added to the 'ThisWorkbook' code page
'Pictures of most buttons by faceid can be found at:
'http://www.kebabshopblues.co.uk/2007/01/04/visual-studio-2005-tools-for-office-commandbarbutton-faceid-property/
Sub AddNewToolBar()
'This procedure creates a new temporary toolbar.
Dim comBar As CommandBar, comBarContrl As CommandBarControl

'Delete the toolbar if it already exists
On Error Resume Next
CommandBars("COMBINE SHEETS").Delete
On Error GoTo ErrorHandler

'Create a new toolbar and make it visible.
Set comBar = CommandBars.Add(Name:="COMBINE SHEETS", Position:= _
msoBarTop, Temporary:=True)
comBar.Visible = True

'Create a button
Set comBarContrl = comBar.Controls.Add(Type:=msoControlButton)
With comBarContrl
.FaceId = 1657 'Picture on the button
.Caption = "Process 'Combine Sheets'" 'What buttomn text reads
.Style = msoButtonIconAndCaption 'How button looks
.TooltipText = "Execute Macro" 'Button tooltip
.OnAction = "Combine_Sheets" 'Bacro triggered by button
End With

' 'Create a button
' Set comBarContrl = comBar.Controls.Add(Type:=msoControlButton)
' With comBarContrl
' .FaceId = 29
' .Style = msoButtonIconAndCaption
' .Caption = "Process 'TESTS Worksheet'"
' .TooltipText = "Lookup Test info for WAF #s listed on the TESTS worksheet"
' .OnAction = "ProcessTestsWorksheet"
' End With
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub

'Code to delete the toolbar called by workbook close:
Sub DeleteToolbar()
On Error Resume Next
CommandBars("COMBINE SHEETS").Delete
End Sub

 

[pbornemeier]

Did you uncomment and put the part of the code at the top of the page between the mines on the "ThisWorkbook" code page? It should still be commented if you left it in the Module area.
What line/word was highlighted when the error occurred? It worked OK on my machine.

 

[bowlinbd]

I uncommented the lines and placed it on the ThisWorkbook code page. No line is getting highligted on the error, that's why I'm having the problem figuring out where the problem is. Are you using 2007? I'm using 2003.

 

[pbornemeier]

I am using 2003. I tried the code as you have it (assuming the code between the lines is uncommented and actually on the "ThisWorkbook" code sheet and everything else is in a Module sheet in the same workbook) and it created and deleted your tool bar and button and complained when the "Combine_Sheets" macro was not present.
I did find a problem in my original code that I corrected below, but what you have above worked on my machine. Use Alt-F8 and run the "AddNewToolBar" macro manually to see if it generates the toolbar & button. Ditto for "DeleteToolbar" to see if it deletes it. If AddNewToolBar doesn't work, put a breakpoint on the On Error GoTo ErrorHandler line and manually trigger it then step through it.

This code for the 'This Workbook' module:

Option Explicit
Private Sub Workbook_Open()
     AddNewToolBar
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' 'The toolbar was created with Temporary:=True it remains until Excel
' 'is closed. This routine removes the toolbar when the worksheet that
' 'create the toolbar is closed.
     DeleteToolbar
End Sub

This code for a module in the same workbook:

Option Explicit

'Pictures of most buttons by faceid can be found at:
'http://www.kebabshopblues.co.uk/2007/01/04/visual-studio-2005-tools-for-office-commandbarbutton-faceid-property/

Sub AddNewToolBar()
    'This procedure creates a new temporary toolbar.
    Dim comBar As CommandBar, comBarContrl As CommandBarControl
    
    'Delete the toolbar if it already exists
    On Error Resume Next
    CommandBars("COMBINE SHEETS").Delete
    On Error GoTo ErrorHandler
    
    'Create a new toolbar and make it visible.
    Set comBar = CommandBars.Add(Name:="COMBINE SHEETS", Position:= _
    msoBarTop, Temporary:=True)
    comBar.Visible = True
    
    'Create a button
    Set comBarContrl = comBar.Controls.Add(Type:=msoControlButton)
    With comBarContrl
        .FaceId = 1657 'Picture on the button
        .Caption = "Process 'Combine Sheets'" 'What buttomn text reads
        .Style = msoButtonIconAndCaption 'How button looks
        .TooltipText = "Execute Macro" 'Button tooltip
        .OnAction = "Combine_Sheets" 'Bacro triggered by button
    End With
    
    GoTo End_Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & vbCr & Err.Description
End_Sub:
    
End Sub

'Code to delete the toolbar called by workbook close:
Sub DeleteToolbar()
    On Error Resume Next
    CommandBars("COMBINE SHEETS").Delete
End Sub

 

[bowlinbd]

Worked perfectly that time. Thank you very much.

 

[pbornemeier]

An additional option is to have the toolbar visible only when the workbook that created them is active. Here is a note about the changes to make for that:

'Use Workbook_Activate() and Workbook_Deactivate()
'if the added menu items should only be visible while the
'workbook that created them is active
'
'Use Workbook_Open() and Workbook_BeforeClose(Cancel As Boolean)
'if the menu items should be available while the workbook
'that created them is open.

 

[bowlinbd]

An additional option is to have the toolbar visible only when the workbook that created them is active. Here is a note about the changes to make for that:

'Use Workbook_Activate() and Workbook_Deactivate()
'if the added menu items should only be visible while the
'workbook that created them is active
'
'Use Workbook_Open() and Workbook_BeforeClose(Cancel As Boolean)
'if the menu items should be available while the workbook
'that created them is open.

I think that will definitely help eliminate confusion so that the button only shows up on the correct workbook. Thanks again for the assistance.