Select Case in a Worksheet_Change

[IndyBob]

I'm stumped as to why this isn't working. I had a jumble of nested IF/THEN/ELSE statements that worked, but was trying to clean up the code. The routine doesn't ever seem to run and I am not sure how/if I can use breakpoints in a Worksheet_Change routine. Any thoughts?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count <> 1 Then Exit Sub

If Target.Row = 8 And Target.Column = 1 Then
Select Case Target.Offset(0, 1).Value
Case Is = "Abbott"
GoTo Abbott

Case Is = "Benton"
GoTo Benton

Case Is = "Clifton"
GoTo Clifton

Case Is = "Duncan"
GoTo Duncan

Case Is = "Everett"
GoTo Everett

Case Is = "Fletcher"
GoTo Fletcher

Case Is = "Wexford"
GoTo Wexford

Case Is = "Yardley"
GoTo Yardley

Case Is = "Thorndyke"
GoTo Thorndyke

Case Is = "Thurston"
GoTo Thurston

End Select
End If

If Target.Row = 67 And Target.Column = 2 Then
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Select Case Target.Value
Case Is = "Ceramic Tile"
GoTo CeramicFloorPowderRoom

Case Is = "Hardwood"
GoTo HardwoodFloorPowderRoom

Case Is = ""
GoTo UnselectedFloorPowderRoom

End Select
End If


If Target.Row = 16 And Target.Column = 2 Then
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Range("B13").Value = Null
Range("C13").Value = Null
Select Case Target.Value
Case Is = "Avondale"
GoTo AvondaleKitchenCabinets

Case Is = "MidContinent"
GoTo MidContinentKitchenCabinets

Case Is = ""
GoTo UnselectedUpgradeKitchenCabinets

End Select
End If


If Target.Row = 13 And Target.Column = 2 Then
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Range("B16").Value = Null
Range("C16").Value = Null
Exit Sub
End If

GoTo GetOut

Abbott:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = True
Range("Guest_Bedroom").EntireRow.Hidden = False
Range("Guest_Bathroom").EntireRow.Hidden = False
Range("Den").EntireRow.Hidden = False
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Benton:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = False
Range("Guest_Bedroom").EntireRow.Hidden = True
Range("Guest_Bathroom").EntireRow.Hidden = True
Range("Den").EntireRow.Hidden = True
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Clifton:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = True
Range("Guest_Bedroom").EntireRow.Hidden = False
Range("Guest_Bathroom").EntireRow.Hidden = False
Range("Den").EntireRow.Hidden = True
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Duncan:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = False
Range("Guest_Bedroom").EntireRow.Hidden = True
Range("Guest_Bathroom").EntireRow.Hidden = True
Range("Den").EntireRow.Hidden = True
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Everett:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = True
Range("Guest_Bedroom").EntireRow.Hidden = False
Range("Guest_Bathroom").EntireRow.Hidden = False
Range("Den").EntireRow.Hidden = False
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Fletcher:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = True
Range("Guest_Bedroom").EntireRow.Hidden = False
Range("Guest_Bathroom").EntireRow.Hidden = False
Range("Den").EntireRow.Hidden = False
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Wexford:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = True
Range("Guest_Bedroom").EntireRow.Hidden = False
Range("Guest_Bathroom").EntireRow.Hidden = False
Range("Den").EntireRow.Hidden = False
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Yardley:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = False
Range("Guest_Bedroom").EntireRow.Hidden = True
Range("Guest_Bathroom").EntireRow.Hidden = True
Range("Den").EntireRow.Hidden = True
Range("Staircase").EntireRow.Hidden = True
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Thorndyke:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = False
Range("Guest_Bedroom").EntireRow.Hidden = False
Range("Guest_Bathroom").EntireRow.Hidden = False
Range("Den").EntireRow.Hidden = False
Range("Staircase").EntireRow.Hidden = False
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Thurston:
Application.EnableEvents = False
Application.ScreenUpdating = False
' Turn off screen updating. Makes things faster and more mysterious.
Sheets("Buyer Options").Unprotect Password:="phbocl"
Range("Powder_Room").EntireRow.Hidden = False
Range("Guest_Bedroom").EntireRow.Hidden = False
Range("Guest_Bathroom").EntireRow.Hidden = False
Range("Den").EntireRow.Hidden = False
Range("Staircase").EntireRow.Hidden = False
' Sheets("Buyer Options").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="phbocl"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

CeramicFloorPowderRoom:
Range("C67").Select
Selection.Value = Null
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$J$16:$J$23"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

HardwoodFloorPowderRoom:
Range("C67").Select
With Selection.Validation
.Delete
.Add Type:=None
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Selection.Value = "=IF(C$22=" & Chr(34) & Chr(34) _
& "," & Chr(34) & "Please select in Kitchen" _
& Chr(34) & ", IF(C$22=" & Chr(34) & Chr(34) _
& ", " & Chr(34) & Chr(34) & ", C$22))"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

UnselectedFloorPowderRoom:
Range("C67").Select
With Selection.Validation
.Delete
.Add Type:=None
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Selection.Value = ""
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

AvondaleKitchenCabinets:
Range("C16").Select
Selection.Value = Null
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$I$8:$I$11"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

MidContinentKitchenCabinets:
Range("C16").Select
With Selection.Validation
.Delete
.Add Type:=None
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Selection.Value = "Frost"
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

UnselectedUpgradeKitchenCabinets:
Range("C16").Select
With Selection.Validation
.Delete
.Add Type:=None
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Selection.Value = ""
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

GetOut:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[EricL]

I am not sure, but is it because the first if statement is true if the number is 8 so it ends sub?

If Target.Count <> 1 Then Exit Sub

You may just want if = 8 else exit sub.

I am learning VBA so I could be wrong on this.
Just hoping to give back. Good luck

Eric

 

[stevebausch]

Currently working on it for my evening brain-teaser....

Did it compile for you?

'Breakpoints' work wherever you set them, IF you mean clicking on the left border of your code, a brownish circle pops up, and that line of code is highlighted in brown.

You could also insert Stop statements.

Observations:

You could eliminate your Goto and label constructs by creating Sub routines in a Module. While in Project Explorer, right-click and select Insert-Module.

Name the Sub identical to the label you are replacing, and paste in the routine.

Unless you need to run through your labels to the next section of code, using Subs will help you avoid the problems of missing Exit Sub statements.

If, indeed, you have a section of code that is executed many times, then just create a Sub, name it something userul, and pasted that code into the Sub.
This message was edited by stevebausch on 2002-09-16 19:54

 

[IndyBob]

I kind of figured this one out by stumbling into how to use breakpoints with Worksheet_Change. Since there is no way to initiate a "run" command, I had to leave the breakpoint in at the beginning, then make a change, then step through the routine.

As it turns out, the Select Case stuff was working OK, but now I have found the real problem which is noted in my recent post about Worksheet_Change and Application.EnableEvents.

If you have any thoughts on that, I would be grateful.

 

[stevebausch]

Bob, could you post a link to the other thread?

I myself have used the If Target.Row=....Target.Column=.... construct myself. I have seen the Intersect method used, but in this case, it doesn't seem worth the effort.

Something like:

If Cells(8, 1).Address = Target.Address Then


works well. I don't know what's going on behind the scenes, but it's easy to comprehend, and takes up little space.


Thanks,
Steve
This message was edited by stevebausch on 2002-09-16 20:09

 

[IndyBob]

http://www.mrexcel.com/board/viewtopic.php?topic=22344&forum=2&0

I'm not familiar with Intersect, but either way, I believe the problem is with the EnableEvents stuff.