CODEWITHSUNDEEP
vbaexcel
LimaNightHawk
LimaNightHawk

Reputation: 7093

Prevent user from selecting more than one cell at a time

Is there a way to prevent the user from selecting more than one cell at a time (preventing multi-selecting several cells)?

Note: this is not a duplicate of this question. There the user is getting a selection from an Input box. I'm trying to keep them from doing it directly from the sheet via Mouse (or Keyboard + Shift key).

Upvotes: 1

Views: 4042

Answers (2)

DisCypher
DisCypher

Reputation: 1

Here is what I use to ensure a single cell is selected, if the target is a range, then the top right cell is selected. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strTemp As String
    ' check if the selected range is more than one cell
    If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
        ' do something here
    Else
        strTemp = Left(Target.Address, InStr(1, Target.Address, ":") - 1)
        If Not IsNumeric(Right(strTemp, 1)) Then strTemp = strTemp & "1"
        If IsNumeric(Mid(strTemp, 2, 1)) Then strTemp = "A" & strTemp
        Me.Range(strTemp).Select
        ' do something here
    End If
End Sub

Upvotes: 0

lllpratll
lllpratll

Reputation: 378

I literally googled "excel vba prevent multiple cell selection" and the first hit was the exact answer. http://www.ozgrid.com/forum/showthread.php?t=46473 Put this code in the sheet you want to place the restriction on rather than a module

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    '>> Prevent user from multiple selection before any changes:

    If Selection.Cells.Count > 1 Then 
        MsgBox "Sorry, multiple selections are not allowed.", vbCritical 
        ActiveCell.Select 
        Exit Sub 
    End If 

End Sub

Upvotes: 2

Related Questions