Report

VBA single click to get current date [Solved]

Ask a question sroche317 5Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - Latest answer on Feb 19, 2016 08:59AM
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 
Helpful
+0
moins plus
Post the code here. Let us look at it!
sroche317 5Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - Feb 16, 2016 06:16PM
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
Reply
Add comment
Helpful
+0
moins plus
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 5897Posts Monday June 3, 2013Registration date ModeratorStatus September 29, 2016 Last seen - Feb 16, 2016 06:23PM
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.....
Reply
Add comment
Helpful
+0
moins plus
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!
sroche317 5Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - Feb 16, 2016 07:56PM
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
Reply
Add comment
Helpful
+0
moins plus
Yea get rid of the extra end sub........try that!
sroche317 5Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - Feb 16, 2016 08:32PM
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!
Reply
Add comment
Helpful
+0
moins plus
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 5Posts Tuesday February 16, 2016Registration date February 19, 2016 Last seen - Feb 19, 2016 08:59AM
It worked! Thank you so much! I really appreciate it!!!
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!