Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA assign macro to button from a different file (simple you'd thi
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA assign macro to button from a different file (simple you'd thi
-- HTH RP (remove nothere from the email address if mailing direct) "NX3" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA assign macro to button from a different file (simple you'd thi
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA assign macro to button from a different file (simple you'd
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA assign macro to button from a different file (simple you'd
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA assign macro to button from a different file (simple you'd
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign a macro to a button | Excel Discussion (Misc queries) | |||
Button assign macro breaks with file name change and appears elsew | Excel Discussion (Misc queries) | |||
How do I Assign Macro to a button? | Charts and Charting in Excel | |||
Assign macro to button | Excel Programming | |||
Assign button to run macro | Excel Programming |