Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter

Search Tips

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

1. DOWNLOAD THE FREE OZGRID TOOLBAR . NO SPYWARE, ADWARE OR MALWARE!
2. FREE LEVEL 1 EXCEL TRAINING!
3. THIS MONTHS SPECIAL!
   SQL TESTER : The SQL Tester is an interactive Query Tool (Excel add-in) for retrieving data and displaying it on the screen in an ad hoc basis from almost all kinds of databases. The output can be placed in worksheets and added to workbooks VBA-projects. Currently only $33.00!

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