|
Bizarre problem assigning macro to object; works in one folder, not another.
Please Register to Remove these Ads
***2nd UPDATE: The problem was apparently that I used a hyphen in the file name...***
***UPDATE: The problem seems to be in the name of the file, but I still do not understand what would make one file name work and another not work.***
Hello,
I've been trying for hours to figure out why I'm getting this problem, but I've had no success. I am using Excel 2003 on XP.
I have a workbook which runs VBA code to generate another workbook. In the new workbook are several objects, and I use VBA to assign macros to them.
The macros that I assign are originally in a module in the generating workbook for convenience's sake, but as I am generating the new workbook I export that module and then import the exported file into my new workbook.
Now, my new workbook wants to reference the macros stored in the old workbook. To resolve this issue, I assign .OnAction to the macro name, prefixed by the new workbook name and an exclamation point. (...There may be a potential solution here if you've got a better way of doing what I do here...) It looks something like this:
Code:
set checkboxobject = ws.CheckBoxes.Add(100,100,100,100)
checkboxobject.OnAction = wb.Name & "!SomeMacro"
This was working perfectly until I changed the directory where the new workbook was being saved at. Now when I click the objects, they don't work, and instead say that the workbook could not be found. Here's why: for some reason when I look at it's macro assignment by manually selecting an object it shows something like the following instead of just [wb.Name]!SomeMacro
Code:
'This next line is exactly what I see, apostrophes included.
'C:\Documents and Settings\User\My Documents\KUSCC-JN-90617-Frac Selector.09-6-''30_14.34.xls'''!DisplayPoolVial
'This is an example of what the new path might look like; notice that the
'actual file name is DIFFERENT than what excel displays in the Assign
'Macro dialog.
C:\Documents and Settings\User\Desktop\JS Test Folder\FracProcess\Folder\Folder\Folder\Folder\Folder\KUSCC-JN-090617-Frac Selector.09-06-30_14.34.xls
The new path is actually quite long. Could this be part of the problem? Like I said, it works fine when I use other, shorter paths, but I need to be able to use this longer path that I have.
I know this is all very long and maybe convuluted. If you've gotten this far I really appreciate it; thanks for the help.
JS
Last edited by jdsmithks; 06-30-2009 at 06:44 PM.
Reason: 2nd Update.
|
Linear Mode
