Case Select

[NoodNutt]

G'day Everyone

Using Excel 2K - 2K3 compatible format
Range("G5:G2004") is setup with DateTime Picker.

Was hoping to setup a Case Select Statement

Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("'2008'!G5:G2004")) Is Nothing Then

' I have used the [Between] & [And], and the # around the dates as I use
this in Access and thought it easier to express what I am attempting to
achieve.

Case Select
IF("'2008'!G5:G2004"=[Between] #01-Jan-08# [And] #31-Jan-08#,
"'2008'!F5:F2004"="JAN")

ElseIF ("'2008'!G5:G2004"=[Between] #01-Feb-08# [And] *#28-Feb-08#,
"'2008'!F5:F2004"="FEB")
ElseIF ("'208'!G5:G2004"=[Between] #01-Mar-08# [And] #31-Mar-08#,
"'2008'!F5:F2004"="MAR")

And so on upto "DEC"

the bracketed [] comments are for explaination clarity only, they are not
NameRanges
*no automation is required for leap year as code will be updated annually
when new sheet is created.

I look for to any suggestions
TIA
Mark.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("G5:G2004")) Is Nothing Then

' I have used the [Between] & [And], and the # around the dates _
as I use this in Access and thought it easier to express what _
I am attempting to achieve.

Select Case True

Case Target.Value >= DateSerial(2008, 1, 1) And Target.Value <
DateSerial(2008, 2, 1)

'do something

Case Target.Value >= DateSerial(2008, 1, 2) And Target.Value <
DateSerial(2008, 3, 1)

'do something

Case Target.Value >= DateSerial(2008, 1, 3) And Target.Value <
DateSerial(2008, 4, 1)

'do something

'etc.
End Select
End If

End Sub

 

[NoodNutt]

Thank you Bob

Appreciate the assistance

Regards
Mark.

 

[NoodNutt]

Bob

Just a couple of follow up questions if I may.

1. the date format in your response, is it

(yyyy,mm,dd) or (yyyy,dd,mm)

2. with the 'Do Something

What would be the best way to set the focus of then Target.Value Cell +1
Cell to the right.

By that I mean, if the the target cell a1, then I need "response" in b1.

Regards
Mark.

 

[NoodNutt]

Bob

Sorry to be a pest.

Seems I can't use this code.

Keep getting Compile Error: Ambiguous Name Detected (Worksheet_Change)

I'm fairly certain it has to do with the DateTime picker:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G5:G2004")) Is Nothing Then
If DTPicker1.Visible Then DTPicker1.Visible = False
End If
End Sub

Any thoughts.
Mark.

 

[Bob Phillips]

That means that you already have a Worksheet_Change event procedure, so you
need to amalgamate them.

 

[NoodNutt]

Thx again Bob.

Just a couple of follow up questions if I may.

1. the date format in your response, is it

(yyyy,mm,dd) or (yyyy,dd,mm)

2. with the 'Do Something

What would be the best way to move the focus from the Target.Value Cell, to
the Cell at the immediate right.

By that I mean, if the Target. Value Cell = G5, then I need the response in
H5 to = "SEP".

Regards
Mark.

 

[NoodNutt]

Bob

After mucking around for a while I managed to work it through and got the
result I was looking for.

Once again thx heaps for your assistance & patience.

Regards
Mark.