Confused, again! Object variable not set?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

davegb - 23 Jan 2006 23:14 GMT

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.

Reply to this Message

Jim Thomlinson - 23 Jan 2006 23:30 GMT

Where is wksMacRec set. Is the sheet named that or is it set somewhere else???

set wksMacRec = sheets("Sheet1") '???

Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 16 lines]
> workbook. So whatzzup?
> Thanks as always.

Reply to this Message

JakeyC - 23 Jan 2006 23:31 GMT

I think it may be because PWORD needs to either be "PWORD" or before
using it, state PWORD = "mYPassWOrD"

Reply to this Message

Chip Pearson - 23 Jan 2006 23:32 GMT

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")

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

> This code is on the worksheet module. It hides the commandbar
> that is
[quoted text clipped - 22 lines]
> workbook. So whatzzup?
> Thanks as always.

Reply to this Message

GB - 24 Jan 2006 12:57 GMT

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.

Reply to this Message

davegb - 25 Jan 2006 15:25 GMT

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 to this Message