VBA single click to get current date

Solved/Closed
sroche317 Posts 5 Registration date Tuesday February 16, 2016 Status Member Last seen February 19, 2016 - Feb 16, 2016 at 04:43 PM
sroche317 Posts 5 Registration date Tuesday February 16, 2016 Status Member Last seen February 19, 2016 - Feb 19, 2016 at 08:59 AM
Hello,

I'm brand new to VBA and the code that bhargav posted on Feb 6, 2013 10:48AM worked perfectly to get current time in a range of cells with a single click. Is there a way to also get the current date to display with a single click in a different range of cells? I have tried combining pieces of different codes, but keep getting an error message.

Thanks!



Related:

5 responses

Blocked Profile
Feb 16, 2016 at 05:26 PM
Post the code here. Let us look at it!
0
sroche317 Posts 5 Registration date Tuesday February 16, 2016 Status Member Last seen February 19, 2016
Feb 16, 2016 at 06:16 PM
Thanks for your response! Here is the code that allows me to display current time in cells C2:C100:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange As Range
Dim IntersectRange As Range

Set MyRange = Range("C2:C200")

Set IntersectRange = Intersect(Target, MyRange)

On Error GoTo SkipIt

If IntersectRange Is Nothing Then
Exit Sub

Else

Target = Format(Now, "ttttt")

End If

SkipIt:
Exit Sub
End Sub


What I'd like is to also have it display current date in B2:B100. From other threads (all unanswered), it looks like it will involve something like "Target = Format(Now, "mm/dd/yyyy") but I'm trying to figure out how to have more than one range specified. Any tips would be greatly appreciated - thanks so much!
-Stephanie
0
Blocked Profile
Feb 16, 2016 at 06:20 PM
change the range variable. if needed, initialize each variable as an array and iterate through the array. So where the c range variable is, change it to the range you need to check.
0
Blocked Profile
Feb 16, 2016 at 06:23 PM
please forgive me for being brief, as I am on mobile and the tools on a mobile are limited....I will try to post a whole complete direction later.....
0
Blocked Profile
Feb 16, 2016 at 06:26 PM
OK, hang in there....


Dim MyRange0, Myrange1 As Range

Dim IntersectRange0, IntersectRange1 As Range
Set MyRange0 = Range("C2:C200")
Set MyRange1 = Range("B2:B20")
Set IntersectRange0 = Intersect(Target, MyRange0)
Set IntersectRange1 = Intersect(Target, MyRange1)

This code is not elegant, and could be cleaned up, but for simplicity sake and reverse engineering to learn......it works!

I hope this gives you some direction.....I am $1500 a day (that is 8 hours!) LOL! As long as you continue to ask for help, and post, we will continue to help!
I have said it once, I will say it again. IT!
0
sroche317 Posts 5 Registration date Tuesday February 16, 2016 Status Member Last seen February 19, 2016
Feb 16, 2016 at 07:56 PM
Thanks so much! I think I'm nearly there - my guess is that I'm either missing or have an extra "skipit" in there. Doing some trial and error to find out. But thank you so, so much for your help. I super appreciate it!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange0, Myrange1 As Range
Dim IntersectRange0, IntersectRange1 As Range

Set MyRange0 = Range("C2:C200")
Set Myrange1 = Range("B2:B200")
Set IntersectRange0 = Intersect(Target, MyRange0)
Set IntersectRange1 = Intersect(Target, Myrange1)

On Error GoTo SkipIt

If IntersectRange0 Is Nothing Then
Exit Sub

Else

Target = Format(Now, "ttttt")

End If

If IntersectRange1 Is Nothing Then
Exit Sub

Else

Target = Format(Now, "mm/dd/yyyy")

End If

SkipIt:
Exit Sub
End Sub
0
Blocked Profile
Feb 16, 2016 at 08:20 PM
Yea get rid of the extra end sub........try that!
0
sroche317 Posts 5 Registration date Tuesday February 16, 2016 Status Member Last seen February 19, 2016
Feb 16, 2016 at 08:32 PM
I've only got one end sub, but on separate occasions, I tried taking out a "SkipIt:" line, "Exit Sub" line, a combined "SkipIt: Exit Sub" line, and an "End If" line, but it didn't like any of those either. My apologies for all the back-and-forth!
0

Didn't find the answer you are looking for?

Ask a question
This version should solve your problem.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange0, Myrange1 As Range
Dim IntersectRange0, IntersectRange1 As Range

Set MyRange0 = Range("C2:C200")
Set Myrange1 = Range("B2:B200")
Set IntersectRange0 = Intersect(Target, MyRange0)
Set IntersectRange1 = Intersect(Target, Myrange1)

On Error GoTo SkipIt

If Not IntersectRange0 Is Nothing Then
    Target = Format(Now, "ttttt")

ElseIf Not IntersectRange1 Is Nothing Then
    Target = Format(Now, "mm/dd/yyyy")

End If

SkipIt:

End Sub
0
sroche317 Posts 5 Registration date Tuesday February 16, 2016 Status Member Last seen February 19, 2016
Feb 19, 2016 at 08:59 AM
It worked! Thank you so much! I really appreciate it!!!
0