Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Woes
Hi Guys
I am wanting to use the following code, which to an extent works, but then fails on the 2nd IF. this code is attached to the Inbound Sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 5 Then Exit Sub On Error GoTo ErrHandler With Application .ScreenUpdating = False .EnableEvents = False End With If Target.Column = 11 Then If IsNumeric(Target.Value) Then With Target .Offset(, -10).Resize(, 14).Interior.ColorIndex = 6 .Offset(, -9).Select End With Call Module8.Plus_Chep_Out If Target.Value = "" Then With Target .Offset(, -10).Resize(, 14).Interior.ColorIndex = 2 .Offset(, -9).Select End With Call Module8.Minus_Chep_Out End If End If End If ErrHandler: With Application .ScreenUpdating = True .EnableEvents = True End With End Sub If I enter a number in column 11, it fires the "Plus_Chep" call, but! When I delete the number from the same cell, I fully expect it to call "Minus_Chep"and remove the cell colouring from the Outbound sheet, but it does not. Both Call codes are identical with the exception that one colours and the other removes it. The frustrating part is that it partially fires the Minus_Chep call by activating the sheet in question and going to the matching cell in the find: criteria but does not remove the colouring. I was considering using a Case Select but wasn't quite sure which would be the best approach for it. As always Heaps of thanks in advance, and my glass is still half full. Cheers Mick. Sub Plus_Chep_Out() With Application .ScreenUpdating = False .EnableEvents = False End With lookfor = Selection.Value Sheets("Outbound").Activate Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Select With Selection .Offset(, -4).Resize(, 14).Interior.ColorIndex = 6 End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Sub Minus_Chep_Out() With Application .ScreenUpdating = False .EnableEvents = False End With lookfor = Selection.Value Sheets("Outbound").Activate Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Select With Selection .Offset(, -4).Resize(, 14).Interior.ColorIndex = 2 End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Woes
Living the Dream wrote:
If I enter a number in column 11, it fires the "Plus_Chep" call, but! When I delete the number from the same cell, I fully expect it to call "Minus_Chep"and remove the cell colouring from the Outbound sheet, but it does not. What does it do instead? Did you try this in single-step mode? If not: pls set a stop in line: If Target.Column = 11 Then and then run that code again (with no value in Col 11) an when code stops step through using F8. Pls tell the result here ... And you can then try the following: Replace line If Target.Value = "" Then with If Target.Value = "" or IsEmpty(Target.Value) Then and check if result is the same. This because "" and Empty ist NOT the same. Volker -- Im übrigen bin ich der Meinung, dass TCPA/TCG verhindert werden muss Wenn es vom Himmel Zitronen regnet, dann lerne, wie man Limonade macht |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Woes
Hi Mick,
Am Fri, 27 Sep 2013 23:11:10 -0700 (PDT) schrieb Living the Dream: I am wanting to use the following code, which to an extent works, but then fails on the 2nd IF. this code is attached to the Inbound Sheet. try in code window of the sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 5 Or Target.Column < 11 Then Exit Sub If IsNumeric(Target.Value) Then With Target .Offset(, -10).Resize(, 14).Interior.ColorIndex = 6 lookfor = .Offset(, -9) End With Plus_Chep_Out End If If IsEmpty(Target) Then With Target lookfor = .Offset(, -9) .Offset(, -10).Resize(, 14).Interior.Color = xlNone End With Minus_Chep_Out End If End Sub And in a standard module: Public lookfor As String Sub Plus_Chep_Out() Dim c As Range Sheets("Outbound").Activate With ActiveSheet Set c = .UsedRange.Find(lookfor, .Range("A1"), _ xlValues).Offset(, -4) End With If Not c Is Nothing Then _ c.Resize(columnsize:=14).Interior.ColorIndex = 6 End Sub Sub Minus_Chep_Out() Dim c As Range Sheets("Outbound").Activate With ActiveSheet Set c = .UsedRange.Find(lookfor, .Range("A1"), _ xlValues).Offset(, -4) End With If Not c Is Nothing Then _ c.Resize(columnsize:=14).Interior.Color = xlNone End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Woes
Adding to Volker's comments/Qs...
Is there always only going to be 1 cell changed at a time? Are you looking for a mechanism to *undo* previously shaded cells? Have you tried shading via using Conditional Formatting criteria? Attempting to process "" as a value for unshading previously shaded cells is futile at best since there's going to be 10s of 1000s of empty cells whether you're looking for an empty string returned by a formula *or* testing if the cell =Empty. As Volker states.., these are 2 different things!<g Better to store the previous value used as criteria for shading and pull it into play when Target.Value ="" or =Empty. Simplest way to test for both is to use IsEmpty()... If IsEmpty(Target) Then lColorNdx = 0 else lColorNdx = 6 Another suggestion/recommendation is to get in the habit of using object events as triggers for calling some process, but not running the process within the event... In the sheet's cde window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row 4 And Target.Column = 11 Then _ Call Toggle_ChepOut_Shading(Target, Target.Offset(, -9).Value) End Sub In a standard module: Option Explicit Sub Toggle_ChepOut_Shading(Target As Range, LookupVal) Dim lColorNdx&, rngFind As Range, sAddr$ If IsEmpty(Target) Then lColorNdx = 0 Else lColorNdx = 6 If IsEmpty(Target) Or IsNumeric(Target.Value) Then Target.Offset(, -10).Resize(, 14).Interior.ColorIndex = lColorNdx With Sheets("Outbound").UsedRange Set rngFind = .Find(What:=LookupVal, _ After:=.Cells(1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not rngFind Is Nothing Then sAddr = rngFind.Address Do rngFind.Offset(, -4).Resize(, 14).Interior.ColorIndex = lColorNdx Set rngFind = .FindNext(rngFind) Loop While Not rngFind Is Nothing And rngFind.Address < sAddr End If 'Not rngFind Is Nothing End With 'Wks End If End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Woes
Catch the typo, and see added comments below...
In the sheet's code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row 4 And Target.Column = 11 Then _ Call Toggle_ChepOut_Shading(Target, Target.Offset(, -9).Value) End Sub In a standard module: Option Explicit Sub Toggle_ChepOut_Shading(Target As Range, LookupVal) Dim lColorNdx&, rngFind As Range, sAddr$ If IsEmpty(Target) Then lColorNdx = 0 Else lColorNdx = 6 If IsEmpty(Target) Or IsNumeric(Target.Value) Then Target.Offset(, -10).Resize(, 14).Interior.ColorIndex = lColorNdx With Sheets("Outbound").UsedRange Set rngFind = .Find(What:=LookupVal, _ After:=.Cells(1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not rngFind Is Nothing Then sAddr = rngFind.Address Do rngFind.Offset(, -4).Resize(, 14).Interior.ColorIndex = lColorNdx Set rngFind = .FindNext(rngFind) Loop While Not rngFind Is Nothing And rngFind.Address < sAddr End If 'Not rngFind Is Nothing End With 'Wks End If End Sub Note that this sub will find all occurances of LookupVal in case it's not unique. Otherwise, if you're happy with finding only the 1st instance then comment out the Do...Loop While! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Woes
Hi Mick,
Am Fri, 27 Sep 2013 23:11:10 -0700 (PDT) schrieb Living the Dream: If IsNumeric(Target.Value) Then With Target .Offset(, -10).Resize(, 14).Interior.ColorIndex = 6 .Offset(, -9).Select End With if your target is empty the value is 0. And 0 is also numeric. Therefore you get in both conditions the yellow background. Change the order of the conditions. Set IsEmpty as first condition: Private Sub Worksheet_Change(ByVal Target As Range) Dim myC As Integer If Target.Row < 5 Or Target.Column < 11 Then Exit Sub If IsEmpty(Target) Then myC = 0 ElseIf IsNumeric(Target) Then myC = 6 End If With Target .Offset(, -10).Resize(, 14).Interior.ColorIndex = myC lookfor = .Offset(, -9) End With If myC = 6 Then Plus_Chep_Out Else Minus_Chep_Out End If End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change Woes
Hi everyone, A massive thank you to all for your contribution.
Claus' code works really well. Please accept my appreciation for all of your time. You guys never fail to help put that little extra shine on the Duco of my workbooks. Thanks again. Cheers Mick. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More RGB Woes | Excel Programming | |||
For Each Next woes | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |