PDA

View Full Version : Selection



Opv
07-20-2010, 05:05 PM
I have a subroutine that utilizes the following simple:


selection.value = Date


This statement is not based on a pre-defined range but relies on the cells selected by the user. It is triggered by clicking on a command button. In other words, the user highlights the cell or cells in which the current date is to be inserted, then clicks the command button and the date is inserted.

I've run into a problem, however, when only one cell is selected, i.e., the ActiveCell. When I click on the command button the entire spreadsheet is filled with the current date. Is there some way to restrict this to only the active cell when only the active cell is selected?

Opv
07-20-2010, 06:50 PM
Here is the full date script, in case it is needed to give context to the question.


Sub insertDate()

Dim sRng As Range
Dim wRng As Range
On Error Resume Next
Set sRng = Range("SaleDate").Offset(1, 0).Resize(gRows - hRow).SpecialCells(xlCellTypeVisible)
Set wRng = Range("WormDate").Offset(1, 0).Resize(gRows - hRow).SpecialCells(xlCellTypeVisible)

With Sheets("(History)")
''''' Do ONLY if ActiveCell is in the WormDate or SaleDate Range '''''
If Not Intersect(ActiveCell, wRng) Is Nothing Or _
Not Intersect(ActiveCell, sRng) Is Nothing And _
.FilterMode = True Then
If ActiveCell.Value = "" Then
Selection.SpecialCells(xlCellTypeBlanks).Value = Date
Else: Selection.Value = ""
End If

''''' For all other DATE cells '''''
ElseIf Cells(hRow, ActiveCell.Column).Value <> "KidDueDate" And _
Right(Cells(hRow, ActiveCell.Column), 4) = "Date" Then
If ActiveCell.Value = "" Then
ActiveCell.Value = Date
Else: ActiveCell.Value = ""
End If

End If

End With

End Sub

mdmackillop
07-20-2010, 11:49 PM
If Selection.Count > 1 Then
Selection.Value = Date
Else
ActiveCell.Value = Date
End If

Bob Phillips
07-21-2010, 02:23 AM
I've run into a problem, however, when only one cell is selected, i.e., the ActiveCell. When I click on the command button the entire spreadsheet is filled with the current date. Is there some way to restrict this to only the active cell when only the active cell is selected?

That shouldn't happen, and doesn't in a test I just tried.

Opv
07-21-2010, 07:31 AM
Thanks. The only thing I can figure that was going on is that some of the predefined ranges rely on the UsedRange. My UsedRange area kept getting distorted. I set up a function to keep the correct number of data rows and columns in tact. Whether that was the source of the problem, I don't know but either that or some other inadvertent change resolved the problem.