VBA code to enable format cells

[simselk]

Hi Guys

I've been struggling to solve this problem for a few hours now. I have an overall password ("XYZ") protecting the overall sheet, and then a 2nd password for Range("rngB_O") - password "ABC". I need this code to not only unlock the column ( Selection.Locked = False), but to allow users to format cells in a cell in this range.
The code: Selection.EnableSelection = False (to prevent format cells) & Selection.EnableSelection = True (to enable format cells) is not working for me.

Any thoughts please?


'**************************************************************************
' Macros to unhide or hide columns for Management Company
'**************************************************************************
Sub pwdManCoy()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Sheet1.Unprotect Password:="XYZ"

Dim passMan As String
passMan = InputBox("Bitte geben Sie Ihr Passwort")

If passMan <> "ABC" Then
MsgBox "Bitte starten Sie das korrekte Passwort für Verwaltungsgesellschaft", vbOKOnly, "Falsches Passwortfür für die Verwaltungsgesellschaft"

' Protecting columns if password entered in WRONG!!!
Sheet1.Range("rngB_O").Select
Selection.Locked = True
Selection.FormulaHidden = True


Else

' Unprotecting columns if password entered is correct
Sheet1.Range("rngB_O").Select
Selection.Locked = False
Selection.FormulaHidden = False

End If

Sheet1.Protect Password:="Leipzig"

Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub

 

[Col Delane]

If you're trying to limit the area to which the user can access, you could try

Sheet1.ScrollArea = Range("rngB_O")

Also note the following:
1. As I understand how Excel protection hierarchy works, the code line:

Sheet1.Unprotect Password:="XYZ"

unprotects the entire sheet before you ask the user for the 2nd password. As a result, at that point the locked status of individual cells will be irrelevant (as this only becomes effective when the sheet protection is turned on) so the code in your IF...Then doesn't really have any practical effect, even if the user enters the correct password (i.e. "ABC").

2. If the password is wrong, why don't you just re-protect the sheet with:

Sheet1.Protect Password:="Leipzig"

instead of the following (as these properties had not been changed at that point):

' Protecting columns if password entered in WRONG!!!
 Sheet1.Range("rngB_O").Select
 Selection.Locked = True
 Selection.FormulaHidden = True

3. Why is the password to unprotect the sheet ("XYZ") different from that used to re-protect it ("Leipzig")? I would think that this would prevent the macro from unprotecting the sheet next time. (Perhaps that's the underlying problem that is causing you grief?)

HTH