What does it mean to click on a cell?

[Mister_joe]

Hi All,
I have a worksheet with hundreds of rows of data. I am looking to make the clearing of the data easy for users of the worksheet. The worksheet has several regions.
The user is expected to click on the first cell of the range he or she wants to clear from the worksheet and then click a command button that runs the code to clear the range. Clicking on the first cell of a range is intended to mark the range for removal. An array named myRange already has several range objects in it. When the user clicks the command button, the program should loop through the range objects in the array, looking for a match between the address of the cell the user clicked and the address of the first cell of the current element in the array. If a match is found, the index of the range object is stored in an integer variable. With this index, the program would have effectively identified the range object that the user wants to clear from the worksheet and remove from the array of range objects. Easier said than done!

Private Sub btnRemoveRange_Click()    
    Dim i As Integer, marker As Integer    
    marker = 0    
    For i = 1 To UBound(myRange) Step 1        
        If Selection.AddressLocal(rowabsolute:=False, columnabsolute:=False, ReferenceStyle:=xlA1) = myRange(i).Cells(1, 1).AddressLocal(rowabsolute:=False, columnabsolute:=False, ReferenceStyle:=xlA1) Then
            marker = i            
            Exit For        
        End If    
    Next 'i    
    If marker = 0 Then        
        MsgBox "You have not selected the first cell in the range in order to mark it for removal.", vbExclamation + vbOKOnly        
        Exit Sub    
    Else
        myRange(marker).Select        
    With Selection           
         .Clear            
         .Borders.ColorIndex = xlColorIndexNone           
         .Borders.LineStyle = xlLineStyleNone        
    End With       
    Set myRange(marker) = Nothing    
    End If
End Sub

The code above is supposed to implement the above description. The challenge I am having is knowing what it means in code when a user clicks on a cell.
1. Does the cell not become the Activecell? I am asking this question because the command Application.ActiveCell.AddressLocal did not return the address of the cell that was clicked.
2. Does the act of clicking on a cell not Activate the cell? I am asking this question because the command Selection.AddressLocal did not return the address of the cell that was clicked.
3. Does clicking on a cell not return the cell's name? I am asking this question because the command Selection.Name fired up an error message.
4. The command ActiveWorkbook.names(ActiveWorkbook.names.count).name did not return the name of the cell that was clicked.


After all these, I simply don't know how else to proceed. Please, assist me. How does Excel interpret a click on a cell?

[Aflatoon]

1. What did it return?
2. Ditto.
3. You have to use Selection.Name.Name because Selection.Name actually returns a Name object, not a String.
4. Why would it?

[Mister_joe]

Another code snippet had already executed the following commands:

Activeworkbook.Worksheets("Sheet1").Activate
Actvivesheet.cells(26,1).Select

For that reason:
1. What did it return? It returned $A$26. This is certainly not the address of the cell that was clicked.
2. Ditto. It returned $A$26.
3. You have to use Selection.Name.Name because Selection.Name actually returns a Name object, not a String. This triggered Application-define or object-defined error
4. Why would it? I thought by clicking on a cell, a range object would be defined and that every range object has a name. Since the cell was the last range object, the last entry in the names collection would represent the cell. Obviously, this supposition is not true.

Please, tell me, how do I get a range object just by clicking on a cell?

[SamT]

Step thru this and watch the value of X, when you understand things, delete the "X" lines

BTW, I did this while you were posting your last.

Option Explicit

Private Sub btnRemoveRange_Click()
    Dim RngName As Name
    Dim SelectedCell As Range
    Dim Success As Boolean
    
    With ActiveSheet
      Set SelectedCell = Selection
      If SelectedCell.Count > 1 Then
        MsgBox "Please select only one cell and try again."
        Exit Sub
      End If
      
      For Each RngName In ActiveWorkbook.Names
      
          Dim X
          X = RngName.Name
          X = RngName
          
        If Not Intersect(SelectedCell, RngName.RefersToRange) Is Nothing Then
          With Range(RngName)
              .Clear
              .Borders.ColorIndex = xlColorIndexNone
              .Borders.LineStyle = xlLineStyleNone
          End With
          ActiveWorkbook.Names(RngName.Name).Delete
          Success = True
          Exit Sub
        End If
      Next RngName
    End With
    
    If Not Success Then
        MsgBox "You have not selected a cell in the range in order to mark it for removal.", vbExclamation + vbOKOnly
    End If
End Sub

Another code snippet had already executed the following commands:
Activeworkbook.Worksheets('Sheet1").Activate
Actvivesheet.cells(26,1).Select

For that reason:
1. What did it return? It returned $A$26. This is certainly not the address of the cell that was clicked.
2. Ditto. It returned $A$26.

#2? Because that is what the code snippet selected. My code will probably also use "A26" as long as that code snippet is doing the selection.

But I tested my code without the snippet and it works.

[Aflatoon]

Activecell returns the activecell and Selection returns the selection - which may be one cell, several cells, or something else entirely - at the time the call to activecell is made. If you have other code that selects a different cell before you test the activecell, then it may well not refer to the cell you clicked on.

[Mister_joe]

Thanks. I have commented out the Activesheet.cells(26,1).Select and the code returned the address of the cell that I clicked. I was already running myself into a frenzy. Gee! Much respect!

[Paul_Hossler]

additional thoughts

1. error checking to make sure a cell is selected (and not a shape or something else)
2. handles more than one cell selected
3. allows any cell in the ranges in the array to be selected (bigger target)


not tested with real data

Option Explicit
'assume the Ranges are set, and you're not storing the addresses
Dim myRange(1 To 3) As Range

Private Sub btnRemoveRange_Click()
    Dim rSelectedCell As Range, rMarker As Range
    Dim i As Integer, marker As Integer
    
    'in case a shape of something other than a Range is selected
    If Not TypeOf Selection Is Range Then Exit Sub
    
    Set rMarker = Nothing
    
    'if more than one cell is selected, use the top, left
    Set rSelectedCell = Selection.Cells(1, 1)
    
    'loop thru the array of Range objects
    For i = LBound(myRange) To UBound(myRange)
        
        'if Nothing, then try the next one
        If myRange(i) Is Nothing Then GoTo GetNext
        
        'if the top left is not in the intersection
        If Intersect(rSelectedCell, myRange(i)) Is Nothing Then GoTo GetNext
                
        'remember this one, 'clear it' and get out
        Set rMarker = myRange(i)
        Set myRange(i) = Nothing
        Exit For
GetNext:
    Next 'I
    
    'if we did not find an intersection, make a message
    If rMarker Is Nothing Then
        MsgBox "You have not selected the a cell in a range in order to mark it for removal.", vbExclamation + vbOKOnly
    
    'if we did, then clear the range
    Else
        With rMarker
            .Clear
            .Borders.ColorIndex = xlColorIndexNone
            .Borders.LineStyle = xlLineStyleNone
        End With
    End If
End Sub

Paul