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

Report
Posts
5
Registration date
Tuesday February 16, 2016
Status
Member
Last seen
February 19, 2016
-
Posts
5
Registration date
Tuesday February 16, 2016
Status
Member
Last seen
February 19, 2016
-
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!



5 replies


Post the code here. Let us look at it!
Posts
5
Registration date
Tuesday February 16, 2016
Status
Member
Last seen
February 19, 2016

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

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.
Blocked Profile
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.....

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!
Posts
5
Registration date
Tuesday February 16, 2016
Status
Member
Last seen
February 19, 2016

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

Yea get rid of the extra end sub........try that!
Posts
5
Registration date
Tuesday February 16, 2016
Status
Member
Last seen
February 19, 2016

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!
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
Posts
5
Registration date
Tuesday February 16, 2016
Status
Member
Last seen
February 19, 2016

It worked! Thank you so much! I really appreciate it!!!