etech0
asked on
Excel "Personal macro workbook" macro when specific file is opened
Is there a way to have a macro in my Personal Macro workbook run every time a specific excel file is opened? For security reasons, I cannot store the macro in that file.
ASKER
Thanks! Where should I put this code?
Hi, etech0.
An alternative would be to put a simple macro in the file in question - it would call the actual macro in the Personal file.
Regards,
Brian.
An alternative would be to put a simple macro in the file in question - it would call the actual macro in the Personal file.
Regards,
Brian.
Ya that's going to be easier ^^^ because you always have your personal workbook open. You could just have something like:
Then Just put that code in the afteropen sub of your workbook you want to run the macro.
Private Sub Workbook_Open()
Application.Run "'PERSONAL.XLSX'!Makeitso" ' where Makeitso is the name of the macro in your personal workbook you want to run.
end sub
Then Just put that code in the afteropen sub of your workbook you want to run the macro.
ASKER
Can't do that - that file is on the server, and files with macros in them lead to issues on the server.
(See my open question https://www.experts-exchange.com/questions/27419126/Excel-file-on-server-make-trusted-document.html which has yet to receive a feasible solution.)
(See my open question https://www.experts-exchange.com/questions/27419126/Excel-file-on-server-make-trusted-document.html which has yet to receive a feasible solution.)
etech0,
Please see the attached. As it stands, it simply displays a message for each file that opens.
Regards,
Brian.
Start-App-Event.xlsm
Please see the attached. As it stands, it simply displays a message for each file that opens.
Regards,
Brian.
Start-App-Event.xlsm
ASKER
Can I put this into my personal.xlsm?
etech0,
Sure! Don't forget the "EventClass" class module.
Regards,
Brian.
Sure! Don't forget the "EventClass" class module.
Regards,
Brian.
ASKER
I put in it my personal.xlsm
When I open excel I get an error:
User type defined not defined
When I open excel I get an error:
User type defined not defined
etecho,
Did you include...
Brian.
Did you include...
Public WithEvents App As Application
Regards,Brian.
ASKER
no....
Where should I put it?
Where should I put it?
etech0,
In any of the modules - probably best in the one that has your Auto_Open (as I did above).
Regards,
Brian.
In any of the modules - probably best in the one that has your Auto_Open (as I did above).
Regards,
Brian.
etho,
My mistake, that should be in the class module.
Regards,
Brian.
My mistake, that should be in the class module.
Regards,
Brian.
ASKER
Where is the class module?
ASKER
Hi!
I put it in a class module, but the code does not seem to run when I open a workbook!
I put it in a class module, but the code does not seem to run when I open a workbook!
etech0,
Did you include the Auto_Open module? This is not a Class module, just an ordinary one.
If your picture above was from the Personal.xlsm then it does look to be in the correct place. Please post your Personal.xlsm.
Thanks,
Brian.
Did you include the Auto_Open module? This is not a Class module, just an ordinary one.
If your picture above was from the Personal.xlsm then it does look to be in the correct place. Please post your Personal.xlsm.
Thanks,
Brian.
ASKER
I'm trying to upload it, but I keep getting an error!
What error?
ASKER
That the file extension is not valid
OK, EE only allows specific file types to be uploaded, but XLSM is definitely on the list! Is it possible you're trying to upload a shortcut to the Personal.xlsm rather than the file itself?
ASKER
No.
The file is xlsb. Does that make a difference?
The file is xlsb. Does that make a difference?
Yes! For some reason EE doesn't accept those. Probably the easiest option is to simply save your XLSB as an XLSM and upload that.
ASKER
I get the following error:
The extension of one or more files in the archive is not in the list of allowed extensions: _rels/.rels
But it's xlsm!
The extension of one or more files in the archive is not in the list of allowed extensions: _rels/.rels
But it's xlsm!
etech0,
Sounds like EE doesn't like one of its components. I'm not sure what's going on so let's try an alternative. Zip up the XLSM and upload the ZIP. (If that fails, try renaming the XLSM to TXT and zip and upload that ZIP.)
Regards,
Brian.
Sounds like EE doesn't like one of its components. I'm not sure what's going on so let's try an alternative. Zip up the XLSM and upload the ZIP. (If that fails, try renaming the XLSM to TXT and zip and upload that ZIP.)
Regards,
Brian.
ASKER
Still no go - I tried the txt, the zipped xlsm, and the zipped txt.
HELP!
HELP!
etech0,
I've no idea what's going on, so let's try another route...
In 37066211 above I included a screen shot. Could you post two similar ones, please. One showing the Auto_Open module and the other the Class module. Make sure that the Project Explorer part (i.e.e the left hand pane) is expanded so that the file name and modules are visible. If the declaration that goes with the Auto_Open is not visible please take a screenshot of it also.
Thanks,
Brian.
I've no idea what's going on, so let's try another route...
In 37066211 above I included a screen shot. Could you post two similar ones, please. One showing the Auto_Open module and the other the Class module. Make sure that the Project Explorer part (i.e.e the left hand pane) is expanded so that the file name and modules are visible. If the declaration that goes with the Auto_Open is not visible please take a screenshot of it also.
Thanks,
Brian.
etech0,
Silly me, you could try saving it as an XLS. That's not 100% but it would at least let me see you code.
Thanks,
Brian.
Silly me, you could try saving it as an XLS. That's not 100% but it would at least let me see you code.
Thanks,
Brian.
ASKER
"The extension of one or more files in the archive is not in the list of allowed extensions: _rels/.rels"
It still didn't work - I guess it wasn't silly of you!
It still didn't work - I guess it wasn't silly of you!
etech0,
That means that EE is identifying the file as an XLSM. Did you select "Excel 97-2003 Workbook (*.xls)" in the "Save As" dialog?
Thanks,
Brian.
That means that EE is identifying the file as an XLSM. Did you select "Excel 97-2003 Workbook (*.xls)" in the "Save As" dialog?
Thanks,
Brian.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WOW!!!!!
one more question: what does this line do?
Debug.Print "App_WorkbookOpen Started."
one more question: what does this line do?
Debug.Print "App_WorkbookOpen Started."
etech0,
It's completely unnecessary. The Debug.Print command is like MsgBox, except that it writes its output to the Immediate Window instead of a message box. (The Immediate Window is in the same place where you edit your macros - if it's not displayed hit CTRL-G. Its main function is to allow you to enter and execute VBA commands outside a macro.)
Regards,
Brian.
It's completely unnecessary. The Debug.Print command is like MsgBox, except that it writes its output to the Immediate Window instead of a message box. (The Immediate Window is in the same place where you edit your macros - if it's not displayed hit CTRL-G. Its main function is to allow you to enter and execute VBA commands outside a macro.)
Regards,
Brian.
ASKER
Thanks for all your help!
I'd like to know how to do the same thing in Word, but I'm going to begin a new thread for that one.
I'd like to know how to do the same thing in Word, but I'm going to begin a new thread for that one.
Thanks, etech0. I saw the Word question and I've posted a possible answer - no surprise, it's almost identical to the Excel one.
ASKER
Thanks!
Try this:
Open in new window