Current Cell Content

Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example

a message box is displayed. Replace this with a macro should you require another course of action.

Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
MsgBox "Text" 'replace this line with your macro
Else
If ActiveCell = "" Then
MsgBox "Blank cell" 'replace this line with your macro
Else
End If
If ActiveCell.HasFormula Then
MsgBox "formula" 'replace this line with your macro
Else
End If
If IsDate(ActiveCell.Value) = True Then
MsgBox "date" 'replace this line with your macro
Else
End If
End If
End Sub

Close All Files

Sometimes you may want to close all files without saving. Doing it manually is a hassle with the question "Do you wanna save?"

Sub CloseAll()
Application.DisplayAlerts = False
myTotal = Workbooks.Count
For i = 1 To myTotal
ActiveWorkbook.Close
Next i
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

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"