VBA assign macro to button from a different file (simple you'd thi

[NX3]

I have to modify 2000 files so I've written a master file that loops through
a list, importing modules and forms into the 2000 files it opens and closes.
On each file when open it draws a button on worksheet. I then want to assign
a macro to the button called "ModifyMenu" . The code for 'Modifymenu' is
imported into file_01.xls and is a unique name. It does not exist is
master.xls, so no confusion.

The code for assigning the code is as follows :

Windows("file_01.xls").Activate
ActiveSheet.Shapes("Button").Select
or
Workbooks("file_01.xls").Sheets("1 B").Shapes("Button").Select


Then

Selection.OnAction = "ModifyMenu"
(This links to master.xls!ModifyMenu not file_01.xls)

Selection.OnAction = "file_01.xls!ModifyMenu"
(This fails, runtime 1004. 'Unable to set the OnAction property of the
button class)

Selection.OnAction = ThisWorkbook.Name & "!ModifyMenu"
(This links to master. MAster is the active code even though 'file_01.xls'
is the active worksheet)

Variations on the above seem to result in the same 1004 error or linking
back to master regardless of the fact I'm trying to link to file_01.xls.

Any help much appricated.
Regards
Nick

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)


"NX3" <[email protected]> wrote in message
news:[email protected]...
> I have to modify 2000 files so I've written a master file that loops
through
> a list, importing modules and forms into the 2000 files it opens and
closes.
> On each file when open it draws a button on worksheet. I then want to
assign
> a macro to the button called "ModifyMenu" . The code for 'Modifymenu' is
> imported into file_01.xls and is a unique name. It does not exist is
> master.xls, so no confusion.
>
> The code for assigning the code is as follows :
>
> Windows("file_01.xls").Activate
> ActiveSheet.Shapes("Button").Select
> or
> Workbooks("file_01.xls").Sheets("1 B").Shapes("Button").Select
>
>
> Then
>
> Selection.OnAction = "ModifyMenu"
> (This links to master.xls!ModifyMenu not file_01.xls)
>
> Selection.OnAction = "file_01.xls!ModifyMenu"
> (This fails, runtime 1004. 'Unable to set the OnAction property of the
> button class)
>
> Selection.OnAction = ThisWorkbook.Name & "!ModifyMenu"
> (This links to master. MAster is the active code even though 'file_01.xls'
> is the active worksheet)
>
> Variations on the above seem to result in the same 1004 error or linking
> back to master regardless of the fact I'm trying to link to file_01.xls.
>
> Any help much appricated.
> Regards
> Nick
>

[Bob Phillips]

Nick,

This code

Selection.OnAction = "file_01.xls!ModifyMenu"

works fine if it is a button from the forms toolbar. My guess is that you
are using control toolbox buttons, which require event code.

To draw a forma toolbar button in code, use

With ActiveSheet
.Buttons.Add(372.75, 46.5, 126, 63).Select
Selection.OnAction = "Macro1"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"NX3" <[email protected]> wrote in message
news:[email protected]...
> I have to modify 2000 files so I've written a master file that loops
through
> a list, importing modules and forms into the 2000 files it opens and
closes.
> On each file when open it draws a button on worksheet. I then want to
assign
> a macro to the button called "ModifyMenu" . The code for 'Modifymenu' is
> imported into file_01.xls and is a unique name. It does not exist is
> master.xls, so no confusion.
>
> The code for assigning the code is as follows :
>
> Windows("file_01.xls").Activate
> ActiveSheet.Shapes("Button").Select
> or
> Workbooks("file_01.xls").Sheets("1 B").Shapes("Button").Select
>
>
> Then
>
> Selection.OnAction = "ModifyMenu"
> (This links to master.xls!ModifyMenu not file_01.xls)
>
> Selection.OnAction = "file_01.xls!ModifyMenu"
> (This fails, runtime 1004. 'Unable to set the OnAction property of the
> button class)
>
> Selection.OnAction = ThisWorkbook.Name & "!ModifyMenu"
> (This links to master. MAster is the active code even though 'file_01.xls'
> is the active worksheet)
>
> Variations on the above seem to result in the same 1004 error or linking
> back to master regardless of the fact I'm trying to link to file_01.xls.
>
> Any help much appricated.
> Regards
> Nick
>

