My Macros are making my cry :-(

[Leila-Marie]

Hi all,

My macros are making me cry - it's awful.

I accidentally saved some of them to "this worksheet" before I was told here that I need to be saving them to XLSTART.

I've been doing that, but nothing is working... I don't know where XLSTART is, to see what's in there...

I don't know if I need to be saving my worksheets INTO XLSTART to make the macros work, instead of where I'd be saving them normally?

I'm so confused that I'm tempted to delete all the macros I have set up and start afresh.

But I don't know if that will help, cos I may be doing it wrong again...?

Unfortunately I'm under time-pressure with this work assignment, so every macro that comes up with an error message is wracking my nerves - which is never good for trying to analytically find the source of your error...

Even new macros that I'm recording in an opened worksheet are "doing nothing"... Very odd. (Tho that may be cos I had an older version of Excel open too, and I think that was "hogging" the XLSTART file/ folder.

Do I need to shut off Excel and start it up again, before macros are "available" for new worksheets?

Oh, and when I try and use macros that are already saved in the XLSTART folder, Excel tells me that that folder is "hidden" (or whatever the correct translation is - I'm working in Europe currently, using a foreign language version of Excel, so I'm guessing at the right vocabulary here...) It tells me to "un-hide" / "make visible" the XLSTART folder, but I don't know where it is, or how to un-hide it...

Do these questions make sense to anyone here? I'd be so happy to stop being on the verge of tears... boohoo

A thousand million trillion thanks,

Leila-Marie

 

[DeusXv]

Here's how you find the XLSTART folder:

Quick tip: Find Excel's XLStart folder fast - TechRepublic

And are you saving the macros as .xlsm and not an ordinary excel sheet extension like .xlsx if you don't save them as .xlsm then you won't be able to run the macro on that sheet, and if you are adding them to the add-ins folder then they need to be .xlam.

Hope this helps

 

[sheetspread]

XLSTART is where you find the personal macro workbook. It's easiest to just record a macro and select Store Macro in personal macro workbook. Then you can insert modules which will also be saved there if you write or copy/paste code. To reach XLSTART through Windows Explorer or My Computer:

C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB (Windows 7)

C:\Documents and Settings\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB (XP)


If a macro is saved to the personal macro workbook it can be used anywhere, otherwise just on the open file. To solve your problem try copying each macro and pasting it to a module inserted in the personal macro workbook.

 

[Leila-Marie]

If a macro is saved to the personal macro workbook it can be used anywhere, otherwise just on the open file. To solve your problem try copying each macro and pasting it to a module inserted in the personal macro workbook.

Hi both,

Thank you for your tips.

Sorry - I think I got something confused - I am saving the macros to "personal macro workbook" (PERSONAL.XLSB).

I assumed that was the same as XLSTART (but obviously not).

The PERSONAL.XLSB file is what the error message is referring to - it's telling me that PERSONAL.XLSB is "hidden" and I must un-hide it. Any ideas?

XXX

L-M

 

[DeusXv]

Saw this somewhere try this:

You need to unhide the personal.xlsb

On the View tab, in the Window group, click Unhide.

Edit the macros, then hide the workbook

 

[Leila-Marie]

C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB (Windows 7)

Right - I've found the file now... But it's not marked as "hidden"...

 

[Leila-Marie]

So would I un-hide and then re-hide PERSONAL.XLSB each time I need to add macros to it?

 

[Leila-Marie]

Here's how you find the XLSTART folder:

Quick tip: Find Excel's XLStart folder fast - TechRepublic

And are you saving the macros as .xlsm and not an ordinary excel sheet extension like .xlsx if you don't save them as .xlsm then you won't be able to run the macro on that sheet, and if you are adding them to the add-ins folder then they need to be .xlam.

Hope this helps

I'm not sure I'm saving them "as" anything... At the moment I'm just using the simple "record" macro function, to make sure that I'm not making any complex mistakes. When I click on "stop recording" a dialogue window pops up, asking me where I want to save the macro to (I'm choosing personal macro workbook) but it's not asking me as which extension I'd like to save it as (I think...)

 

[Leila-Marie]

Hmmm, I have now tried "hiding" PERSONAL.XLSB... which worked... So, err, can I un-hide it again? Cos I literally can't see it anywhere now.

 

[DeusXv]

So would I un-hide and then re-hide PERSONAL.XLSB each time I need to add macros to it?

Unhide the file and then see if the macros work and then hide it again and if they fail, then leave it open until such a time that you will be writing the macros and saving them to specific worksheets. Like I mentioned above when I was taking about the different file extensions.

Hope this helps.