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

[Guest]

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)

 

[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)

 

[Guest]

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)

 

[Guest]

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....