Working with protected files...

[JDaywalt]

I have a file containing many sheet tabs and macro "buttons". The files are
protected & I want to protect the files so users have these basic options:

- Run macros by clicking the designated "buttons" or "drop-down boxes"
- Use available auto filters
- Enter data into designated cells
- Expand column width or row height if necessary to view all data

Here are my two questions:

1) Is there any way--other than adding protect/unprotect code into each
module--to set a protection status in Excel that allows users to run macros
that are assigned to the various buttons/drop-downs?

2) When choosing protection options via the Excel menu, I don't see an
option that allows users to manually 'resize' the column or row based on
viewing needs (you can insert/delete---which I don't want allow--but I don't
see a sizing option). Is there an expanded set of lock/unlock options that
can be built using VB?

 

[Mike H.]

When I go to protect a sheet, I have options for formatting columns and
formatting rows. I believe this will give them the ability to resize as
needed. And there are no advanced set of commands available only in VBA for
protection. HTH

 

[JDaywalt]

I am concerned about allowing the formatting of columns since I assume this
would also allow them to apply other types of formatting (i.e change colors,
fonts, etc.) Correct?

Also, did you have any ideas about the protection/unprotection---will I need
to do that on each individual macro?

 

[Mike H.]

There is a format cells option which does the things you're worried about
them doing. I think format columns is for width only. Don't check format
cells, do check format columns and rows and you will be all right.

As far as protection/unprotection when you run a macro, it depends. When I
run macros, if I am going to need to do something I just call a sub() that I
have named UnprotectSheets. This unprotects all sheets. Then I do my stuff
and then I call another macro, called ProtectAllSheets which puts the
password protection back on. If my macro doesn't need to change anything,
then I don't protect/unprotect. That is why I said depends. HTH...

 

[JDaywalt]

Thanks for the tip on the column/row format option---I did not realize that
it only applies width/height. I'll try that.

Actually, yes I was planning to create the subroutines for protect/unprotect
-- I was just trying to avoid having to insert them into all of my
modules---I have more than 30 that involve things like copy/paste/sort, etc.
which I don't want the user to perform manually, but would allow them to do
whenever they click the appropriate macro button. Was just hoping there was
a 'cleaner' approach, i.e. perhaps using the User Interface Only = True
option or something. Not too familiar with that option, but have read posts
in here that suggest it "goes away" after you close the file, which seems
rather pointless!!