Your Personal.xlsb file is stored in a folder called XLSTART. Moving macros from one computer to another You can rename Modules in the Properties window beneath the Project Explorer, so they better reflect what the macros inside them do. So if you already have Module1 and Module2, VBA will create Module3. Note: When you record a macro in a new instance of Excel, VBA will automatically create a new Module folder and increment its number. You can delete it, or keep it to add code to later. If you don't see it, go to View > Project Explorer.ĭouble-click on the VBA Project (PERSONAL.xlsb) folder > Modules > Module1, and you'll find the empty Macro1 that you recorded. You can find your Personal Macro workbook in the Project Explorer pane on the left hand side. Go to Developer > Visual Basic to launch the Visual Basic Editor (VBE), which is where your macros are stored. When you close the workbook, you'll be prompted to save both the workbook, and the Personal Macro workbook. This is the most important step, because if you don't already have a Personal Macro Workbook, Excel will create one for you.Ĭlick Developer > Stop Recording, and Excel will have created your Personal Macro worbook. In the Store macro in box, pick Personal Macro Workbook > OK. When you do start creating macros of your own, you'll want to give them descriptive names, so you know what they do. You can accept the name that Excel gives you, such as Macro1, since this is just a temporary macro. In the Record Macro dialog box, don't bother entering a name for the macro in the Macro name box. Go to the Developer tab, and click Record Macro. You can learn more about creating macros in Quick start: Create a macro. We’ll record a macro that does nothing, but will create the Personal Macro workbook. For more information, see Show the Developer tab. Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away.Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it. ) | 7 Merge & Split Tools ( Advanced Combine Rows, Split Cells. ) | 12 Conversion Tools ( Numbers to Words, Currency Conversion. ) | 19 Insertion Tools ( Insert QR Code, Insert Picture from Path. ) | 40+ Practical Formulas ( Calculate age based on birthday. Top 15 Toolsets: 12 Text Tools ( Add Text, Remove Characters. Super Lookup: Multiple Criteria VLookup | Multiple Value VLookup | VLookup Across Multiple Sheets | Fuzzy Lookup .Īdvanced Drop-down List: Quickly Create Drop Down List | Dependent Drop Down List | Multi-select Drop Down List .Ĭolumn Manager: Add a Specific Number of Columns | Move Columns | Toggle Visibility Status of Hidden Columns | Compare Ranges & Columns .įeatured Features: Grid Focus | Design View | Big Formula Bar | Workbook & Sheet Manager | Resource Library (Auto Text) | Date Picker | Combine Worksheets | Encrypt/Decrypt Cells | Send Emails by List | Super Filter | Special Filter (filter bold/italic/strikethrough.) . Popular Features: Find/Highlight/Identify Duplicates | Delete Blank Rows | Combine Columns or Cells without Losing Data | Round without Formula . Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely Then save and close this code window, from now on, when users save as this workbook, it will be saved as Excel Macro-Enabled Workbook format by default as following screenshot shown: XFileName = Application.GetSaveAsFilename(, 'Excel Macro-Enabled Workbook (*.xlsm), *.xlsm',, 'Save As xlsm file')ĪctiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabledģ. VBA code: Force to save workbook as macro enabled workbook by default: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Then double click ThisWorkbook under the VBAProject section to open a new blank module, and then copy and paste the following code into the module: Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.Ģ. To save the workbook as macro enabled workbook by default, the following VBA code may help you, please do as this:ġ.

How could you force users to save as a macro enabled workbook?įorce users to save as a macro enabled workbook with VBA code To keep the codes, you should save the workbook as Excel Macro-Enable Workbook format.

When you save an Excel workbook, it will be saved as xlsx file format by default, and this file format will get rid of the macro codes from the workbook if there are multiple codes. How to force users to save as a macro enabled workbook?

Excel macro enabled workbook