Monday, April 16, 2007


To use a counter in your macro, just assign any cell to retain the value. In this example the cell A1 is chosen. Each time the macro is run, it adds the value 1 to the cell A1.

Sub Count()
mycount = Range("a1") + 1
Range("a1") = mycount
End Sub

Carriage Return

Sometimes you may want to put a line of text on the next row and not let it continue on the first row. See this example in a message box.

Sub TwoLines()
MsgBox "Line 1" & vbCrLf & "Line 2"
End Sub

Copying A Range

Copy data from a specific range can be done with this macro. Here data is copied from the current sheet to the active cell. (Refer to Active Cell)
Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub
To copy from a range in another sheet (eg Sheet3) to the active cell you need to change the code to;
Sheets("sheet3").Range("A1:A3").Copy Destination:=ActiveCell

Friday, April 13, 2007

Counting Rows & Columns & Sheets

When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro will do the trick.

Sub Count()
mycount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox mycount
End Sub

The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.

Sub Count2()
mycount = Application.Sheets.Count
MsgBox mycount
End Sub

Adding Items to a combobox

To add a combobox refer to User Form. To populate a combobox or a listbox is the same. You could add from the code or even from a range of cells in your spreadsheet. To add from the code, just add this line to your code.

ComboBox1.AddItem "Product A"
ComboBox1.AddItem "Product B"

Auto Run

Making your macros run automatically when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.

Sub Auto_Open()
Msgbox "Hello"
End Sub

This code would be located in the module. However if you use the second method, the code must be in the workbook (double click "This Workbook" in the explorer window). Click on the drop down list (that says General) and select Workbook. Click on the drop down list (that says declarations) and select Open.

Private Sub Workbook_Open()
Msgbox "Hello"
End Sub