Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi everyone,
The following code is used to change the colour in a cell after a given (validated) entry. In the next cell the date of the change is written and another cell is used for a dash or the word Yes when a task is completed (5 purple). This code works fine in several sheets and has been working until recently in a sheet where I keep track of completed tasks. What happens when I walk through the code step by step, is that the code suddenly stops after ".Columns(2).Value = (Now)". I don't understand what happens here. Any suggestions? TIA, Rob ******************* Code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim myRng As Range, Number As Integer Application.EnableEvents = True Number = Sh.Index Select Case Number Case 1, 2 With Target If .Cells.Count 1 Then Exit Sub 'If (.Row = 4 And .Row <= 100 And .Column = 8 And _ .Column Mod 2 = 0 And .Column <= 15) Then If (.Row = 4 And .Row <= 250 And .Column = 7) Then Set myRng = Target.Offset(0, 0).Resize(1, 1) Select Case LCase(Target.Value) Case Is = "0 blue" myRng.Interior.ColorIndex = 5 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "1 orange" myRng.Interior.ColorIndex = 46 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "2 green" myRng.Interior.ColorIndex = 4 myRng.Font.ColorIndex = 0 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "3 yellow" myRng.Interior.ColorIndex = 6 myRng.Font.ColorIndex = 0 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "4 red" myRng.Interior.ColorIndex = 3 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "5 purple" myRng.Interior.ColorIndex = 39 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "Yes" Case Else 'Set myRng = Target.Offset(0, -1).Resize(1, 1) 'myRng.Interior.ColorIndex = xlNone Set myRng = Target.Offset(0, 0).Resize(1, 1) myRng.Interior.ColorIndex = xlNone .Columns(2).Value = "" .Columns(3).Value = "-" End Select End If End With Case Else End Select End Sub ******************* |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Dqsp,
Near the head of your procedure, you should change: Application.EnableEvents = True to On Error GoTo XIT Application.EnableEvents = False Then replace: End Sub with XIT: Application.EnableEvents = True End Sub Otherwise, every cell value change effected by the procedure will be considered as a new change event and the procedure will r ecursively call itself. --- Regards. Norman "dqsp" wrote in message ... Hi everyone, The following code is used to change the colour in a cell after a given (validated) entry. In the next cell the date of the change is written and another cell is used for a dash or the word Yes when a task is completed (5 purple). This code works fine in several sheets and has been working until recently in a sheet where I keep track of completed tasks. What happens when I walk through the code step by step, is that the code suddenly stops after ".Columns(2).Value = (Now)". I don't understand what happens here. Any suggestions? TIA, Rob ******************* Code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim myRng As Range, Number As Integer Application.EnableEvents = True Number = Sh.Index Select Case Number Case 1, 2 With Target If .Cells.Count 1 Then Exit Sub 'If (.Row = 4 And .Row <= 100 And .Column = 8 And _ .Column Mod 2 = 0 And .Column <= 15) Then If (.Row = 4 And .Row <= 250 And .Column = 7) Then Set myRng = Target.Offset(0, 0).Resize(1, 1) Select Case LCase(Target.Value) Case Is = "0 blue" myRng.Interior.ColorIndex = 5 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "1 orange" myRng.Interior.ColorIndex = 46 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "2 green" myRng.Interior.ColorIndex = 4 myRng.Font.ColorIndex = 0 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "3 yellow" myRng.Interior.ColorIndex = 6 myRng.Font.ColorIndex = 0 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "4 red" myRng.Interior.ColorIndex = 3 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "5 purple" myRng.Interior.ColorIndex = 39 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "Yes" Case Else 'Set myRng = Target.Offset(0, -1).Resize(1, 1) 'myRng.Interior.ColorIndex = xlNone Set myRng = Target.Offset(0, 0).Resize(1, 1) myRng.Interior.ColorIndex = xlNone .Columns(2).Value = "" .Columns(3).Value = "-" End Select End If End With Case Else End Select End Sub ******************* |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Thanks Norman,
Unfortunatelly it doesn't work. When reaching # .Columns(2).Value = (Now)". # the error kicks in and the colour isn't changed. Any other suggestions? TIA Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Dqsp,
The suggested code works for me. If you wish, I can send you a copy of my test workbook, in reesponse to an email to: norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman "dqsp" wrote in message ... Thanks Norman, Unfortunatelly it doesn't work. When reaching # .Columns(2).Value = (Now)". # the error kicks in and the colour isn't changed. Any other suggestions? TIA Rob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Ddsp
Perhaps, more usefully, you could send me a copy of your problematic workbook; remove any sensitivr data. --- Regards. Norman "Norman Jones" wrote in message ... Hi Dqsp, The suggested code works for me. If you wish, I can send you a copy of my test workbook, in reesponse to an email to: norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Norman,
The workbook always worked fine for me as well, until recently. It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I tried the same workbook at home and it doesn't work either. I'm sending it to your e-mailadres. Thanks for helping! Rob On 29 mei, 19:59, "Norman Jones" wrote: Hi Ddsp Perhaps, more usefully, you could send me a copy of your problematic workbook; remove any sensitivr data. --- Regards. Norman "Norman Jones" wrote in message ... Hi Dqsp, The suggested code works for me. If you wish, I can send you a copy of my test workbook, in reesponse to an email to: * * * * norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Rob,
I received your file and it works, as is, straight out of the box! I have sent you an updated file in which I have done nothing other than successfully to make entries after April 18 in column G. Therefore, I suspect that you have the Application events disabled. Perhaps you have other code which is disabling events and then failing to reinstate them. FWIW, with any code which disables Application settings, I always use an error handler which reinstates the required values. I would suggest, therefore, two things: - In the Immediate window (Ctrl-G from the VBE), type: Application.EnableEvents = True and hit Enter; - Check any other code in the workbook (or any other workbooks which may be opened) to ensure that any instruction which disables events, includes an error handler to restore the setting. --- Regards. Norman "dqsp" wrote in message ... Hi Norman, The workbook always worked fine for me as well, until recently. It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I tried the same workbook at home and it doesn't work either. I'm sending it to your e-mailadres. Thanks for helping! Rob On 29 mei, 19:59, "Norman Jones" wrote: Hi Ddsp Perhaps, more usefully, you could send me a copy of your problematic workbook; remove any sensitivr data. --- Regards. Norman "Norman Jones" wrote in message ... Hi Dqsp, The suggested code works for me. If you wish, I can send you a copy of my test workbook, in reesponse to an email to: norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
On 30 mei, 14:59, "Norman Jones"
wrote: Hi Rob, I received your file and it works, as is, straight out of the box! I have sent you an updated file in which I have done nothing other than successfully to make entries after April 18 in column G. Therefore, I suspect that you have the Application events disabled. Perhaps you have other code which is disabling events and then failing to reinstate them. FWIW, with any code which disables Application settings, I always use an error handler which reinstates the required values. I would suggest, therefore, two things: * * - In the Immediate window (Ctrl-G from * * * the VBE), type: * * * Application.EnableEvents = True * * * and hit Enter; * * - Check any other code in the workbook * * * (or any other workbooks which may be * * * opened) to ensure that any instruction * * * which *disables events, includes an error * * * handler to restore the setting. --- Regards. Norman "dqsp" wrote in message ... Hi Norman, The workbook always worked fine for me as well, until recently. It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I tried the same workbook at home and it doesn't work either. I'm sending it to your e-mailadres. Thanks for helping! Rob On 29 mei, 19:59, "Norman Jones" wrote: Hi Ddsp Perhaps, more usefully, you could send me a copy of your problematic workbook; remove any sensitivr data. --- Regards. Norman "Norman Jones" wrote in message ... Hi Dqsp, The suggested code works for me. If you wish, I can send you a copy of my test workbook, in reesponse to an email to: norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven -- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Well, it's the only workbook opened and the only code used. So shoot me, it really doesn't work after your suggested action in the immediate window. At home I have the same problem with this. In another workbook were I use simular code it also stopped working. Could it be a general Excel setting? TIA for your answer, really appreciate your actions! Rob |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Rob,
Run the following sub and then try changing column G values. '======== Public Sub Tester() Application.EnableEvents = True End Sub '<<======== --- Regards. Norman "dqsp" wrote in message ... On 30 mei, 14:59, "Norman Jones" wrote: Hi Rob, I received your file and it works, as is, straight out of the box! I have sent you an updated file in which I have done nothing other than successfully to make entries after April 18 in column G. Therefore, I suspect that you have the Application events disabled. Perhaps you have other code which is disabling events and then failing to reinstate them. FWIW, with any code which disables Application settings, I always use an error handler which reinstates the required values. I would suggest, therefore, two things: - In the Immediate window (Ctrl-G from the VBE), type: Application.EnableEvents = True and hit Enter; - Check any other code in the workbook (or any other workbooks which may be opened) to ensure that any instruction which disables events, includes an error handler to restore the setting. --- Regards. Norman "dqsp" wrote in message ... Hi Norman, The workbook always worked fine for me as well, until recently. It's used on a Citrix PS 4 SBC-environment with Excel 2003. Just now I tried the same workbook at home and it doesn't work either. I'm sending it to your e-mailadres. Thanks for helping! Rob On 29 mei, 19:59, "Norman Jones" wrote: Hi Ddsp Perhaps, more usefully, you could send me a copy of your problematic workbook; remove any sensitivr data. --- Regards. Norman "Norman Jones" wrote in message ... Hi Dqsp, The suggested code works for me. If you wish, I can send you a copy of my test workbook, in reesponse to an email to: norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven -- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Well, it's the only workbook opened and the only code used. So shoot me, it really doesn't work after your suggested action in the immediate window. At home I have the same problem with this. In another workbook were I use simular code it also stopped working. Could it be a general Excel setting? TIA for your answer, really appreciate your actions! Rob |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Norman,
I allready tried that, but no succes. At home it works (had to do with macroprotection (grr)). At my work it doesn't. If I debug with F8, I stll see that the macro ends after .Columns(2).Value = (Now) Any other idea? Thanks again, Rob On 30 mei, 18:11, "Norman Jones" wrote: Hi Rob, Run the following sub and then try changing column G values. '======== Public Sub Tester() * * Application.EnableEvents = True End Sub '<<======== --- Regards. Norman |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Rob,
Earlier, you reported that the code was not working either on your home or work systems; now, at least, works on your home system. Reviwing the thread, I note that you only discuss the 'error' in terms of stepping though the code. Therefore, two questions arise: What happens when remove any stop points and simply select a new value from one of the data validation dropdowns in column G? What error message and error number are reported? If the error complains about the Now function, I would suggest that you check the references: When the code errors, go to the VBE and select the project in the Project Explorer Window Hit the reset button and look at the Menu | Tools | References list Look for any references marked as "MISSING". Either uncheck the reference or, if the reference is needed, try the browse button to locate the library. --- Regards. Norman "dqsp" wrote in message ... Hi Norman, I allready tried that, but no succes. At home it works (had to do with macroprotection (grr)). At my work it doesn't. If I debug with F8, I stll see that the macro ends after .Columns(2).Value = (Now) Any other idea? Thanks again, Rob On 30 mei, 18:11, "Norman Jones" wrote: Hi Rob, Run the following sub and then try changing column G values. '======== Public Sub Tester() Application.EnableEvents = True End Sub '<<======== --- Regards. Norman |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi Norman,
I only stepped through the code to find the errorspot. The macro just stops, no errormessage from Excel. I added some code to produce a msgbox with the errorcode and the error is a "1004 Application or object defined error", which doesn't surprise me. No references missing. I replaced the (now) with "01-06-2008" (Dutch format). Same errorcode. When I delete the two lines for the values for column 2 and 3, no error anymore, but the cell isn't colored blue (for example). When I delete only the columns(2) line, the same error occurs. Dark forces at work if you ask me! Still any ideas? Thanks again, regards, Rob On 31 mei, 18:22, "Norman Jones" wrote: Hi Rob, Earlier, you reported that the code was not working either on your home or work systems; now, at least, works on your home system. Reviwing the thread, I note that you only discuss the 'error' in terms of stepping though the code. Therefore, two questions arise: What happens when remove any stop points and simply select a new value from one of the data validation dropdowns in column G? What error message and error number are reported? If the error complains about the Now function, I would suggest that you check the references: When the code errors, go to the VBE and select the project in the Project Explorer Window Hit the reset button and look at the Menu | Tools | References list Look for any references marked as "MISSING". Either uncheck the reference or, if the reference is needed, try the browse button *to locate the library. --- Regards. Norman "dqsp" wrote in message ... Hi Norman, I allready tried that, but no succes. At home it works (had to do with macroprotection (grr)). At my work it doesn't. If I debug with F8, I stll see that the macro ends after .Columns(2).Value = (Now) Any other idea? Thanks again, Rob On 30 mei, 18:11, "Norman Jones" wrote: Hi Rob, Run the following sub and then try changing column G values. '======== Public Sub Tester() Application.EnableEvents = True End Sub '<<======== --- Regards. Norman- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Oh, and when I use (now) in the 'Case else' statement instead of "" in
the columns(2) line, todays date is presented in the cell. On 31 mei, 17:50, dqsp wrote: Hi Norman, I allready tried that, but no succes. At home it works (had to do with macroprotection (grr)). At my work it doesn't. If I debug with F8, I stll see that the macro ends after .Columns(2).Value = (Now) Any other idea? Thanks again, Rob On 30 mei, 18:11, "Norman Jones" wrote: Hi Rob, Run the following sub and then try changing column G values. '======== Public Sub Tester() * * Application.EnableEvents = True End Sub '<<======== --- Regards. Norman- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Tab Colours | Excel Worksheet Functions | |||
Changing colours by conditions | Excel Discussion (Misc queries) | |||
Changing chart colours using VBA | Excel Programming | |||
Changing Colours With Protection | Excel Discussion (Misc queries) | |||
Changing colours in chart | Charts and Charting in Excel |