Excel Macro help

[Dayin]

Few questions we've been having around the office. As the most junior on the programming team I get the short straw with the IT stuff cause our IT guy only comes once every couple of weeks.

Our accounting team has been utilizing more macros in their Excel work (2007) and we're having problems with editing macros. A macro won't work the way they want it to so they'll go to edit or delete it and come back with an error about hidden workbooks.

Did a little research and I think the problem comes from the settings in the workbook itself about having macros visible or enabled for that particular file because when I go to the macro settings and bring up their individual file they're working on the macro list is empty. (This also might by why the macros aren't working the way they want them to.)

So I think I need advice on how to edit macro settings for individual documents. If I haven't identified the problem correctly I would appreciate any information on how to fix my problem.

 

[arkamw]

AFAIK, there's isn't really an easy way to edit macro settings for individual files. You can change the security level in Excel to something like low which would enable most macros by default but this isn't typically a recommended practice.

It's also possible that the user has disabled macros from running (i.e. clicked the Disable button at the opening dialog) and then can't edit / run any.

Something else to look at is if the macro has the designation Private. You can check this by opening the code window (ALT-F11) and then double-clicking the various sheets and modules in the left-hand pane. If any of the various modules have Private Sub listed at the top of the subroutine, it won't show up in the macro list (Tools > Macros), but will be available to run, and be available to edit through the code screen.

Also, for hidden workbooks, check under Window > Hide or Window > Unhide.

 

[Dayin]

Alright, thanks, that gives me some things to try.

In the event that I'm led to believe they disabled macros on accident, is there a way to change that setting? I've tried closing out and reloading and I can't get the dialog to come back up for the file(s) in question.

 

[arkamw]

If the dialog doesn't come up at all, there's either a setting that I don't know about, or there just isn't a macro there (check the code screen to know for sure). Excel won't put throw up a macro warning unless there's a macro there to warn about.

One more thing, I don't have 2007, I only have 2003 so I can't speak to any changes made.