| 2 Helper, on Feb 9, 2009 2:08:46 am GMTI forgot to mention. The code works for any cell that is clicked on the worksheet so if you have other info.
that has to be modified this click event will erase what might be in the current cell. Reply to Helper | 3 Helper, on Feb 9, 2009 2:53:19 am GMTJust to clarify. If there are only certain cells that need to be clicked, then something like this will work better.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case Range("A1").Address
Target = Format(Now, "ttttt")
Case Range("A5").Address
Target = Format(Now, "ttttt")
Case Range("A10").Address
Target = Format(Now, "ttttt")
End Select
End Sub Reply to Helper | 4 Seek, on Mar 13, 2009 5:51:52 pm GMTI just used your suggestion for getting times in certain cells and it worked great. Now lets say intead of just cell F4, I want to include F4-F100? How would I change the code. And is there a way to lock them once they are entered? Thank you very much! Reply to Seek | 5 WutUp WutUp, on Mar 13, 2009 10:27:26 pm GMTAs you can see, I changed my Nickname. Anyway, let's get to business.
The code below will give you the range F4:F100 as you requested and the sheet protection.
What you need to know also is that everytime you must unprotect the sheet when selecting the range F4:F100. Do this by clicking on the
Review tab and then click on the Unprotect Sheet icon. This will give you access to the range F4:F100
that you want to change. Now, if you immediately click anywhere in the range F4:F100 again, you will see that nothing happens. The reason is that once the code runs it will protect the cells so you don't accidentally click on
another cell. Just unprotect the sheet again in the Review tab.
Hope this helps!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("F4:F100")
Set IntersectRange = Intersect(Target, MyRange)
On Error GoTo SkipIt
If IntersectRange Is Nothing Then
Exit Sub
Else
Target = Format(Now, "ttttt")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xllockedCells
End If
SkipIt:
Exit Sub
ActiveSheet.Unprotect
Rows("1:3").Select
Range("1:3,A4:E65536").Select
Range("A4").Activate
Range("1:3,A4:E65536,G4:IV65536").Select
Range("G4").Activate
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
End Sub Reply to WutUp WutUp | 6 BradCustom, on Apr 6, 2009 9:35:38 pm BSTI've tried your formula and it works great with one exception; when you try to sort all the fields change to the current date and time. Can the code be changed to a double click on the cell instead of a single click.
Thanks Reply to BradCustom | 7 WutUp WutUp, on Apr 7, 2009 12:24:33 am BSTYou will need to put the code in the Worksheet Before Double click AppEvent instead of the Selection
Change event. You will probably notice it takes slightly longer for the code to run when you double click.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("F4:F100")
Set IntersectRange = Intersect(Target, MyRange)
On Error GoTo SkipIt
If IntersectRange Is Nothing Then
Exit Sub
Else
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Target = Format(Now, "ttttt")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xllockedCells
End If
ActiveSheet.Unprotect
Rows("1:3").Select
Range("1:3,A4:E65536").Select
Range("1:3,A4:E65536,G4:IV65536").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
ActiveCell.Offset(, 1).Select
SkipIt:
Exit Sub
End Sub Reply to WutUp WutUp | 9 mia, on Jul 2, 2009 7:38:02 am BSTCan I use this code in excel 2003? Reply to mia |
| 10 Emmettken, on Aug 24, 2009 3:24:23 pm BSTI tried the first part of your formula and it worked great. When I tried to modify it so I had to double click a cell and the cell had to be in a pre defined area, it went bad.
Would you please show mw a formula that double clicks the current time in a pre-defined area of cells.
I really appreciate this. At one time I was fairly good with Excel (Lotus prior) and then 15 years of not using it have erased my mind.
Thanks Ken Reply to Emmettken |
|
|
|
|
|
|