[NX3]

Thanks for replying Bob.

The code to draw the button and assign are as follows :

With ActiveSheet.Buttons.Add(Range("A2:H2").Width, Range("I1:I2").Height,
Range("I2").Width, Range("I4").Top)
.Name = "ModifySheet"
.Caption = "Modify Worksheet"
.OnAction = "ModifyMenu"
End With

The problem is that when I click the button its looking for 'ModifyMenu' in
the master file not the activework book. To me this looks more or less the
same as the sample and various other postings I've read.

If the button is in the master file I can assign the other way e.g

Selection.OnAction = "'H810-26011006_Oct-05.xls'!ModifyMenu"

So a really dumb solution would be draw the button in file_01 etc cut and
paste it into master, use the line above which works, then cut and paste it
back to file_01. This works but it seems a very silly way of doing it :-\

The question is, how from master can I draw a button and assign code in
file_01 ? As per my original examples they all link back to master which
doesn't have the code even.... I've been pulling my hair out on this one to
say the least !!

TIA

[Bob Phillips]

How about this

With ActiveSheet.Buttons.Add(Range("A2:H2").Width, Range("I1:I2").Height,
Range("I2").Width, Range("I4").Top)
.Name = "ModifySheet"
.Caption = "Modify Worksheet"
.OnAction = ActiveWorkbook.Name & "!ModifyMenu"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"NX3" <[email protected]> wrote in message
news:[email protected]...
> Thanks for replying Bob.
>
> The code to draw the button and assign are as follows :
>
> With ActiveSheet.Buttons.Add(Range("A2:H2").Width, Range("I1:I2").Height,
> Range("I2").Width, Range("I4").Top)
> .Name = "ModifySheet"
> .Caption = "Modify Worksheet"
> .OnAction = "ModifyMenu"
> End With
>
> The problem is that when I click the button its looking for 'ModifyMenu'
in
> the master file not the activework book. To me this looks more or less the
> same as the sample and various other postings I've read.
>
> If the button is in the master file I can assign the other way e.g
>
> Selection.OnAction = "'H810-26011006_Oct-05.xls'!ModifyMenu"
>
> So a really dumb solution would be draw the button in file_01 etc cut and
> paste it into master, use the line above which works, then cut and paste
it
> back to file_01. This works but it seems a very silly way of doing it :-\
>
> The question is, how from master can I draw a button and assign code in
> file_01 ? As per my original examples they all link back to master which
> doesn't have the code even.... I've been pulling my hair out on this one
to
> say the least !!
>
> TIA

[NX3]

I'd tried ActiveWorkbook.Name actually, sorry not mentioned it. Same
difference as

Selection.OnAction = "file_01.xls!ModifyMenu"

This fails, runtime 1004. 'Unable to set the OnAction property of the button
class'

It seems to have a problem because the active code is in master but the
activesheet is in file_01. When you assign a macro to a object if the code is
in the same file then you don't reference the file. I'm referencing a button
and code in another file from master and that's what causes the problem.

If I create the button in any file other than the one I want it in I can
reference the correct code. Then cut and paste the button to the correct file
with correct link intact. e.g the code above....

"Bob Phillips" wrote:

> How about this
>
> With ActiveSheet.Buttons.Add(Range("A2:H2").Width, Range("I1:I2").Height,
> Range("I2").Width, Range("I4").Top)
> .Name = "ModifySheet"
> .Caption = "Modify Worksheet"
> .OnAction = ActiveWorkbook.Name & "!ModifyMenu"
> End With
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "NX3" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for replying Bob.
> >
> > The code to draw the button and assign are as follows :
> >
> > With ActiveSheet.Buttons.Add(Range("A2:H2").Width, Range("I1:I2").Height,
> > Range("I2").Width, Range("I4").Top)
> > .Name = "ModifySheet"
> > .Caption = "Modify Worksheet"
> > .OnAction = "ModifyMenu"
> > End With
> >
> > The problem is that when I click the button its looking for 'ModifyMenu'
> in
> > the master file not the activework book. To me this looks more or less the
> > same as the sample and various other postings I've read.
> >
> > If the button is in the master file I can assign the other way e.g
> >
> > Selection.OnAction = "'H810-26011006_Oct-05.xls'!ModifyMenu"
> >
> > So a really dumb solution would be draw the button in file_01 etc cut and
> > paste it into master, use the line above which works, then cut and paste
> it
> > back to file_01. This works but it seems a very silly way of doing it :-\
> >
> > The question is, how from master can I draw a button and assign code in
> > file_01 ? As per my original examples they all link back to master which
> > doesn't have the code even.... I've been pulling my hair out on this one
> to
> > say the least !!
> >
> > TIA
>
>
>

