Select Case in a Worksheet_Change

I

IndyBob

New Member
Joined
Sep 15, 2002
Messages
26
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

tlc53
  • Solved
Unprotect to run VBA - Why isn't it working??
Replies
1
Views
400
tlc53
tlc53
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
386
kevin9999
K
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
141
Akuini
Akuini
S
  • Question
VBA Help Please
2
Replies
16
Views
905
Peter_SSs
Peter_SSs
W
  • Solved
Macro slowing down excessivly
Replies
4
Views
379
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,636
Messages
6,120,669
Members
448,977
Latest member
moonlight6

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top