CODEWITHSUNDEEP
excelexcel-2010vba
angular2neewbie
angular2neewbie

Reputation: 129

Check if more than one cell selected

I am wondering how I can check if more than one cell is celected in a range?

I have 3 cells i want to check the Ranges are "A:B" and also "D", and im trying this code but its not working for me. 

If 3 - CountA(range) < 1 Then

How can I do it in anther way?

Upvotes: 11

Views: 22830

Answers (5)

Sabatino Ognibene
Sabatino Ognibene

Reputation: 171

I used a modified combination of @FreeSoftwareServers and @KazimierzJawor's answers to complete a military alphabet macro. If you like what I share, please give them a thumbs up. I'm only sharing in case it helps someone see how to use this in a subroutine (I did have to modify it) and provide a bonus military alphabet sub for readers.

Sub Military_Alphabet_MSG()

'''''                   Check if more than one cell is selected
    Dim SelectedRng As Range, Cell As Range, S$, s2$
    Set SelectedRng = Range(Selection.Address) ' Or Range("A1:A2"), whatever
    If SelectedRng.Cells.CountLarge > 1 Then
        MsgBox "More than one cell is selected.  Select one cell and try again.", vbCritical, "ERROR!"
        Exit Sub
    Else
        Set Cell = ActiveCell
        S = UCase(Cell.Value)
    End If

'''''                   Create dictionary with military alphabet (Microsoft Scripting Runtime reference required)
    Dim milDict As Scripting.Dictionary
    Set milDict = New Scripting.Dictionary
        milDict("A") = "Alpha"
        milDict("B") = "Bravo"
        milDict("C") = "Charlie"
        milDict("D") = "Delta"
        milDict("E") = "Echo"
        milDict("F") = "Foxtrot"
        milDict("G") = "Golf"
        milDict("H") = "Hotel"
        milDict("I") = "India"
        milDict("J") = "Juliet"
        milDict("K") = "Kilo"
        milDict("L") = "Lima"
        milDict("M") = "Mike"
        milDict("N") = "November"
        milDict("O") = "Oscar"
        milDict("P") = "Papa"
        milDict("Q") = "Quebec"
        milDict("R") = "Romeo"
        milDict("S") = "Sierra"
        milDict("T") = "Tango"
        milDict("U") = "Uniform"
        milDict("V") = "Victor"
        milDict("W") = "Whiskey"
        milDict("X") = "X-Ray"
        milDict("Y") = "Yankee"
        milDict("Z") = "Zulu"
        milDict("1") = "One"
        milDict("2") = "Two"
        milDict("3") = "Three"
        milDict("4") = "Four"
        milDict("5") = "Five"
        milDict("6") = "Six"
        milDict("7") = "Seven"
        milDict("8") = "Eight"
        milDict("9") = "Niner"
        milDict("0") = "Zero"
        milDict("@") = "@AT@"
        milDict("-") = "-DASH-"
        milDict(".") = ".DOT."

'''''                   Unused section demonstrating how to loop through keys
'    Dim key As Variant
'    For Each key In milDict.Keys
'        Debug.Print key, milDict(key)
'    Next key

'''''                   Create First Part of ReturnString
    Dim ReturnString$
    ReturnString = "(" & Chr(34)
    If milDict.Exists(Left(S, 1)) Then
        ReturnString = ReturnString & milDict(Left(S, 1))
    Else
        ReturnString = Left(S, 1)
    End If
    
'''''                   Create the rest of ReturnString
    If Len(S) > 1 Then
        For i = 2 To Len(S)
            s2 = Mid(S, i, 1)
            If milDict.Exists(s2) Then
                ReturnString = ReturnString & Chr(34) & ", " & Chr(34) & milDict(s2)
            Else
                ReturnString = ReturnString & Chr(34) & ", " & Chr(34) & s2
            End If
        Next i
    End If
    ReturnString = ReturnString & Chr(34) & ")"
    

'''''                   Add to clipboard
    Dim objData As New MSForms.DataObject  '"Microsoft Forms 2.0 Object Library" (or later) must be enabled per user before DataObject may be utilized
    objData.SetText ReturnString
    objData.PutInClipboard
    
'''''                   Display Informational MsgBox
    MsgBox Cell.Value & Chr(10) & Chr(10) & ReturnString & Chr(10) & Chr(10) & "Conversion has been placed in your clipboard.", vbOKOnly + vbInformation, "Military Alphabet Conversion"
End Sub

Upvotes: 0

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2831

 Dim SelectedRng As Range, SelectedRngStr As String
 Set SelectedRng = Range(Target.Address) ' Or Range("A1:A2"), whatever
 If SelectedRng.Cells.Count = 1 Then
  SelectedRngStr = SelectedRng.Value
 Else
  Debug.Print "SelectedRng.Cells.Count = " & SelectedRng.Cells.Count
 End If

Upvotes: 1

elano7
elano7

Reputation: 2265

If you are using Worksheet_Change(ByVal Target As Range) or Worksheet_SelectionChange(ByVal Target As Range) then use this code:

If InStr(Target.Address, ":") > 0 Or InStr(Target.Address, ",") > 0 Or InStr(Target.Address, ";") > 0 Then

This will check if the selected range is for example:

`A1;C1` (Cells A1 and C1 are selected) or
`E1:E4` (E1 to E4 are selected)

Sometimes it is used ";" and sometimes "," so that we check both of them.

Instead of Target you can use the range that you defined in your code, for example:

If InStr(MyRange.Address, ":") > 0 Or InStr(MyRange.Address, ",") > 0 Or InStr(MyRange.Address, ";") > 0 Then

Upvotes: 5

brettdj
brettdj

Reputation: 55702

I think you want to try something along these lines

Dim rng1 As Range
Set rng1 = Range("A1:B1,D1")
MsgBox 3 - rng1.Cells.Count

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

Something like this for selected range:

If 3-Selection.Cells.Count < 1 then

or, if there is possibility that you will have selected really a lot of cells use this one:

If 3-Selection.Cells.Countlarge < 1 Then

Upvotes: 12

Related Questions