Macro linked to Icon

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884
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:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top