Compile Error: Case Else Outside Case Select Case

[romeoshakes]

Hi,

Can someone help me with this. I can't figure out how to fix this error message. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Select Case Me.Range("J4")<o></o>
Case "Weekly", "Weekly"<o></o>
With Me<o></o>
Sheets("Test").Unprotect Password:="$test$"<o></o>
Me.Cells.Locked = True<o></o>
Me.Cells.FormulaHidden = False<o></o>
.Range("D10:D12, D15:D16, D40:D49").Locked = False<o></o>
Range("C3:E5,C10:C12,C22:C29,C32:C34,E32,E40:F49,I3:J4,L5:M5").Locked = False<o></o>
.Range("J4").Locked = False<o></o>
.Protect<o></o>
Select Case Me.Range("Q8")<o></o>
Case "Monthly", "Monthly"<o></o>
With Me<o></o>
Sheets("CPR - TAXServices").Unprotect Password:="$cpr$"<o></o>
Me.Cells.Locked = True<o></o>
Me.Cells.FormulaHidden = False<o></o>
Range("C22:C29").Locked = True<o></o>
Protect<o></o>
<o> </o>
Case Else<o></o>
With Me<o></o>
Sheets("Test").Unprotect Password:="$test$"<o></o>
Me.Cells.Locked = True<o></o>
Me.Cells.FormulaHidden = False<o></o>
.Range("D14:D16, D40:D49").Locked = False<o></o>
Range("C3:E5,C10:C12,C22:C29,C32:C34,E32,E40:F49,I3:J4,L5:M5").Locked = False<o></o>
.Range("J4").Locked = False<o></o>
.Protect<o></o>
End Select<o></o>
End If<o></o>
End Sub

 

[sunnyland]

Hello,
The problem is you are not using the Select Case and the With statement properly.

Simple select case open with select case and end with End Select, in between you have the choices starting with case

Select case x
case y
case m
case o
case else [not mandatory]
end select

If you want to nest a select case in a select case, same thing apply: a select case, choices of case and an end select. There will be a select case and end select for each nested selection and the outer select case will be close at the end.

I hope I am clear in my explanations , if not consult the select case help in vba.
For the With and End With statement, if it supposed to easy the typing a programmer do, in your case, I am not sure you should use it as the code is on the worksheet and the me is not necessary. I did not go into details with your code... The with has to be ended with a end with



Select case x
case y
Select case ...
case u
case v
case w
case else [not mandatory]
end select
case m
Select case ...
case i
case y
case w
case else [not mandatory]
end select

case else [not mandatory]
end select





Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
'selection based on address
'if cell changing J4
Case Range("J4").Address
'test now value of range("J4") using a nested select case opening with select case and end select
Select Case Target.Value
Case "Weekly", "weekly"
'if using With, you have to end it with an End With
' I am not sure you need the me as it will be the same without it
With Me
Sheets("Test").Unprotect Password:="$test$"
.Cells.Locked = True
.Cells.FormulaHidden = False
.Range("D10:D12, D15:D16, D40:D49").Locked = False
.Range("C3:E5,C10:C12,C22:C29,C32:C34,E32,E40:F49,I3:J4,L5:M5").Locked = False
.Range("J4").Locked = False
.Protect
End With

End Select
'if cell changing Q8
Case Me.Range("Q8").Address
'test now value of range("Q8") using a nested select case opening with select case and end select
Select Case Target.Value
Case "Monthly", "monthly"
Sheets("CPR - TAXServices").Unprotect Password:="$cpr$"
Cells.Locked = True
Cells.FormulaHidden = False
Range("C22:C29").Locked = True
Protect
End Select
'if cell changing is any other cell

Case Else
With Me
Sheets("Test").Unprotect Password:="$test$"
Me.Cells.Locked = True
Me.Cells.FormulaHidden = False
.Range("D14:D16, D40:D49").Locked = False
Range("C3:E5,C10:C12,C22:C29,C32:C34,E32,E40:F49,I3:J4,L5:M5").Locked = False
.Range("J4").Locked = False
.Protect
End With

End Select
End Sub

 

[romeoshakes]

Dear Francoise,

Thank you so much for the tip and it fixed my error! I have ways to go on vb.

My only problem now is when I select Variable, D10:D12 should be locked but they're not. I'm trying to prevent user from accidently entering data if this scenario is chosen. I tried adding the cells to lock but didn't work. There are 3 data validation list items to choose from in J5: Variable, Flat, or Both.

See below for revised code base on your edits:

