Question about recording an excel macro

[bthumble]

Hello and thank you for reading my post.

Created a macro using the recorder so that I can format a cell quickly. This involves Fill color green, font white and white boarder around the cell. After creating it and assigning a button in the quick access toolbar it does not work. Error says it can't file the file and is it possible it was moved renamed or deleted. Any ideas to what I am doing wrong.

Thanks.

 

[Joe4]

Did you save the code in your Personal Macro Workbook?

If not, it is only in the workbook that you recorded it in, which means that code will not be available unless you have that particular file open.

 

[bthumble]

No I did not. How do I I save it there?

 

[Micron]

Ribbon buttons can only call functions - I presume that is true for QAT buttons as well. Try changing your Sub (which is what the recorder would create) to a Public Function. IIRC, recorder would create sub in a standard module, so that part (standard module) should be ok.

EDIT - good point about personal. I presumed the problem was in the same workbook as the macro was recorded in.

 

[Joe4]

See here: Copy your macros to a Personal Macro Workbook

 

[Micron]

I see that the QAT will run macros, so it's not the same as the Ribbon. However, the default choice is "all workbooks". Perhaps the current workbook was chosen instead of all. I tested and can call a sub (macro) in a standard module from other workbooks given that I used the all workbooks option. My guess is, that automatically puts the sub in personal workbook.