Update Button with Relative Reference instead of Absolute Reference

[hulashakes]

My 'Update_Button' macro is supposed to do this -

1 - Open a file (Sample.xls)
2 - Update a button (Button 1) to point to a different macro
3 - Save the file
4 - Close the file

More information -
This macro is set to run on a directory and open each file and update each file. This macro is saved in a file called "Macro_Excel.xls" The macro runs fine, but it creates a reference back to Sheet 1 in the Macro_Excel.xls file. I want it to reference to Sheet1 in the file that it is in (Sample.xls), not back to Macro_Excel.xls

The code is this:

Sub Update_Button_To_Absolute()
' This portion generates the list of files in the directory[INDENT]Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "D:\DirectoryOfFiles\*.xls"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop
[/INDENT]Set r = Range("A1")
While r.Value <> ""
Workbooks.Open Filename:="D:\DirectoryOfFiles\" & r.Value
  [INDENT]' This is the actual copy once the file is open
    Windows(r.Value).Activate
    Worksheets("Entry").Shapes("Button 1").OnAction =  "Sheet1.ProjectUpdate_Entry"
'   End of the update button Macro portion
[/INDENT]ActiveWorkbook.Save
ActiveWorkbook.Close
Set r = r.Offset(1, 0)
Wend
End Sub

So, from the code, I expect it to update the button with "Sheet1.ProjectUpdate_Entry".

However, it updates it with "Macro_Excel.xls!Sheet1.ProjectUpdate_Entry" because I am running the macro from Macro_Excel.xls

I have searched MrExcel and google and I can't find anything that helps. I am frustrated. If anyone can help, it would be greatly appreciated.

 

[wolverineb]

I think if you create a variable like this

Dim wkbCurrentWorkbook As Workbook

And then do this after your Activate statement (which I think may be unnecessary, by the way):

Set wkbCurrentWorkbook = ActiveWorkbook

You could then qualify your Sheet1 reference by putting "wkbCurrentWorkbook." before it and it would be sure to refer to the right thing.

 

[hulashakes]

Thanks for your help, I have updated the code with the following:

Sub Update_Button_To_Absolute()
' This portion generates the list of files
Dim F As String
Dim roww As Long
Dim wkbCurrentWorkbook As Workbook
roww = 0
Dim FileLocSpec As String
FileLocSpec = "D:\PARS\*.xls"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop

Set r = Range("A1")
While r.Value <> ""
Workbooks.Open Filename:="D:\PARS\" & r.Value
  ' This is the actual copy once the file is open
    Windows(r.Value).Activate
    Set wkbCurrentWorkbook = ActiveWorkbook
    Worksheets("Entry").Shapes("Button 1").OnAction = "wkbCurrentWorkbook.Sheet1.ProjectUpdate_Entry"

but now, it updates the macro with:

Macro_Excel.xls!wkbCurrentWorkbook.Sheet1.ProjectUpdate_Entry

Am I entering the code wrong?

 

[wolverineb]

Where is the code for the macro you want to run physically located? In the Macro_Excel file or the one that gets opened? Both?

Are you trying to link a shape on the just-opened file to a macro in the Macro_Excel file?

 

[hulashakes]

The Macro I want the object (Button 1) to run is already in the workbook.

For example, in Directory C:\Project_Files\ there are many excel files:
Excel_one.xls
Excel_two.xls
Excel_three.xls
etc...

Each of these files already has an object (Button 1) that already is set to execute a macro, Sheet1.ProjectRemove.

Each of these files also has the macro ProjectUpdate_Entry already in Sheet 1.

All I want to do is update the object (Button 1) so that it now points to the macro Sheet1.ProjectUpdateEntry.

The file Macro_Excel.xls is just where I created the macro to run on all the files in C:\Project_Files\. My intent was that the macro in Macro_Excel.xls would update the object (Button 1) in each file.

 

[wolverineb]

What happens if you change your code to this:

    wkbCurrentWorkbook = ActiveWorkbook
    wkbCurrentWorkbook.Worksheets("Entry").Shapes("Button 1").OnAction = "ThisWorkbook.ProjectUpdate_Entry"

 

[hulashakes]

It wouldn't run with the code you provided. I had to add 'Set' like this:

Set wkbCurrentWorkbook = ActiveWorkbook
wkbCurrentWorkbook.Worksheets("Entry").Shapes("Button 1").OnAction = "ThisWorkbook.ProjectUpdate_Entry"

Still did not work. Changed it to:

Macro_Excel.xls!ThisWorkbook.ProjectUpdate_Entry

 

[wolverineb]

Sorry I left off the Set thing - typing too fast.

Okay. I think if you specify the file name, it won't fill one in for you. Hopefully. Try this one:

wkbCurrentWorkbook.Worksheets("Entry").Shapes("Button 1").OnAction = wkbCurrentWorkbook.Name & "!ProjectUpdate_Entry"

 

[hulashakes]

Ok, it is very close now. However, it is throwing in an extra single quote. Now the error it returns is:

The macro "PARSTesting.xls'!ProjectUpdate_Entry' cannot be found.

PARSTesting.xls is correct, but I think it should leave out the the single quote after xls.

For reference, the previous error was:

The macro 'Macro_Excel.xls!ThisWorkbook.ProjectUpdate_Entry' cannot be found.