Private Sub Worksheet_Change(ByVal Target As Range)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Select Case Target.Address<o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceName w:st="on">Case</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("J5").Address<o></o>
Select Case Target.Value<o></o>
Case "Variable", "Variable"<o></o>
With Me<o></o>
Sheets("ABC - Test").Unprotect Password:="$abc$"<o></o>
Me.Cells.Locked = True<o></o>
Me.Cells.FormulaHidden = False<o></o>
.Range("D10:D12, D40:D49").Locked = False<o></o>
<o></o>
.Range("c10:c12, d15:d16, c78:c100, d78:d100, e78:e100, f78:f100, g78:g100, h78:h100, i78:i100, j78:j100, k78:k100, l78:l100").Locked = False<o></o>
<o></o>
End With<o></o>
<o></o>
End Select<o></o>
<st1lace w:st="on"><st1:PlaceName w:st="on">Case</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("J5").Address<o></o>
Case Me.Range("J5").Address<o></o>
Select Case Target.Value<o></o>
Case "Flat", "Flat"<o></o>
With Me<o></o>
Sheets("ABC - Test").Unprotect Password:="$abc$"<o></o>
Me.Cells.Locked = True<o></o>
Me.Cells.FormulaHidden = False<o></o>
.Range("D14:D16, D40:D49").Locked = False<o></o>
.Range("C3:E5,C10:C12,C22:C29,C31:C34,a36, d39:F39,E31:E33,e40:e54, f40:f54, d40:d54,J3:J5,M5, M6, M10:M12, M23:M24").Locked = False<o></o>
.Range("A78:A200, b78:b100, c78:c100, d78:d100, e78:e100, f78:f100, g78:g100, h78:h100, i78:i100, j78:j100, k78:k100, l78:l100").Locked = False<o></o>
Sheets("ABC - Test").Protect Password:="$abc$"<o></o>
End With<o></o>
End Select<o></o>
<o></o>
<o></o>
Case Else<o></o>
With Me<o></o>
Sheets("ABC - Test").Unprotect Password:="$abc$"<o></o>
Me.Cells.Locked = True<o></o>
Me.Cells.FormulaHidden = False<o></o>
.Range("D10:D16, D40:D49").Locked = False<o></o>
Range("C3:E5,C10:C12,C22:C29,a36, C31:C34,e40:e54, f40:f54, d39:F39,E31:E33,d40:d54,I3:J4,L5:M5, M3, M10:M24").Locked = False<o></o>
.Range("J5").Locked = False<o></o>
.Range("A78:A200, b78:b100, c78:c100, d78:d100, e78:e100, f78:f100, g78:g100, h78:h100, i78:i100, j78:j100, k78:k100, l78:l100").Locked = False<o></o>
Sheets("ABC - Test").Protect Password:="$abc$"<o></o>
End With<o></o>
<o> </o>
End Select<o></o>
End Sub

 

[sunnyland]

Hello,

Last time I just corrected your select statement without really going into the content of your code, this time I went a bit further and try to understand what you are trying to achieve.

My deduction, [may be I am wrong] is that you have one sheet [ABC - Test], and you want some cells protected on this sheet depending of a data validation on a range J5 on a separate sheet. If I am wrong, just post again.
??????
What was not clear to me is where this validation list is: on a separate worksheet??? I assume it is on another sheet so my code will work if this is the case.
---------------------------------------------------
I was a bit confused: ie with the use of me in your code:
Sheets("ABC - Test").Unprotect Password:="$abc$"
Me.Cells.Locked = True
Me.Cells.FormulaHidden = False
as according to me you are unprotecting sheets("ABC - Test") but locking the cells on the other worksheet if any.
----------------------------------------------------


So the following solution is for 2 sheets :

one called "ABC - Test"
and the other one that has a data validation in J5.


On the worksheet with the data validation you will have the following code:
--------------------------------------------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo erreurs
'I declare 2 string variables to hold the worksheet name and password, so it is easy to change
'rather than changing in the code
Dim ProtectedSheet As String
Dim ProtectedPassword As String
ProtectedSheet = "ABC - Test"
ProtectedPassword = "$abc$"

'changes on worksheet where the list is and the value of j5 changes
If Target.Address = Range("J5").Address Then
Worksheets(ProtectedSheet).Select
'The following line is just that I used conditional formatting on the area A1:M200 to show which cells are locked or not
Application.ScreenUpdating = False

'depending on the value of J5 lock cells on sheet ("ABC - Test")
Select Case Target.Value

