View Full Version : Code Stopped Working..sort of...
GoKats78
03-15-2010, 09:14 AM
I have this code on a speadsheet used by multiple people on many different machines...it works on some but not all machines...I threw a message box in one of the conditions and test it on one of the machine where the code does not work..the message box fired. I am so confused..
Oh, it worked on everyone's machine a few weeks back. I did make changes to some other code that should not affect this bit of code...
Private Sub worksheet_change(ByVal target As Range)
If target.Count > 1 Then Exit Sub
If target.Column = 8 Then
Select Case target.Value
Case "Mass Production": target.Offset(0, -6).Interior.ColorIndex = 3: target.Offset(0, -5).Interior.ColorIndex = 3
Case "Warranty": target.Offset(0, -6).Interior.ColorIndex = 45: target.Offset(0, -5).Interior.ColorIndex = 45
Case "New Model": target.Offset(0, -6).Interior.ColorIndex = 38: target.Offset(0, -5).Interior.ColorIndex = 38
Case "Information Only": target.Offset(0, -6).Interior.ColorIndex = 5: target.Offset(0, -6).Font.ColorIndex = 2: _
target.Offset(0, -5).Interior.ColorIndex = 5: target.Offset(0, -5).Font.ColorIndex = 2
Case "Void": target.Offset(0, -6).Interior.ColorIndex = 48: target.Offset(0, -5).Interior.ColorIndex = 48
Case "DTR": target.Offset(0, -6).Interior.ColorIndex = 3: target.Offset(0, -5).Interior.ColorIndex = 3
Case "Customer": target.Offset(0, -6).Interior.ColorIndex = 6: target.Offset(0, -5).Interior.ColorIndex = 6
Case "Internal": target.Offset(0, -6).Interior.ColorIndex = 30: target.Offset(0, -6).Font.ColorIndex = 2: _
target.Offset(0, -5).Interior.ColorIndex = 30: target.Offset(0, -5).Font.ColorIndex = 2
Case "Supplier Reported": target.Offset(0, -6).Interior.ColorIndex = 21: target.Offset(0, -6).Font.ColorIndex = 2: _
target.Offset(0, -5).Interior.ColorIndex = 21: target.Offset(0, -5).Font.ColorIndex = 2
Case Else: target.Offset(0, -6).Interior.ColorIndex = xlNone: target.Offset(0, -6).Font.ColorIndex = xlAutomatic
End Select
End If
lucas
03-15-2010, 09:48 AM
It seems to work as advertised on 2003.
mbarron
03-15-2010, 10:02 AM
2007 as well
GoKats78
03-15-2010, 10:38 AM
This is in Cell E4. I have similar statements in E5-E8
=IF(ISERROR(VALUE(RankSevSort(H$2,D4))),0,VALUE(RankSevSort(H$2,D4)))
Below is the code I added last week that, apparently, is causing the problem. When I remove the "If" statements from E4-E8 the code controlling the color of B2 works...
Function RankSevSort(sType As Range, SortSev As Range) As String
Select Case (sType)
Case "Mass Production", "Warranty", "DTR", "Customer", "Internal"
If SortSev = "A" Then
RankSevSort = "100"
ElseIf SortSev = "B" Then
RankSevSort = 25
ElseIf SortSev = "C" Then
RankSevSort = 10
ElseIf SortSev = "N/A" Then
RankSevSort = 0
End If
Case "Information Only", "New Model", "Supplier Reported", "Void"
If SortSev = "A" Then
RankSevSort = 0
ElseIf SortSev = "B" Then
RankSevSort = 0
ElseIf SortSev = "C" Then
RankSevSort = 0
ElseIf SortSev = "N/A" Then
RankSevSort = ""
End If
Case Else
RankSevSort = ""
End Select
End Function
I just tested in in Excel 07...does not work...
I was leaning towards being a Service Pack issue...I have Excel 03 with SP3, I tested in on a machine with SP2 and it did not work...but I thought Excel 07 SHOULD have worked...
Paul_Hossler
03-15-2010, 10:39 AM
I did make changes to some other code that should not affect this bit of code...
Famous last words :rotlaugh:
One thing you might check since there are apparently machine dependent differences, is to make sure that Events are enabled.
I turn off events and sometimes when things break, they're left off
In the VBE's Immediate Window, type (you need the question mark)
?Application.EnableEvents <enter>
and make sure that it's True
Paul
GoKats78
03-15-2010, 11:03 AM
Famous last words :rotlaugh:
Paul
That was my thought....
I tried the ?Application.EnableEvents <enter>..making sure it was true...no luck.
mdmackillop
03-15-2010, 11:53 AM
Hi GoKats,
Please use the VBA button to format your code as shown
Regards
MD
mdmackillop
03-15-2010, 12:12 PM
Can it be data entry? Void <> void
Try using Option Compare Text
GoKats78
03-16-2010, 03:02 AM
The choices are in a dropdown...not possible to be data entry.
There is something about the function and the code that are in conflict...
Just checking some assumptions about your UDF RankSevSort...
$H$2, ($H because the relative column from E doesn't change,) contains the string checked in the
Select Case(sType)
Is that the proper syntax for Select Case? I thought it was
Select Case sType.
I would also try
Select Case sType.Value
Also, I see you setting the value of RankSevSort to a string, ("100",) a number, (25, 10, or 0,) or an empty string, (""). Since RankSevSort is Range, you might try using
RankSevSort.Value = ??? explicitly.
Finally. Try minimizing your cell formula and see what happens
=RankSevSort($H$2,$D4)
vice
=IF(ISERROR(VALUE(RankSevSort(H$2,D4))),0,VALUE(RankSevSort(H$2,D4)))
Value converts a Text String enclosed in quotes to a number. Converting an empty string, Quotes only, will give an error in the cell.
The only possible error involving your UDF will be if none of the nine checked-for inputs are there, which will result in an empty string being returned to the VALUE Worksheet function.
I, personally, would also change the reference to D4 to read $D4 simply because the more explicitly you write code the less chance of errors in understanding and operation.
Hope this helps.
Edit: YOur Cell Formula says that if there is an error from the UDF, make the cells value = 0. In all cases except "Mass Production", "Warranty", "DTR", "Customer", "Internal", you set the cells value to 0 or empty. doesn't this do the same?
=RankSevSort($H$2,$D4)
Function RankSevSort(sType As Range, SortSev As Range) As String
Select Case (sType)
Case "Mass Production", "Warranty", "DTR", "Customer", "Internal"
If SortSev = "A" Then RankSevSort = "100"
ElseIf SortSev = "B" Then RankSevSort = 25
ElseIf SortSev = "C" Then RankSevSort = 10
Else RankSevSort = 0
End If
End Select
End Function
Paul_Hossler
03-16-2010, 02:35 PM
If it were mine, I'd put the worksheet formula logic inside my VBA and just call it:
=RankSevSort(H$2,D4)
Function RankSevSort(sType As Range, SortSev As Range) As Variant
Select Case (sType)
Case "Mass Production", "Warranty", "DTR", "Customer", "Internal"
Select Case SortSev
Case "A"
RankSevSort = 100
Case "B"
RankSevSort = 25
Case "C"
RankSevSort = 10
Case "N/A"
RankSevSort = vbNullString
Case Else
MsgBox "!!!! Fell thru (1)"
End Select
Case "Information Only", "New Model", "Supplier Reported", "Void"
Select Case SortSev
Case "A", "B", "C"
RankSevSort = 0
Case "N/A"
RankSevSort = vbNullString
Case Else
MsgBox "!!!! Fell thru (2)"
End Select
Case Else
RankSevSort = Empty
End Select
End Function
Paul
GoKats78
03-17-2010, 03:57 AM
Thanks for all the help..I have tried all these..it did hlep clean up the code a bit...BUT none of them helped with my initial problem of the function (RankSevSort) and code in my first post to change to color of the cells based on the choice made in my dropdown (e.g. Mass Production selected in the dropdown in Cell H2 changing C2 to red)
The "color code" works on some machines but not all...
Looked at both pieces of your code this time.
If I understand correctly :dunno You first insert a value into $H$2. This causes a change in the values in Range(E4:E8). These changes trigger the WorksheetChange event which you use to color B2:C2
Private Sub worksheet_change(ByVal target As Range)
If target.Count > 1 Then Exit Sub
If target.Column = 8 Then
Which immediately exits if any cell in the E column happened to change when H2 did.
Try tieing your Range(B2:C2) coloring code to the Change Event of the Listbox that is in $H$2.
Try this. It is not tested.
Private Sub ListBox1_Change()
With ActiveSheet.Range("B2:C2")
Select Case ListBox1.Value
Case "Mass Production": .Interior.ColorIndex = 3
Case "Warranty": .Interior.ColorIndex = 45
Case "New Model": .Interior.ColorIndex = 38
Case "Information Only": .Interior.ColorIndex = 5: _
.Font.ColorIndex = 2
Case "Void": .Interior.ColorIndex = 48
Case "DTR": .Interior.ColorIndex = 3
Case "Customer": .Interior.ColorIndex = 6
Case "Internal": .Interior.ColorIndex = 30: _
.Font.ColorIndex = 2
Case "Supplier Reported": .Interior.ColorIndex = 21: _
.Font.ColorIndex = 2
Case Else: .Interior.ColorIndex = xlNone: _
.Font.ColorIndex = xlAutomatic
End Select
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.