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?
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?