Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused, again! Object variable not set?
This code is on the worksheet module. It hides the commandbar that is
unique to this sheet when the sheet is deactivated and erases the commandbar name from the worksheet so that XL doesn't try to hide the toolbar when the workbook is closed. Private Sub Worksheet_deActivate() Application.CommandBars("Abuse").Visible = False With wksMacRec .Unprotect Password:=PWORD<----ERROR .Range("E2").Value = "" .Protect Password:=PWORD End With 'ActiveWorkbook.Worksheets("Macro Records").Range("E2").Value = "" End Sub I'm getting an "object variable or with block variable not set" error. The password in set as a public constant in another module in this same workbook. So whatzzup? Thanks as always. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused, again! Object variable not set?
Where is wksMacRec set. Is the sheet named that or is it set somewhere else???
set wksMacRec = sheets("Sheet1") '??? -- HTH... Jim Thomlinson "davegb" wrote: This code is on the worksheet module. It hides the commandbar that is unique to this sheet when the sheet is deactivated and erases the commandbar name from the worksheet so that XL doesn't try to hide the toolbar when the workbook is closed. Private Sub Worksheet_deActivate() Application.CommandBars("Abuse").Visible = False With wksMacRec .Unprotect Password:=PWORD<----ERROR .Range("E2").Value = "" .Protect Password:=PWORD End With 'ActiveWorkbook.Worksheets("Macro Records").Range("E2").Value = "" End Sub I'm getting an "object variable or with block variable not set" error. The password in set as a public constant in another module in this same workbook. So whatzzup? Thanks as always. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused, again! Object variable not set?
I think it may be because PWORD needs to either be "PWORD" or before
using it, state PWORD = "mYPassWOrD" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused, again! Object variable not set?
As far as I can see in your code, you've never set the wksMacRec
variable. You need some code like Dim wksMacRec As Worksheet Set wksMacRec = Worksheets("Sheet1") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "davegb" wrote in message ups.com... This code is on the worksheet module. It hides the commandbar that is unique to this sheet when the sheet is deactivated and erases the commandbar name from the worksheet so that XL doesn't try to hide the toolbar when the workbook is closed. Private Sub Worksheet_deActivate() Application.CommandBars("Abuse").Visible = False With wksMacRec .Unprotect Password:=PWORD<----ERROR .Range("E2").Value = "" .Protect Password:=PWORD End With 'ActiveWorkbook.Worksheets("Macro Records").Range("E2").Value = "" End Sub I'm getting an "object variable or with block variable not set" error. The password in set as a public constant in another module in this same workbook. So whatzzup? Thanks as always. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused, again! Object variable not set?
Testing methodoligy would suggest try commenting out the first affected line.
If the next line gives the same error, then it would suggest the cause that others here have indicated. If it is a different error (as obviously to perform the requested function the protection must be turned off to do the next step) then there is definetly a problem with the line indicated and it is other than that suggested by others here. "davegb" wrote: This code is on the worksheet module. It hides the commandbar that is unique to this sheet when the sheet is deactivated and erases the commandbar name from the worksheet so that XL doesn't try to hide the toolbar when the workbook is closed. Private Sub Worksheet_deActivate() Application.CommandBars("Abuse").Visible = False With wksMacRec .Unprotect Password:=PWORD<----ERROR .Range("E2").Value = "" .Protect Password:=PWORD End With 'ActiveWorkbook.Worksheets("Macro Records").Range("E2").Value = "" End Sub I'm getting an "object variable or with block variable not set" error. The password in set as a public constant in another module in this same workbook. So whatzzup? Thanks as always. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused, again! Object variable not set?
Thanks for all your help. The problem was that wksMacRec was not set in
this macro. I forgot that a "set" has to be done in each separate macro and can't be done publicly like setting a constant. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Run-time Error'91: Object variable or With block variable not set | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Confused with variable | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |