Object variable or With block variable not set?

[Guest]

Hi all,

I am a newbie "programmer-wannabe" and have this code:

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet

If wSheet.ProtectContents = False Then
ProductCaution.Show
Protection.Show
Else

End If
End Sub

Don't know what this error means:

"Object variable or With block variable not set"

Can someone please help.

Thanks in advance and Happy Easter,
Emilio

 

[Jim Cone]

Emilio,

Dim wSheet as Worksheet does nothing until you tell Excel what sheet it is...
Set wSheet = ActiveSheet.

Your code should be in the sheet module, therefore "ActiveSheet" or "Me" also
makes a reference to the sheet.

It appears to me that you have more problems then your object variable not
being set. The following code is my interpretation of what you want...

'-----------------------------------
Private Sub Worksheet_Activate()

If Me.ProtectContents = False Then
MsgBox "Sheet is unprotected. ", vbExclamation, " Emilio Says..."
Application.Dialogs(xlDialogProtectDocument).Show
End If

End Sub
'------------------------------------

Regards,
Jim Cone
San Francisco, USA

 

[Guest]

Thank you very much.

I would like to take a step forward and automatically protect that specific
sheet,

I tried:


"If Me.ProtectContents = False Then
Me.ProtectContents = True"

but doesn't work.

Thanks ,
Emilio

 

[Jim Cone]

Emilio,

Me.Protect

More info in the VBA help file. Look up "Protect" or "Protect Method".

Jim Cone
San Francisco, USA

 

[Guest]

Sorry waqsn't thinking.

I got it. (Me!Protect)


Thanks for everything!

Emilio