VBA single click to get current date [Solved/Closed]

sroche317 5 Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - Feb 16, 2016 at 04:43 PM - Latest reply: sroche317 5 Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen
- 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!



See more 

10 replies

ac3mark 10525 Posts Monday June 3, 2013Registration dateModeratorStatus September 21, 2018 Last seen - Feb 16, 2016 at 05:26 PM
0
Thank you
Post the code here. Let us look at it!
sroche317 5 Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - 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
ac3mark 10525 Posts Monday June 3, 2013Registration dateModeratorStatus September 21, 2018 Last seen - Feb 16, 2016 at 06:20 PM
0
Thank you
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.
ac3mark 10525 Posts Monday June 3, 2013Registration dateModeratorStatus September 21, 2018 Last seen - 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.....
ac3mark 10525 Posts Monday June 3, 2013Registration dateModeratorStatus September 21, 2018 Last seen - Feb 16, 2016 at 06:26 PM
0
Thank you
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!
sroche317 5 Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - 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
ac3mark 10525 Posts Monday June 3, 2013Registration dateModeratorStatus September 21, 2018 Last seen - Feb 16, 2016 at 08:20 PM
0
Thank you
Yea get rid of the extra end sub........try that!
sroche317 5 Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - 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
Thank you
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
sroche317 5 Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - Feb 19, 2016 at 08:59 AM
It worked! Thank you so much! I really appreciate it!!!