Excel Vba to change displayed year automatically.


Joined
Dec 13, 2017
Messages
45
Reaction score
11
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:
Ad

Advertisements

Joined
Feb 21, 2018
Messages
160
Reaction score
62
Good. Very Interesting Question.

I am working on it...will get back to you shortly....
 
Joined
Feb 21, 2018
Messages
160
Reaction score
62
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
45
Reaction score
11
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
160
Reaction score
62
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
45
Reaction score
11
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
45
Reaction score
11
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
160
Reaction score
62
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


 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
45
Reaction score
11
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
Feb 21, 2018
Messages
160
Reaction score
62
Yes, I always use Option Explicit. I had it drilled into me when I first wrote code.
Idealistic way is to use it.
If you want to keep it.... Just use
Dim aw1 as integer
Or as variance ....its safer
 
Joined
Dec 13, 2017
Messages
45
Reaction score
11
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
160
Reaction score
62
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
45
Reaction score
11
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"
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
45
Reaction score
11
I am sorry misspelled variant

Dim switchAddress as variant
IT works. I noticed the cell ranges were different in the 2 apps. I thank you for getting it to work & for helping me find my mistake in the other app.
 

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

Similar Threads

Access 2 Digit year in Access Text Box 3

Top