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?

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" wrote:

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?

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." wrote:

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" wrote:

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?

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" wrote:

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." wrote:

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" wrote:

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?

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!!

"Mike H." wrote:

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" wrote:

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." wrote:

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" wrote:

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?