Excel VBA to confirm a range is selected

You need to type TypeName

One of the most powerful statements in VBA is Selection. This holds whatever the user has selected just before they ran the macro. Normally it is a range, but what if it isn’t?

If a user has a chart selected but he runs a macro that is expecting a range then an error usually occurs.

To identify what is selected when the macro runs you can use the TypeName statement as shown below.


Sub Testing()
  If TypeName(Selection) = "Range" Then
    'code to do something to a range
    MsgBox "Please select a range before running this macro"
  End If
End Sub

A message box is displayed if the user hasn’t selected a range before running the macro.

Note: a single cell is still treated as a range in Excel.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.