Run Time Error 1004 Unable to set hidden property

[Lester Lee]

Hi,

I'm experiencing problems with a run time error 1004 unable to set
hidden property. The problem is that the worksheet that I'm using is
protected, and on my PC, I do not get this error. But when some other
people open the file on their PC, they get this error. I'm currently
using Excel 2002 (10.2614.3501) SP-1, I don't know what version the
other users have, but on an unprotected version of this file, they do
not have this problem.

Does this problem have something to do with versioning, or settings in
Excel?

Thanks,

Lester

 

[Dave Peterson]

xl2002 added a bunch more things you can do to a protected worksheet.

One of them is "format columns". xl2k (and before) didn't have this.

If you're using code, you may want to try to protect the worksheet there:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

(you could use the workbook_open even under ThisWorkbook, too.)

userinterfaceonly:=true allows your code to do more things than the users.

So I could do this:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.Range("a1,e1").EntireColumn.Hidden = True
End With
End Sub

 

[Lester]

Thanks for the explanation Dave.

I put the code in and it protects the sheets now, but it gives me the
error with when I'm unhiding rows, and I have a lot of code that hides
and unhides rows throughout the workbook. Its there a way to put the
protection in the code and allow it to use the hidden command without
me having put a protect and unprotect code with every hidden command?

Thanks,

Lester

 

[keepITcool]

Remarks on Protect Method in VBA help.:
If you apply the Protect method with the UserInterfaceOnly argument set
to True to a worksheet and then save the workbook, the entire worksheet
(not just the interface) will be fully protected when you reopen the
workbook. To re-enable the user interface protection after the workbook
is opened, you must again apply the Protect method with
UserInterfaceOnly set to True.
If changes wanted to be made to a protected worksheet, it is possible
to use the Protect method on a protected worksheet if the password is
supplied. Also, another method would be to unprotect the worksheet,
make the necessary changes, and then protect the worksheet again.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Lester wrote :

 

[Dave Peterson]

Just to add to Keepitcool's response.

By putting the code in the Auto_open sub in a general module, this code gets run
each time the workbook opens (well, if macros and startup macros are enabled).

So it should work without unprotecting, doing the work and reprotecting.