PDA

View Full Version : [SOLVED] What does it mean to click on a cell?



Mister_joe
04-10-2014, 04:40 AM
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
04-10-2014, 05:26 AM
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
04-10-2014, 06:30 AM
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
04-10-2014, 06:41 AM
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
04-10-2014, 06:58 AM
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
04-10-2014, 07:07 AM
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
04-10-2014, 03:37 PM
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