Macro linked to Icon

[Smurfit-Stone]

Hello Board,

I have a macro that I want to use on two separate (but identical) files. One is for ordering Cutting Dies and the other for Print Plates. These two file have different file names. The macro is assigned to a icon on my toolbar. It works perfectly, but whenever I click my icon to run the macro it opens the other file. Its like the macro can only be connected or linked to one file at a time. I tried to make the macro non specific by using "active worksheet", but something is not working properly. Can anyone tell me how I can have two files with different name and with the same macro in both...share a single icon on my toolbar? The macro is as follows

Sub SheetsVisible()
Dim wks As Worksheet
Sheets("Order").Activate
For Each wks In ActiveWorkbook.Worksheets
wks.Visible = True
Next wks
End Sub

Thanks in advance.

 

[pbornemeier]

You need to have code to re-create the toolbar/button each time the workbook becomes active. The button in the toolbar "remembers" which file it pointed to when it was created and will work with that file.

The following code will create a toolbar and button each time it is run:

Option Explicit

'The commented code below the line 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)
'    'Although toolbar was created with Temporary:=True it remains until Excel
'    'is closed.  This routine removes the toolbar when the worksheet that creates
'    'it is closed.
'    DeleteToolbar
'End Sub
'---------------------------------------------------------------
'The commented code above the line must added to the 'ThisWorkbook'
'code page and uncommented.
'
'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.

Sub AddNewToolBar()

    'This procedure creates a new temporary toolbar.
    Dim comBar As CommandBar, comBarContrl As CommandBarControl
    
    'Create a new floating toolbar and make it visible.
    On Error Resume Next
    'Delete the toolbar if it already exists
    CommandBars("DEMO TOOLBAR").Delete
    On Error GoTo ErrorHandler
    
    Set comBar = CommandBars.Add(Name:="DEMO TOOLBAR", Position:= _
    msoBarTop, Temporary:=True)
    comBar.Visible = True
    
    'Create a button
    Set comBarContrl = comBar.Controls.Add(Type:=msoControlButton)
    With comBarContrl
        .FaceId = 1657
        .Caption = "Button 1"
        .Style = msoButtonIconAndCaption
        .TooltipText = "msoControlButton 1"
         'the onaction line tells the button to run a certain macro
        .OnAction = "ProcessButton1"
    End With
    
GoTo End_Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & vbCr & Err.Description
    Exit Sub

End_Sub:

End Sub

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

 

[Smurfit-Stone]

Thanks Phil...this is wonderful. Your a genius.