[jordan2322]

Hi Guys,

Not sure if you're still stuck with this, but I just had the same issue.
The trick seemed to be the ' character at the beginning and end of the target workbook's name
i.e. the macro assignment should look something like "'target_workbook.xls'!my_macro"

so I solved it by doing the following,

This example assumes a shapes button named "GO_Button" on a sheet named "My_Sheet" in a another workbook OTHER than ThisWorkBook (master workbook)
Let's assume the Target workbook's name is "Remote_Book.xls"

Dim Target_Book as Workbook
Dim Target_Macro as String
Dim My_Target_Macro as String

' assign a variable to your target workbook
set Target_Book = Activeworkbook

' identify the macro you want
My_Target_Macro = "Some_Action"

' assign the target workbooks name and macro to a variable - note the " ' " characters at either end of the workbook's name
Target_Macro = "'" & Target_Book.Name & "'!My_Target_Macro"

' if you debug.print Target_Macro at this point you should get the following,
'Remote_Book.xls'!Some_Action

' assign the macro to the button in the target workbook using a single variable
Target_Book.Sheets("MY_SHEET").Shapes("GO_Button").OnAction = Target_Macro

' change some text on the button in the target workbook
Target_Book.Sheets("MY_SHEET").Shapes("GO_Button").TextFrame.Characters.Text = "Some new text."

I realise that using 3 variables to define 2 things is probably not best coding practice, but in my case I have a loop where I redefine certain macro names and workbooks.
So this approach made it easier for me when assigning the .OnAction = Target_Macro to a single variable that is essential made up from the two other variables.

Hope this helps.

I'd tried ActiveWorkbook.Name actually, sorry not mentioned it. Same
difference as

Selection.OnAction = "file_01.xls!ModifyMenu"

This fails, runtime 1004. 'Unable to set the OnAction property of the button
class'

It seems to have a problem because the active code is in master but the
activesheet is in file_01. When you assign a macro to a object if the code is
in the same file then you don't reference the file. I'm referencing a button
and code in another file from master and that's what causes the problem.

If I create the button in any file other than the one I want it in I can
reference the correct code. Then cut and paste the button to the correct file
with correct link intact. e.g the code above....

"Bob Phillips" wrote:

> How about this
>
> With ActiveSheet.Buttons.Add(Range("A2:H2").Width, Range("I1:I2").Height,
> Range("I2").Width, Range("I4").Top)
> .Name = "ModifySheet"
> .Caption = "Modify Worksheet"
> .OnAction = ActiveWorkbook.Name & "!ModifyMenu"
> End With
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "NX3" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for replying Bob.
> >
> > The code to draw the button and assign are as follows :
> >
> > With ActiveSheet.Buttons.Add(Range("A2:H2").Width, Range("I1:I2").Height,
> > Range("I2").Width, Range("I4").Top)
> > .Name = "ModifySheet"
> > .Caption = "Modify Worksheet"
> > .OnAction = "ModifyMenu"
> > End With
> >
> > The problem is that when I click the button its looking for 'ModifyMenu'
> in
> > the master file not the activework book. To me this looks more or less the
> > same as the sample and various other postings I've read.
> >
> > If the button is in the master file I can assign the other way e.g
> >
> > Selection.OnAction = "'H810-26011006_Oct-05.xls'!ModifyMenu"
> >
> > So a really dumb solution would be draw the button in file_01 etc cut and
> > paste it into master, use the line above which works, then cut and paste
> it
> > back to file_01. This works but it seems a very silly way of doing it :-\
> >
> > The question is, how from master can I draw a button and assign code in
> > file_01 ? As per my original examples they all link back to master which
> > doesn't have the code even.... I've been pulling my hair out on this one
> to
> > say the least !!
> >
> > TIA
>
>
>