Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Morning,
I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ ..Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ ..Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel
is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Thanks a lot for trying.
Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Is the problem with formulas or the data. Put a message box a the beginning
of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
All the data is on one worksheet.
There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
I assume yo are NOT getting the messagebox. Is this correct?
The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Yes correct. I see no message box.
All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
I asumed you put the message box at the 1st line of the macro. if you are
not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
With this code I'm finding an issue on the following line -:
Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
I added spaces at the beginning of the line which cuased the end of the line
to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If Application.EnableEvents = True End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Hi,
I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
I forgot that line was in the code. Now it should loop. The problem was G4
was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 "LiAD" wrote: Thanks a lot for trying. Unfortunately I cant get that to drive it either. Again formulas update but not the gantt. "joel" wrote: I think I figured out the problem. 1 hour is not 1 but 1/24. Time in excel is one day = 1. Try this fix from StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") to: StartTime = Hour(Cells(t.Row, "J")) EndTime = Hour(Cells(t.Row, "K")) "LiAD" wrote: Morning, I have a code that reads three columns, colour (black, blue, green, brown and two numbers, start hours and finish hours, (1, 2, 3 etc). The code then plots a series of coloured boxes, one colour per row to produce a gant chart. It works fine if I enter the three input columns manually, but if I drive the hour columns through formulas the coloured lines do not update with changes. So for example I want to plot blue from 0 to 3 hours on row 4 and black from 5 to 7 hours on row 5. Enter the data hit return and it works, I get a blue bar four boxes long starting in L4 and a black one 3 boxes long starting in P5. Then I change the row 5 inputs to be; start time = finish time of row 4 + 1 end time = start time + 3 Any changes to the row 4 inputs will not update the gantt, although the values in cells driving the gantt will change. Can anyone help me know why this happens or how to fix it? The code I'm using is below if it helps. Thanks LiAD Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub Application.EnableEvents = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else Exit Sub ' color is no good End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Hi,
With the new code and the formulas in cols J and K, fed by Col G the code stops at this line: If Not Intersect(t, i) Is Nothing Then Would you have any more ideas? Thanks a lor for your help "joel" wrote: I forgot that line was in the code. Now it should loop. The problem was G4 was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. The function is working fine if I dont use formulas in the worksheet. "joel" wrote: Is the problem with formulas or the data. Put a message box a the beginning of the worksheet change to see if yo are getting into the function. Also, are you formulas referecing other worksheets? worksheet change only apply to the sheet where they are located. Private Sub Worksheet_Change(ByVal Target As Range) msgbox("Data : " & target &"; Addr : " & Target.address) MyBlue = 5 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
the code is working the way I intended it to work. Since changing G4 doesn't
directly change the gant chart nothing will happen. The code loops 1 time for each cell that gets changed on the worksheet. When you change G4 the cell K4 will also change and the code will loop a 2nd time for K4. When K4 = target (the 2nd time though the loop) the code should update the gant chart. "LiAD" wrote: Hi, With the new code and the formulas in cols J and K, fed by Col G the code stops at this line: If Not Intersect(t, i) Is Nothing Then Would you have any more ideas? Thanks a lor for your help "joel" wrote: I forgot that line was in the code. Now it should loop. The problem was G4 was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: All the data is on one worksheet. There is no problem with data entered as numbers. The problem is only with data generated by formulas, for example if in cell K4 I put = J4 + G4 then I change the value in G4 the gantt will not update to the new value in K4. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Is it not possible that I can use G4 to update K4 which will then change the
gannt? For example in G5 I have 2, J5 I have =K4, K5 I have = J5+G5. For now we can imagine that the gannt shows a coloured line 3 cells long. Now I change G5 from 2 to 4 and hit enter and the gannt will automatically change from 3 to 5 cells long. Is this possible? Thanks "joel" wrote: the code is working the way I intended it to work. Since changing G4 doesn't directly change the gant chart nothing will happen. The code loops 1 time for each cell that gets changed on the worksheet. When you change G4 the cell K4 will also change and the code will loop a 2nd time for K4. When K4 = target (the 2nd time though the loop) the code should update the gant chart. "LiAD" wrote: Hi, With the new code and the formulas in cols J and K, fed by Col G the code stops at this line: If Not Intersect(t, i) Is Nothing Then Would you have any more ideas? Thanks a lor for your help "joel" wrote: I forgot that line was in the code. Now it should loop. The problem was G4 was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. Does the macro start working again if yo only change the formulas back to a number? I want to make sure that events weren't disabled. Try changing your error trapping in the VBA menu Tools - OPtions - General - Stop on all errors This will give additional diagnostics to help solve the problem. Post the data in cells J4, G4, and K4. "LiAD" wrote: |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
I didn't say that that it is not possible!!!!!!!
You have the following in cell K4 = J4 + G4 When G4 change so does K4. The worksheet change function gets call twice. the first time when you change G4 and the second time after the worksheet is re-calculated and K4 changes. You don't need to have the macro run when G4 changes. The line "If Not Intersect(t, i) Is Nothing Then" stops the code from running when G4 changes put allows the code to run when K4 changes. Do you have the worksheet set to Recaculate Automatically??? The 2nd time the function is called after K4 is changed the Gant chart should update. You also may need to put HOUR() back into the code. You never posted what data was in G4 and J4. "LiAD" wrote: Is it not possible that I can use G4 to update K4 which will then change the gannt? For example in G5 I have 2, J5 I have =K4, K5 I have = J5+G5. For now we can imagine that the gannt shows a coloured line 3 cells long. Now I change G5 from 2 to 4 and hit enter and the gannt will automatically change from 3 to 5 cells long. Is this possible? Thanks "joel" wrote: the code is working the way I intended it to work. Since changing G4 doesn't directly change the gant chart nothing will happen. The code loops 1 time for each cell that gets changed on the worksheet. When you change G4 the cell K4 will also change and the code will loop a 2nd time for K4. When K4 = target (the 2nd time though the loop) the code should update the gant chart. "LiAD" wrote: Hi, With the new code and the formulas in cols J and K, fed by Col G the code stops at this line: If Not Intersect(t, i) Is Nothing Then Would you have any more ideas? Thanks a lor for your help "joel" wrote: I forgot that line was in the code. Now it should loop. The problem was G4 was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. All worksheet options work correctly. There are no other macros etc in this workbook, just cells filled with text, raw numbers and the formulas in question. Yes if I change the formula back to a number it works no problems again. Changing the error trapping has not shown any issues or errors any time I've tried to run it. I see no messages in excel or VB, it just doesnt seem to work with formulas. Do you think the Hour() bit is important? I dont see why this should be time related problem as its just dealing with numbers. I have tried it but no luck. "joel" wrote: I assume yo are NOT getting the messagebox. Is this correct? The worksheet function shold always work unless you are getting an error in antoerh macro. You said the only thing you changed was the number to a formula. this means you worksheet options are working correctly. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Yes the formula you quoted is what I have.
Yes the worksheet is on auto calculate. In J4 I have the number 0 formatted as a number in G4 is another number which I'm changing to try to see if it works, between 1 and 5. Tried hour and it doesn't change anything for me here. I assume you are talking about the double change, once for when I change G4 and once for when the formula automatically changes K4 due to the change made in G4? Why does the code need to worry about G4, can it just not be driven off the output generated in K4? "joel" wrote: I didn't say that that it is not possible!!!!!!! You have the following in cell K4 = J4 + G4 When G4 change so does K4. The worksheet change function gets call twice. the first time when you change G4 and the second time after the worksheet is re-calculated and K4 changes. You don't need to have the macro run when G4 changes. The line "If Not Intersect(t, i) Is Nothing Then" stops the code from running when G4 changes put allows the code to run when K4 changes. Do you have the worksheet set to Recaculate Automatically??? The 2nd time the function is called after K4 is changed the Gant chart should update. You also may need to put HOUR() back into the code. You never posted what data was in G4 and J4. "LiAD" wrote: Is it not possible that I can use G4 to update K4 which will then change the gannt? For example in G5 I have 2, J5 I have =K4, K5 I have = J5+G5. For now we can imagine that the gannt shows a coloured line 3 cells long. Now I change G5 from 2 to 4 and hit enter and the gannt will automatically change from 3 to 5 cells long. Is this possible? Thanks "joel" wrote: the code is working the way I intended it to work. Since changing G4 doesn't directly change the gant chart nothing will happen. The code loops 1 time for each cell that gets changed on the worksheet. When you change G4 the cell K4 will also change and the code will loop a 2nd time for K4. When K4 = target (the 2nd time though the loop) the code should update the gant chart. "LiAD" wrote: Hi, With the new code and the formulas in cols J and K, fed by Col G the code stops at this line: If Not Intersect(t, i) Is Nothing Then Would you have any more ideas? Thanks a lor for your help "joel" wrote: I forgot that line was in the code. Now it should loop. The problem was G4 was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime).Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Yes correct. I see no message box. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
You are right. The code doesn't worry about G4. That is what I've been
trying to explain. That is why the code was stopping on the line "If Not Intersect(t, i) Is Nothing Then" when G4 was being processed. The code should be execute when K4 is processed Yo don't need hours() if your numbers are 0 to 23 and not a time format. Is the code working or not working? yo ned to check that the routine gets called twice when g4 is changed. I think you understand better how the code is working once you started to debug the problems. "LiAD" wrote: Yes the formula you quoted is what I have. Yes the worksheet is on auto calculate. In J4 I have the number 0 formatted as a number in G4 is another number which I'm changing to try to see if it works, between 1 and 5. Tried hour and it doesn't change anything for me here. I assume you are talking about the double change, once for when I change G4 and once for when the formula automatically changes K4 due to the change made in G4? Why does the code need to worry about G4, can it just not be driven off the output generated in K4? "joel" wrote: I didn't say that that it is not possible!!!!!!! You have the following in cell K4 = J4 + G4 When G4 change so does K4. The worksheet change function gets call twice. the first time when you change G4 and the second time after the worksheet is re-calculated and K4 changes. You don't need to have the macro run when G4 changes. The line "If Not Intersect(t, i) Is Nothing Then" stops the code from running when G4 changes put allows the code to run when K4 changes. Do you have the worksheet set to Recaculate Automatically??? The 2nd time the function is called after K4 is changed the Gant chart should update. You also may need to put HOUR() back into the code. You never posted what data was in G4 and J4. "LiAD" wrote: Is it not possible that I can use G4 to update K4 which will then change the gannt? For example in G5 I have 2, J5 I have =K4, K5 I have = J5+G5. For now we can imagine that the gannt shows a coloured line 3 cells long. Now I change G5 from 2 to 4 and hit enter and the gannt will automatically change from 3 to 5 cells long. Is this possible? Thanks "joel" wrote: the code is working the way I intended it to work. Since changing G4 doesn't directly change the gant chart nothing will happen. The code loops 1 time for each cell that gets changed on the worksheet. When you change G4 the cell K4 will also change and the code will loop a 2nd time for K4. When K4 = target (the 2nd time though the loop) the code should update the gant chart. "LiAD" wrote: Hi, With the new code and the formulas in cols J and K, fed by Col G the code stops at this line: If Not Intersect(t, i) Is Nothing Then Would you have any more ideas? Thanks a lor for your help "joel" wrote: I forgot that line was in the code. Now it should loop. The problem was G4 was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime).Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck - VB Help needed
Sorry sorry but its still not working. Its still stopping when I use
formulas at If Not Intersect(t, i) Is Nothing Then When I set the stop there it never gets past, I've checked every line below that. "joel" wrote: You are right. The code doesn't worry about G4. That is what I've been trying to explain. That is why the code was stopping on the line "If Not Intersect(t, i) Is Nothing Then" when G4 was being processed. The code should be execute when K4 is processed Yo don't need hours() if your numbers are 0 to 23 and not a time format. Is the code working or not working? yo ned to check that the routine gets called twice when g4 is changed. I think you understand better how the code is working once you started to debug the problems. "LiAD" wrote: Yes the formula you quoted is what I have. Yes the worksheet is on auto calculate. In J4 I have the number 0 formatted as a number in G4 is another number which I'm changing to try to see if it works, between 1 and 5. Tried hour and it doesn't change anything for me here. I assume you are talking about the double change, once for when I change G4 and once for when the formula automatically changes K4 due to the change made in G4? Why does the code need to worry about G4, can it just not be driven off the output generated in K4? "joel" wrote: I didn't say that that it is not possible!!!!!!! You have the following in cell K4 = J4 + G4 When G4 change so does K4. The worksheet change function gets call twice. the first time when you change G4 and the second time after the worksheet is re-calculated and K4 changes. You don't need to have the macro run when G4 changes. The line "If Not Intersect(t, i) Is Nothing Then" stops the code from running when G4 changes put allows the code to run when K4 changes. Do you have the worksheet set to Recaculate Automatically??? The 2nd time the function is called after K4 is changed the Gant chart should update. You also may need to put HOUR() back into the code. You never posted what data was in G4 and J4. "LiAD" wrote: Is it not possible that I can use G4 to update K4 which will then change the gannt? For example in G5 I have 2, J5 I have =K4, K5 I have = J5+G5. For now we can imagine that the gannt shows a coloured line 3 cells long. Now I change G5 from 2 to 4 and hit enter and the gannt will automatically change from 3 to 5 cells long. Is this possible? Thanks "joel" wrote: the code is working the way I intended it to work. Since changing G4 doesn't directly change the gant chart nothing will happen. The code loops 1 time for each cell that gets changed on the worksheet. When you change G4 the cell K4 will also change and the code will loop a 2nd time for K4. When K4 = target (the 2nd time though the loop) the code should update the gant chart. "LiAD" wrote: Hi, With the new code and the formulas in cols J and K, fed by Col G the code stops at this line: If Not Intersect(t, i) Is Nothing Then Would you have any more ideas? Thanks a lor for your help "joel" wrote: I forgot that line was in the code. Now it should loop. The problem was G4 was no in the intesect area and was causing the macro to stop. the code wilnow continue after G4. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Not Intersect(t, i) Is Nothing Then BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Interior.ColorIndex = MyBack Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)) _ .Font.ColorIndex = MyFont Else 'Start Time good end time not good Cells(t.Row, "L").Offset(0, StartTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, StartTime) _ .Font.ColorIndex = MyFont End If Else If EndTime < "" And _ IsNumeric(EndTime) Then 'Start time no good, end time good Cells(t.Row, "L").Offset(0, EndTime) _ .Interior.ColorIndex = MyBack Cells(t.Row, "L").Offset(0, EndTime) _ .Font.ColorIndex = MyFont Else 'start time and end time no good End If End If End If End If Next t Application.EnableEvents = True End Sub "LiAD" wrote: Hi, I have found that with formulas the code stops after Set i = Range("I4:K20") Set t = Target If Intersect(t, i) Is Nothing Then Exit Sub So when I change a cell the code exits almost immediately. Interesting if I copy the formula down one cell at a time after a change it updates. If I copy then change or copy more than one cell at a time it doesnt work. Does this help at all? I think I owe you serious beer after this one! "joel" wrote: I added spaces at the beginning of the line which cuased the end of the line to wrap from Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = MyBack to (aaded line continuation character - an underline) Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = _ MyBack "LiAD" wrote: With this code I'm finding an issue on the following line -: Cells(t.Row, "L").Offset(0, StartTime).Interior.ColorIndex = It gives a compile error: Syntax error. "joel" wrote: I asumed you put the message box at the 1st line of the macro. if you are not seeing the message box either there is something wrong with the data or another macro is causing an error. I would need to see the data to determine what the probelm is. You could add a break point on the worksheet change line in your macro. click on this line with mouse and press F9. the line should change color. First make sure you get to the break point when the macro is working. Press F5 to continue after getting to the break point. Doing this will tell us if the data is bad (gets to this line) or something else in the workbook is failing (doesn't get to the line). The hour will has nothing to do with the function being called. After we find out why the macro isn't runni9ng then we have to address the hour issue. I would need to see samples of the data to determine if the hour is needed. It is only needed if you have a cell formated as time. You also need to change the function to add a loop since yo are changing more than one value in the range. When you change J4 you are also changing K4 so the function need to loop. Exscel does all the calculattions on the worksheet before calling the worksheet change function. Private Sub Worksheet_Change(ByVal Target As Range) MyBlue = 5 MyGreen = 4 MyBrown = 18 MyBlack = 1 MyGrey = 15 MyWhite = 2 Application.EnableEvents = False Set i = Range("I4:K20") For Each t In Target If Intersect(t, i) Is Nothing Then Exit Sub BadColor = False Select Case UCase(Cells(t.Row, "I")) Case "BLUE": MyBack = MyBlue MyWhite = MyBlack Case "GREEN": MyBack = MyGreen MyFont = MyBlack Case "BROWN": MyBack = MyBrown MyFont = MyBlack Case "BLACK": MyBack = MyBlack MyFont = MyWhite Case "GREY": MyBack = MyGrey MyFont = MyBlack Case Else ' color is no good BadColor = True End Select 'clear old colors Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Interior.ColorIndex = xlColorIndexNone 'make font black Range(Cells(t.Row, "k"), Cells(t.Row, "k").Offset(0, 23)) _ .Font.ColorIndex = MyBlack StartTime = Cells(t.Row, "J") EndTime = Cells(t.Row, "K") If BadColor = False Then If StartTime < "" And _ IsNumeric(StartTime) Then 'Start time is valid If EndTime < "" And _ IsNumeric(EndTime) Then 'both starttime and end time are good Range(Cells(t.Row, "L").Offset(0, StartTime), _ Cells(t.Row, "L").Offset(0, EndTime)).Interior.ColorIndex = MyBack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am stuck | Excel Worksheet Functions | |||
Stuck and need help | Excel Programming | |||
Help, please, I'm stuck | Excel Discussion (Misc queries) | |||
Ok now im stuck | Excel Programming | |||
Stuck! | Excel Programming |