Excel Vba to change displayed year automatically.

FrancisM

FrancisM

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:
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:
AmjiBhai said:
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
Click to expand...
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.
 
FrancisM said:
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.
Click to expand...

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 said:
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
Click to expand...
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:
AmjiBhai said:
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
Click to expand...
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.
 
FrancisM said:
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.
Click to expand...

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


www.excel-easy.com

Option Explicit in Excel VBA

We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.
www.excel-easy.com www.excel-easy.com
 
AmjiBhai said:
Remove this command (probably as the top line of your module, this is compelling you to use Dim for all variables declared)
Option Explicit


www.excel-easy.com

Option Explicit in Excel VBA

We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.
www.excel-easy.com www.excel-easy.com
Click to expand...
Yes, I always use Option Explicit. I had it drilled into me when I first wrote code.
 
AmjiBhai said:
Idealistic way is to use it.
If you want to keep it.... Just use
Dim aw1 as integer
Click to expand...
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.
 
FrancisM said:
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.
Click to expand...
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:
AmjiBhai said:
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.
Click to expand...
I added the line as suggested, now it says: "User - defined type not defined"
 
Back
Top