Object variable or With block variable not set?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Emilio,

Me.Protect

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

Jim Cone
San Francisco, USA
 
Back
Top