What determines location of VBA code from recorded macro?

I recorded a macro (as a basis for editing, maybe with help here). But how are you supposed to know where to find the VBA code please?

I assumed it would be in PERSONAL.xlsb as a new module, But as you see it's replaced whatever was in Module 8, rather than creating Module 16.

Excel-VBA-Module-Location.jpg


What logic or inadvertent setting on my part gets it into Module 8?

Also, could I ask a couple of associated very basic queries please:

1. Is there any way to change 'Module 1' etc to meaningful names?
2. Or search for a macro name?
3. Is there a KB shortcut to stop a macro recording, rather than the four clicks, in my case: Classical Menu > Tools > Macro > Stop Recording

4. With many modules open, as I had here, can I close them all at once instead of individually?

--
Terry, East Grinstead, UK

It does not replace an existing module so module 8 must have been removed at some point

1. Yes you can change module name. Make sure the properties window is visible. Click next to name in properties window to edit name. Be aware a module can contain more than 1 macro
2. In vba just click tools:macros and the macro dialogue window opens
3. There should be a"stop recording" button. What version of excel do yiu use?

Originally Posted by terrypin

2. Or search for a macro name?

Save the workbook attached in your Xlstart folder (the same one your personal workbook is in.) as a xlsb file.
In your project list you will now have a project called "Select Personal Macros (User Form).xlsb".
Under tools - References make sure that "mMcrosoft visual basic for applications extensibility 5.3" is checked.
Under Modules in Select Personal Macros (User Form).xlsb run the ShowMacroUserForm macro.

You will then get a userform to see all the macro's in your personal workbook and you can run the macro's from there.

Btw, I didn't create the file so apologies to the person who did because I can't remember who it was now to give them credit

Please provide links when cross-posting as per the forum rules...

https://www.mrexcel.com/forum/excel-...ml#post4765353

Thanks Phil, appreciate the fast reply.

That would explain the module 8 choice.

#1. Thanks, I've duly renamed one module and will do the others later. BTW, I'm surprised that the familiar Right-click doesn't include Rename in its context menu?

#2. OK, that gives a handy list by workbook, similar to the one from Alt+F8 in the main Excel app. (It's easier to read as it doesn't prefix the entry with the workbook name). So I assume there's no Search tool?

#3. I'm using Excel 365 and found the large Ribbon rather daunting following my move from Excel 2000 after many years. But I see that for VBA/macros it would be better to use the Developer tab, where I do see a Macro Record button, thanks.

P.S. When typing replies in this forum, is it possible to get word-wrap? At present, all I can see in the line above is '...better to'

--
Terry, East Grinstead, UK

Originally Posted by terrypin

#3. I'm using Excel 365 and found the large Ribbon rather daunting following my move from Excel 2000 after many years. But I see that for VBA/macros it would be better to use the Developer tab, where I do see a Macro Record button, thanks.

In your status bar next to where it says "Ready" do you not have a Record button (I don't use 365 so can't see if it is diferent)?

Originally Posted by WasWodge

Save the workbook attached in your Xlstart folder (the same one your personal workbook is in.) as a xlsb file.
In your project list you will now have a project called "Select Personal Macros (User Form).xlsb".
Under tools - References make sure that "mMcrosoft visual basic for applications extensibility 5.3" is checked.
Under Modules in Select Personal Macros (User Form).xlsb run the ShowMacroUserForm macro.

You will then get a userform to see all the macro's in your personal workbook and you can run the macro's from there.

Btw, I didn't create the file so apologies to the person who did because I can't remember who it was now to give them credit

Thanks. Looks a bit heavy but I'll set aside some time to try it. Although I'm fairly happy running them from Alt+F8. It's finding them in what are currently numbered 'Modules' that's tedious!
Any way to close all modules at once?

--
Terry, East Grinstead, UK

Originally Posted by terrypin

Any way to close all modules at once?

Only way I know of is with MZ-Tools and I as you use 365 it probably isn't an option.

Originally Posted by philaugust2004

3 Your stop recording button is hidden. To get it back, start recording a macro and then click View tab:macros:stop recording. This should bring the button back

You've rather lost me on that, Phil. Do you mean under Classic Menu? I have no Start or Stop Macro Recording button there. And starting and stopping a macro doesn't create one.

Excel365-ClassicMenu-Ribbon-2.jpg

If you mean under the Developer menu. as mentioned I do have a button there. And other useful tools. So an extra click to open up Developer is no big deal, although ideally I'd like a button under Classic menu too.

Excel365-DeveloperMenu-Ribbon-1.jpg

--
Terry, East Grinstead, UK

Originally Posted by philaugust2004

I have excel 2010 so don't know about earlier versions

the OP is using 365 which is the latest version (although the OP does have an aversion to using the ribbon).

Bingo, thanks Lewis, never would have thought of looking for it there!

--
Terry, East Grinstead, UK

Originally Posted by terrypin

Bingo, thanks Lewis, never would have thought of looking for it there!

--
Terry, East Grinstead, UK

See post #6

Originally Posted by philaugust2004

Do you have a "record macro" button in Developer tab?

Yes, as I reported in post #5.

--
Terry, East Grinstead, UK

Originally Posted by WasWodge

See post #6

Duh, misread that, sorry. You get the seegar!


--
Terry, East Grinstead, UK