Preventing Multi Cell Selection

  • Thread starter Michael Excel Dude
  • Start date
M

Michael Excel Dude

I have built macros which are activated by a single cell selection in a
worksheet (using a change event macro). I get an error if the user
inadvertently selects more than one cell. My questions are:

1. How can I prevent the user from selecting more than one cell on a
particular worksheet?

2. What code will abort a macro if there is an error.

Many Thanks

Michael
 
D

Dave Peterson

#1. I like to start my worksheet_change procedures with code like:

if target.cells.count > 1 then exit sub
if intersect(target, me.range("a1:c9")) is nothing then exit sub

Only one cell in the Range of A1:C9.

#2. You could use the worksheet_selectionchange event.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Application.EnableEvents = False
Target.Cells(1).Select
Application.EnableEvents = True
End If
End Sub
 
M

Michael Excel Dude

Dave, as always, you advice is flawless. Thanks very much.

----------------------------
 
Top