Back to Excel Newsletter Archives
EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS
ASSIGNING MACROS TO A SHORTCUT KEY, OBJECT, COMMAND BUTTON OR A TOOLBAR
Once you have recorded your Excel macro , there are a number of ways that you can run it. To go via the Macro dialog box in the menu system go to Tools>Macro>Macros (Alt+F8), click on the name of the macro you wish to run and select Run. There are also a number of much quicker alternatives:
ASSIGNING A MACRO TO AN OBJECT
To assign a macro to an object, select an object from the drawing toolbar, then right click and go to Assign Macro, select the macro you wish to assign, then click OK. (You can double click as well)
ASSIGN A MACRO TO A SHORT-CUT KEY
Assigning a macro to a shortcut key can be done at the first stage of recording a macro, when your Record Macro dialog first pops up to ask you to give it a name. The second step asks for a Shortcut key. Note that if you select a shortcut that is already in use (such as Ctrl C for Copy), the original command will be overridden and replaced with your macro.
If you have already created your macro and wish to then go back and assign it to a shortcut key, go to Tools>Macro>Macros (Alt+F8), then select the Options button and enter in the shortcut key you would like to use.
ASSIGNING A MACRO TO A BUTTON FROM THE FORMS TOOLBAR
You can easily assign a macro to a button from the Forms Toolbar. To do this, go to View>Toolbars>Forms to show your Forms Toolbar, then select the Button icon and draw a button on your worksheet. Immediately when you let go of your button, the Assign Macro dialog will pop up. Select the macro you wish to assign, then click OK. Note that if you select the border of the button, then right click, extra options, such as the ability to name your button, and again the option to Assign a Macro to the button if you have not already done so.
ASSIGNING A MACRO TO A COMMAND BUTTON FROM THE CONTROL TOOLBOX
You can also assign a macro to a Command Button from the Control Toolbox, giving you much greater flexibility than using a button from the Forms Toolbar. This is because the Control Toolbox uses ActiveX controls. Go here to read the difference between ActiveX controls and other controls To do this, go to View>Toolbars>Control Toolbox to show the Control Toolbox Toolbar, then select the Command Button icon and draw a button on your worksheet. Note than when you do this the button becomes an embedded Object of the Worksheet Object, giving you greater flexibility and access to many visual properties of the button, by right clicking on the Command Button and choosing Properties. To assign a macro, double click on the button and type in the words: Run "name_of_your_macro_here" make sure thename of your macro is in inverted commas.
ASSIGNING A MACRO TO A TOOLBAR
The final way you can run a macro is to assign it to a toolbar. To do this, go to View>Toolbars>Customize and select the Commands tab. Scroll through the Categories list until you get to the Macros option and select it. in the right pane, select Custom Button (smiley face) and drag to the desired location your toolbar. Click the Modify Selection button and you will see the Assign Macro option at the bottom of the list. Click on it and the Assign Macro dialog will pop up. Select the macro you wish to assign and click OK. There are many other options here such as giving a Name to your button, or changing the image. We want click Close when you have made any other changes.
Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise), name it "MySheet" and have it become the active sheet, you would use some code like shown below;
Sub AddWorksheet() Worksheets.Add().Name = "MySheet"End Sub
If we wanted to add a Worksheet as the last Worksheet and name it "MySheet" we would use;
Sub AddAsLastWorksheet() Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"End Sub
The Add Method as it applies to the Worksheet Object also has a Before Variant as well as an After Variant. However, we can only nominate a Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet after the current active Sheet.
To add, say, 4 Worksheets we could use the Count Variant;
Sub AddXWorksheets() Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4End Sub
The only other Variant we can use if desired is the Type Variant. The Type specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet.
Add Excel Worksheets in Numeric OrderThere are occasions where adding a new Worksheet to an Excel Workbook should be added in a number sequence. For example, you may need to add Worksheets, name them in a numeric order and have the Worksheets in the correct numeric position. The code below will do this
Sub AddNumberSequence()Dim wSheet As WorksheetDim lNum As LongDim lIndex As Long For Each wSheet In Worksheets If IsNumeric(wSheet.Name) Then lNum = wSheet.Name + 1 lIndex = wSheet.Index End If Next wSheet If lNum <> 0 Then Worksheets.Add After:=Worksheets(lIndex) ActiveSheet.Name = lNum End If End Sub
Add Excel Worksheets in Monthly Order
There are occasions where adding a new Worksheet to an Excel Workbook should be added in a month sequence. For example, you may need to add Worksheets, name them one month on from the last month added and have the Worksheets in the correct month position. The code below will do this
Sub AddMonthSequence()Dim wSheet As WorksheetDim strName As StringDim lMonth As LongDim lIndex As Long For Each wSheet In Worksheets Select Case wSheet.Name Case "Jan", "January" lMonth = 2 lIndex = wSheet.Index Case "Feb", "Febuary" If lMonth < 3 Then lMonth = 3 lIndex = wSheet.Index Case "Mar", "March" If lMonth < 4 Then lMonth = 4 lIndex = wSheet.Index Case "Apr", "April" If lMonth < 5 Then lMonth = 5 lIndex = wSheet.Index Case "May" If lMonth < 6 Then lMonth = 6 lIndex = wSheet.Index Case "Jun", "June" If lMonth < 7 Then lMonth = 7 lIndex = wSheet.Index Case "Jul", "July" If lMonth < 8 Then lMonth = 8 lIndex = wSheet.Index Case "Aug", "August" If lMonth < 9 Then lMonth = 9 lIndex = wSheet.Index Case "Sep", "Septemeber" If lMonth < 10 Then lMonth = 10 lIndex = wSheet.Index Case "Oct", "October" If lMonth < 11 Then lMonth = 11 lIndex = wSheet.Index Case "Nov", "November" If lMonth < 12 Then lMonth = 12 lIndex = wSheet.Index Case Else If lMonth = 0 Then lMonth = 1 lIndex = wSheet.Index End If End Select Next wSheet If lMonth <> 0 And lMonth < 12 Then On Error Resume Next Worksheets.Add After:=Worksheets(lIndex) ActiveSheet.Name = Format(DateSerial(Year(Date), lMonth, 1), "mmm") On Error GoTo 0 End IfEnd Sub
Software Categories Search Software
Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software || Outlook Add-ins
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.