UserInterfaceOnly

[Karen53]

Hi,

I've been trying to put this in my workbook but the userinterfaceonly is not
picking up. Is there something else I need to do?

Private Sub Workbook_Open()

Dim wkSheet As Worksheet

For Each wkSheet In Worksheets
wkSheet.Protect Password:="pw", _
userinterfaceonly:=True, _
AllowFormattingCells:=True, _
AllowFiltering:=True
Next wkSheet

End Sub

 

[Ron de Bruin]

See
http://www.contextures.com/xlautofilter03.html#Protect

 

[Karen53]

Hi Ron,

Thank you so much for the information! I needed it!

I haven't gotten there yet, though. If you look at my code the
userinterfaceonly:=True is in lower case. I type them in lower case so when
it's correct and accepted Excel adds the case formatting. Excel is not doing
so with the userinterfaceonly. I see no typing errors. Shouldn't some of
the letters be capitalized like the others?

--
Thanks for your help.
Karen53

See
http://www.contextures.com/xlautofilter03.html#Protect

 

[Ron de Bruin]

-No need for userinterfaceonly in the newer Excel versions

Record a macro when you do your steps manual and look at the code in 2002 and up

You need the code on Debra's site in 97-2000

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron,

Thank you so much for the information! I needed it!

I haven't gotten there yet, though. If you look at my code the
userinterfaceonly:=True is in lower case. I type them in lower case so when
it's correct and accepted Excel adds the case formatting. Excel is not doing
so with the userinterfaceonly. I see no typing errors. Shouldn't some of
the letters be capitalized like the others?

 

[Karen53]

Ron,

Since I'm using 2003 I don't need to set it? It's that way automatically?
Great!

Thanks!

 

[Karen53]

Hi ron,

I'm confused by something. I've recorded my macro with a password yet the
password does not show up in the code. Don't I need the password? How do I
reset if if I unprotect the sheet? I'm in 2003.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFiltering:=True

 

[Chip Pearson]

I've recorded my macro with a password yet the
password does not show up in the code.

I would assume that the macro recorder doesn't insert the password into
recorded code as a security measure. If you are using a password, you may
not want it the code for anyone to see.

You can modify the recorded code to include a password and the UI flag:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password:="abc", userinterfaceonly:=True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Hi ron,

I'm confused by something. I've recorded my macro with a password yet the
password does not show up in the code. Don't I need the password? How do
I
reset if if I unprotect the sheet? I'm in 2003.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
_
, AllowFormattingCells:=True, AllowFiltering:=True

 

[Karen53]

Thank you, Chip! The password did work.

Unfortunately, I need to add and delete rows which requires my removing and
adding the protection and password. the same with the workbook protection as
I need to add, move and delete worksheets. I do have my vba code password
protected as well.

I am concerned that my add worksheet routine takes so long. I have
screenupdating turned off. The process inserts quite a few links between the
main page and the newly created sheet as well as placing the new sheet in
order by unit number. Is it normal for it to take quite a while or do I have
a problem? I would appreciate any suggestions you may have.

 

[Cheers]

Hi,
Yes. You can Protect & Unprotect Any Excel Sheet via VBA provided you have
set the correct password in the VBA. Users who use your Macro will not have a
chance to edit your protected excel file if you are able to control the VBA
well. User will not be asked to input the password.

I have written a VBA program. All you need is to input <Filename>, <Path>
and <Password> for the file via VBA. You may edit anything in the hidden mode
via VBA, then save it wihout user have a chance to view what is going on.

Email to:
(e-mail address removed)
for more details.

Cheers