Object variable or With block variable not set?
 

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

Object variable or With block variable not set?
 

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



"Wind54Surfer" wrote in message
...
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

Object variable or With block variable not set?
 

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" wrote:

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



"Wind54Surfer" wrote in message
...
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

Object variable or With block variable not set?
 

Emilio,

Me.Protect

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

Jim Cone
San Francisco, USA



"Wind54Surfer" wrote in message
...
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" wrote:
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

Object variable or With block variable not set?
 

Sorry waqsn't thinking.

I got it. (Me!Protect)


Thanks for everything!

Emilio

"Wind54Surfer" wrote:

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" wrote:

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



"Wind54Surfer" wrote in message
...
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