Case "Variable"
With Sheets(ProtectedSheet)
.Unprotect Password:=ProtectedPassword
.Cells.Locked = True
.Cells.FormulaHidden = False
.Range("c10:d12,D40:D49, d15:d16, c78:l100").Locked = False
.Protect Password:=ProtectedPassword
End With

Case "Flat"
With Sheets(ProtectedSheet)
.Unprotect Password:=ProtectedPassword
.Cells.Locked = True
.Cells.FormulaHidden = False
.Range("D14:D16, D40:D49").Locked = False
.Range("C3:E5,C10:C12,C22:C29,C31:C34,a36, d39:F39,E31:E33,d40:f54, J3:J5,M5, M6, M10:M12, M23:M24").Locked = False
.Range("A78:A200, b78:l100").Locked = False
.Protect Password:=ProtectedPassword
End With

Case "Both"
With Sheets(ProtectedSheet)
.Unprotect Password:=ProtectedPassword
.Cells.Locked = True
.Cells.FormulaHidden = False
.Range("D10:D16, D40:D49").Locked = False
.Range("C3:E5,C10:C12,C22:C29,a36, C31:C34,d40:f54, d39:F39,E31:E33,I3:J4,L5:M5, M3, M10:M24").Locked = False
.Range("J5").Locked = False
.Range("A78:A200, b78:b100, c78:l100").Locked = False
.Protect Password:=ProtectedPassword
End With
Case Else
'if people can just delete value in J5
With Sheets(ProtectedSheet)
.Unprotect Password:=ProtectedPassword
.Cells.Locked = True
.Cells.FormulaHidden = True
.Protect Password:=ProtectedPassword
End With
End Select
Application.ScreenUpdating = True

End If
Exit Sub
erreurs:
Application.ScreenUpdating = True
'may want to lock back all cells on the sheet ("ABC - Test") if error occurs
End Sub

------------------------------------------------------------------------

I create as well a module where I will put this code that will be used in conjuction of the conditional formatting to show locked row in one colour and unlocked one in another, just to check when you are trying to see if your code works the way it is supposed too.

code for module
-------------------------------------
Function islocked(rangi As Range) As Boolean
Dim isselected As Boolean
isselected = rangi.Locked
If isselected = True Then
islocked = True
Else
islocked = False
End If
End Function
-------------------------------------

Then I will select a1:m200 on the "ABC - Test" worksheet [check that the sheet is unprotected first] and will do a conditional formatting as follow, choosing formula as option:
=islocked(A1)=FALSE and selecting color of background cell as green for unlocked cells
and then adding another condition
=islocked(A1)=true and selecting red for locked cells.
-----------------------------------------
Note: I tried first to select the whole worksheet but the application just crashed as too many cells to refresh [conditional format]


To test my solution you can dowload the file:
http://www.4shared.com/file/224214495/1512530e/ProtectingCellbyMacro.html

or simply create a new workbook with 2 worksheets and 1 module
The code will go as explained above, and you will have to do manually the conditional formatting on the "ABC - Test", just to check that the correct cells are locked.

 

[romeoshakes]

Hi Francoise,

The codes work! Thanks so much. The file actually has only 1 tab in workbook but I can modify the codes to fix this. The lock cells are based on scenarios 'flat, variable, or both.' I also thought of unlocking the common cells so I don't have to unlock too many cells with with vb.

I have 2 more scenarios to lock (same worksheet) as a standalone based on new or existing and whether it meets 25K requirements in another cell so it can lock other cells. Yikes. I'll try this on my own. Thanks sooo soo much!

 

[romeoshakes]

Hi Francoise,

I feel really lame now since I keep getting error messages when I try to lock another set of cells based on 2 criterias and the cells are at 2 different locations.

If for example cell C3 is equal to "new" and cell C12 is less than 25,000 then lock all cells in range C22-C27, D22-D27, E22-E27. These 2 criterias are separate from the the ones in J5 and are stand alone. Cell C3 is data validation and it's all on one tab. Any advice?

JT

 

[sunnyland]

Hello,
=============================
Your question:
If for example cell C3 is equal to "new" and cell C12 is less than 25,000 then lock all cells in range C22-C27, D22-D27, E22-E27. These 2 criterias are separate from the the ones in J5 and are stand alone. Cell C3 is data validation and it's all on one tab. Any advice?
=============================

This cell C3 and C12 are they on the same worksheet as your previous code and if so what happens if c3 is new and c12>25000 ??? and what happens and if c3 is not new???

Once you use your validation in C3 what happened to all the other cells not in C:22 TO E27 are they locked or not??? as you say it is a stand alone.