Code Stopped Working..sort of...

[GoKats78]

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]

It seems to work as advertised on 2003.

[mbarron]

2007 as well

[GoKats78]

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]

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]

Famous last words :rotlaugh:


Paul

That was my thought....

I tried the ?Application.EnableEvents <enter>..making sure it was true...no luck.

[mdmackillop]

Hi GoKats,
Please use the VBA button to format your code as shown
Regards
MD

[mdmackillop]

Can it be data entry? Void <> void
Try using Option Compare Text

[GoKats78]

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...

[SamT]

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]

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]

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...

[SamT]

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