| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
| Pivot Table - Object variable or with block variable not set? | Excel Programming | |||
| Error 91 - Object variable with block variable not set | Excel Programming | |||
Hybrid Mode
