Macro defaulting to original filepath after sheet extracted to new workbook

[cannuk]

Hello all...


I have a workbook (WRAPR.xlsm) that has several worksheets. One worksheet in particular (Webinar Planner) needs to be sent to a coworker regularly. I have written code in VBA that does a great job of extracting that single worksheet, saving it to a new workbook (WRAPR & <today's date>) and emailing it to my coworker. That part is working great.


Here's my challenge: In the original multisheet workbook, I have written very simple macros to hide/unhide specific columns and attached those macros to buttons (inserted shapes with labels and attached macros) labelled "Hide columns" and "Show columns" respectively. (See code below as an example)

Sub Links_UnHide()
Workbooks("WRAPR.xlsm").Sheets("Webinar Planner").Range("B:B").Columns.Hidden = False
End Sub

The two subs when attached to the buttons work just fine when launched from the original, multi-sheet workbook.

However, after running my code to extract that one worksheet to a new workbook and sending it, things go bad. To test it, I send it to myself. Upon opening, I push through the warnings of "Enable Editing" and "Enable Content". Those I understand and can be dealt with my coaching my coworker to accept them.


Then I get the error "This workbook contains links to other data sources...." . The options are to "Continue" or "Edit Links". This is the part I am struggling with. I can't ask my coworker to "Edit Links" every day! Clicking on "Continue" allows the new, single sheet workbook to open. The macro buttons appear as expected, but when clicked throw the error : "C:\Temp\GTW_Reports\WRAPR.xlsm could not be found....."


When I open the Developer window and run the code from there, the columns hide and unhide as expected. But the buttons on the extracted sheet are clearly looking to the path of the original workbook.


Can anyone advise me how to make the code in the original multi-sheet workbook, that is embedded in the "Webinar Planner" sheet so that when that sheet is extracted out to it's own workbook and sent to other users, the buttons will look to the code it it's workbook, or at least dynamically look to the path of the workbook instead of the path of the original file?


I highly expect that I'm missing something quite simple :)


Thanks all


Cannuk

[Paul_Hossler]

Try this

1. I made MultiSheet.xlsm and on Sheet 1 I added 2 ActiveX buttons with the macros below. These go in the Sheet code page, not a Standard Module

Option Explicit

Private Sub CommandButton1_Click()
    Me.Cells(1, 1).CurrentRegion.Interior.Color = vbRed
End Sub

Private Sub CommandButton2_Click()
    Me.Cells(1, 1).CurrentRegion.Interior.Color = vbGreen
End Sub

2. I manually copied Sheet 1 to a new workbook and saved as an XLSM

3. Closed both and opened just CopySheet.xlsm and the code for the 2 buttons went with the sheet with no linking

[snb]

The macro

Sub Links_UnHide()
   Workbooks("WRAPR.xlsm").Sheets("Webinar Planner").Range("B:B").Columns.Hidden = False
End Sub

should be written as:

Sub Links_UnHide()
   Thisworkbook.Sheets("Webinar Planner").Columns(2).Hidden = False
End Sub

[cannuk]

<shakes head embarrassed> Yup, I knew I was missing something simple. Thanks Paul... of course... attach the code to the button, not the workbook. DUH! Worked like a charm.

PS: Not bad for a linguistics major huh?