Please check this code (ready to paste for you) - gives error "Invalid qualifier" - It is a protect/

[Gunnar Johansson]

Hi,
Please follow the XCodeName - it will cause the error "Invalid qualifier" in
Sub ProtectSheetRoutine. Anybody know why and how to fix it?

I try to optimize my code and to get rid of all unprotect & protect lines in
every "sheet event" like WorkSheet_Activate, WorkSheet_Change etc. I'm
nearly there with this code. It should be able to send the Sheet Identity
(CodeName) and Protect & Unprotect it with password.

(I always use CodeName in VBA, I have cleaned it and traslated the swedish
Automatic CodeName "Blad1" to "Worksheet1", hope that part look Ok - that
isn't the problem anyhow!)

SHEET CODE:

Private Sub Worksheet_Activate ()
'*******************************
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim XCodeName As String
XCodeName = ActiveSheet.CodeName ' Can send any other sheet CodeName as
well !

Call UnProtectXSheet (XCodeName) ' Sending it here

' [ I RUN SOME CODE]

Call ProtectXSheet (XCodeName) ' Should work the same

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


MODULE CODE:

Sub UnProtectXSheet (XCodeName As String)
'*********************************************
Dim sPass As String
Dim pStatus As String

'Place to edit /change "sPass" password !
sPass = "stupid"
pStatus = "unprotect"

Call ProtectSheetRoutine (sPass, pStatus, XCodeName)
End Sub


Sub ProtectSheetRoutine (sPass As String, pStatus As String, XCodeName As
String)
'*********************************************
If pStatus = "unprotect" Then
XCodeName.Unprotect Password:=sPass

Else
If XCodeName = "Worksheet1" Then
Worksheet1.Protect Password:=sPass, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If

If XCodeName = "Worksheet2" Then
Worksheet2.Protect Password:=sPass, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If

If XCodeName = "Worksheet3" Then
Worksheet3.Protect Password:=sPass, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If
' ........etc 12 more sheets

End If
Exit Sub


/Regards

 

[Don Guillett]

Do you have sheets named worksheet1?
Unless you have a lot of sheets, why not exclude the ones you don't want
instead? Something like:

for each ws in worksheets
if ws.name<> "joe" then
ws.protect etc
end if
next

 

[Gunnar Johansson]

Hi,
That will not help me with the error "Invalid qualifier". Some of the sheets
have different types of protection, thererfore I identify each one of them,
byt yes, I can shorten It a bit by grouping them together.

Still, why error "Invalid qualifier" ?

/regards

Do you have sheets named worksheet1?
Unless you have a lot of sheets, why not exclude the ones you don't want
instead? Something like:

for each ws in worksheets
if ws.name<> "joe" then
ws.protect etc
end if
next

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi,
Please follow the XCodeName - it will cause the error "Invalid qualifier" in
Sub ProtectSheetRoutine. Anybody know why and how to fix it?

I try to optimize my code and to get rid of all unprotect & protect lines in
every "sheet event" like WorkSheet_Activate, WorkSheet_Change etc. I'm
nearly there with this code. It should be able to send the Sheet Identity
(CodeName) and Protect & Unprotect it with password.

(I always use CodeName in VBA, I have cleaned it and traslated the
swedish
Automatic CodeName "Blad1" to "Worksheet1", hope that part look Ok - that
isn't the problem anyhow!)

SHEET CODE:

Private Sub Worksheet_Activate ()
'*******************************
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim XCodeName As String
XCodeName = ActiveSheet.CodeName ' Can send any other sheet CodeName as
well !

Call UnProtectXSheet (XCodeName) ' Sending it here

' [ I RUN SOME CODE]

Call ProtectXSheet (XCodeName) ' Should work the same

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


MODULE CODE:

Sub UnProtectXSheet (XCodeName As String)
'*********************************************
Dim sPass As String
Dim pStatus As String

'Place to edit /change "sPass" password !
sPass = "stupid"
pStatus = "unprotect"

Call ProtectSheetRoutine (sPass, pStatus, XCodeName)
End Sub


Sub ProtectSheetRoutine (sPass As String, pStatus As String, XCodeName As
String)
'*********************************************
If pStatus = "unprotect" Then
XCodeName.Unprotect Password:=sPass

Else
If XCodeName = "Worksheet1" Then
Worksheet1.Protect Password:=sPass, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If

If XCodeName = "Worksheet2" Then
Worksheet2.Protect Password:=sPass, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If

If XCodeName = "Worksheet3" Then
Worksheet3.Protect Password:=sPass, DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If
' ........etc 12 more sheets

End If
Exit Sub


/Regards