Case Select
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. |
Case Select
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 -- __________________________________ HTH Bob "NoodNutt" wrote in message ... 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. |
Case Select
Thank you Bob
Appreciate the assistance Regards Mark. |
Case Select
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. |
Case Select
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. |
Case Select
That means that you already have a Worksheet_Change event procedure, so you
need to amalgamate them. -- __________________________________ HTH Bob "NoodNutt" wrote in message ... 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. |
Case Select
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. |
Case Select
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. |
All times are GMT +1. The time now is 12:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com