Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case Select
Thank you Bob
Appreciate the assistance Regards Mark. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Minimum value after select case | Excel Worksheet Functions | |||
Select Case | Excel Discussion (Misc queries) | |||
VBA select case question | Excel Discussion (Misc queries) | |||
Need help on Select Case | Excel Worksheet Functions |