Excel Vba to change displayed year automatically.

Joined
Dec 13, 2017
Messages
71
Reaction score
19
I have Cell G19 with a generic date depending on the month of the worksheet. I.e. 10/1/2000, 11/1/2000,........... I currently have a user that enters the month & day, but not the year. I am trying to change the year automatically when a month & day is entered. Attached are examples of the generic date & changed date. G22, which is below Days until next meeting, requires the entry of a month, day & current year. Is what I ask, possible. I have seen date comparison, but nothing about changing just the year. The dates entered are done in advance, so an entry for January would be entered in December. The January worksheet would show the year for that January.

examples.gif
 
Last edited:
Joined
Feb 21, 2018
Messages
216
Reaction score
86
Good. Very Interesting Question.

I am working on it...will get back to you shortly....
 
Joined
Feb 21, 2018
Messages
216
Reaction score
86
I am using Cell AW1 as a switch ...feel free to change it in line 2 of the vba code below...

Private Sub Worksheet_Change(ByVal Target As Range)
switchAddress = "AW1"
If Target.Address = "$G$19" And Range(switchAddress) <> 1 Then
If ActiveSheet.Range("G22") <> "" Then
Range(switchAddress) = 1
Target = ActiveSheet.Range("G20") + ActiveSheet.Range("G22")
Else
Range(switchAddress) = 1
Target = Month(Target) & "/" & Day(Target) & "/" & Year(ActiveSheet.Range("G20"))

End If
Range(switchAddress).ClearContents
End If



End Sub
 
Last edited:
Joined
Dec 13, 2017
Messages
71
Reaction score
19
I am using Cell AW1 as a switch ...feel free to change it in line 2 of the vba code below...

Private Sub Worksheet_Change(ByVal Target As Range)
switchAddress = "AW1"
If Target.Address = "$G$19" And Range(switchAddress) <> 1 Then
If ActiveSheet.Range("G22") <> "" Then
Range(switchAddress) = 1
Target = ActiveSheet.Range("G20") + ActiveSheet.Range("G22")
Else
Range(switchAddress) = 1
Target = Month(Target) & "/" & Day(Target) & "/" & Year(ActiveSheet.Range("G20"))

End If
Range(switchAddress).ClearContents
End If



End Sub
Sorry it took me so long to get back to you, I have been out the past few days. I think I may have given you, wrong cell ranges. F19 is the date entry cell, F20 is the current date, & F22 displays number of days until next meeting. The only cell that you can modify is F19.
 
Joined
Feb 21, 2018
Messages
216
Reaction score
86
Sorry it took me so long to get back to you, I have been out the past few days. I think I may have given you, wrong cell ranges. F19 is the date entry cell, F20 is the current date, & F22 displays number of days until next meeting. The only cell that you can modify is F19.

Here is the revised code


Private Sub Worksheet_Change(ByVal Target As Range)
switchAddress = "AW1"
If Target.Address = "$F$19" And Range(switchAddress) <> 1 Then

Range(switchAddress) = 1
Target = Month(Target) & "/" & Day(Target) & "/" & Year(ActiveSheet.Range("F20"))
If Target <= Target.Offset(1, 0) Then
MsgBox "Can't Schedule in Past"
End If
Range("F22") = DateDiff("d", Target.Offset(1, 0), Target)

Range(switchAddress).ClearContents

End If

End Sub

I look forward to your feedback
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
Here is the revised code


Private Sub Worksheet_Change(ByVal Target As Range)
switchAddress = "AW1"
If Target.Address = "$F$19" And Range(switchAddress) <> 1 Then

Range(switchAddress) = 1
Target = Month(Target) & "/" & Day(Target) & "/" & Year(ActiveSheet.Range("F20"))
If Target <= Target.Offset(1, 0) Then
MsgBox "Can't Schedule in Past"
End If
Range("F22") = DateDiff("d", Target.Offset(1, 0), Target)

Range(switchAddress).ClearContents

End If

End Sub

I look forward to your feedback
Thank you for the code & fixing my mistakes (wrong ranges).

To future users of the code, place the code at the beginning of the worksheet change & do not call it as a sub. It won't run if it is not placed first, or called as a sub.
 
Last edited:
Joined
Dec 13, 2017
Messages
71
Reaction score
19
Here is the revised code


Private Sub Worksheet_Change(ByVal Target As Range)
switchAddress = "AW1"
If Target.Address = "$F$19" And Range(switchAddress) <> 1 Then

Range(switchAddress) = 1
Target = Month(Target) & "/" & Day(Target) & "/" & Year(ActiveSheet.Range("F20"))
If Target <= Target.Offset(1, 0) Then
MsgBox "Can't Schedule in Past"
End If
Range("F22") = DateDiff("d", Target.Offset(1, 0), Target)

Range(switchAddress).ClearContents

End If

End Sub

I look forward to your feedback
AmjiBhai The code worked in the original application, however when I placed the code in a similar application it gives me a error message saying Variable not defined on switchAddress = "AW1" Is there something I am missing. I copied your code directly into both applications. I do have other pieces of code under Private Sub Worksheet_Change(ByVal Target As Range). If you want to see the other code, let me know.
 
Joined
Feb 21, 2018
Messages
216
Reaction score
86
AmjiBhai The code worked in the original application, however when I placed the code in a similar application it gives me a error message saying Variable not defined on switchAddress = "AW1" Is there something I am missing. I copied your code directly into both applications. I do have other pieces of code under Private Sub Worksheet_Change(ByVal Target As Range). If you want to see the other code, let me know.

Remove this command (probably as the top line of your module, this is compelling you to use Dim for all variables declared)
Option Explicit


 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
Remove this command (probably as the top line of your module, this is compelling you to use Dim for all variables declared)
Option Explicit


Yes, I always use Option Explicit. I had it drilled into me when I first wrote code.
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
Idealistic way is to use it.
If you want to keep it.... Just use
Dim aw1 as integer
so I would replace switchAddress = "AW1" with Dim aw1 as integer. I checked & found that the other app is 1 instance where I forgot to use Option Explicit. I will be fixing that mistake.
 
Joined
Feb 21, 2018
Messages
216
Reaction score
86
so I would replace switchAddress = "AW1" with Dim aw1 as integer. I checked & found that the other app is 1 instance where I forgot to use Option Explicit. I will be fixing that mistake.
No.... Instead of replacing it please insert a line ....write it as the first line of the Sub

Dim switchAddress as variance

Remember AW1 is not a variable.... Rather a cell address.... Sorry for confusing you.
 
Last edited:
Joined
Dec 13, 2017
Messages
71
Reaction score
19
No.... Instead of replacing it please insert a line ....write it as the first line of the Sub

Dim switchAddress as variance

Remember AW1 is not a variable.... Rather a cell address.... Sorry for confusing you.
I added the line as suggested, now it says: "User - defined type not defined"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top