Protect worksheets, but allow resizing of rows and columns 1

[makeitwork09]

Hello,

I am using Microsoft Excel 2007

I have obtained the following code to run a macro for a protected worksheet
that works as it should, I am only showing it here as a reference

Private Sub Workbook_Open()

   Dim wSheet As Worksheet

       For Each wSheet In Worksheets

           wSheet.Protect Password:="mypassword", UserInterFaceOnly:=True

       Next wSheet

End Sub

In regards to rows and columns in protecting the sheet, I have been looking
for a way to protect the contents of the cells, but allow the user to
resize columns and/or rows. I know when setting up the protection the user
can be given the rights to format the column and rows, but the only
attribute I want to allow is the resizing of columns and/or rows. Is that
possible?

Thanks

 

[osx99]

There should be an option in Excel 2007 that allows a user to 'Protect sheet'

With the options available select 'Format Columns' & 'Format Rows' this allows the cell contents to be locked but the sizing to be amended.

I'm using Excel 2010 but I know for a fact this can be done in Excel 2007 as I used to do it myself; apologies for not remembering which menu it's under. In Excel 2010 it's in Review/Protect Sheet

 

[SkipVought]

You can ONLY do in VBA, what you can do on the sheet. Find out how to PROTECT in accordance with your requirements, then turn on your macro recorder.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[osx99]

Sorry Skipvought, but if your protecting your OWN sheet you don't need VBA. Set the required protection options as I mentioned above. If you are using Excel 2003 then perhaps you're right but in Excel 2007 & 2010 you can protect the cell contents but allow for inserting of rows/columns and resizing rows/columns etc.

 

[SkipVought]

Sorry Skipvought, but if your protecting your OWN sheet you don't need VBA.

Of COURSE you don't. That's EXACTLY the advice I offered

But the user posted in forum707, and apparently desires protection via a macro.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[osx99]

I misread your post as 'You can only do this in VBA', which is not what you wrote. Sincere apologies!

 

[makeitwork09]

Thanks all - as Skip pointed out, I am only able to do in VBA what I can do in the sheet and as osx stated and I mentioned in my initial post, the selection only has the option to format column and format row. As a result, I cannot be as specfic as I would like in protecting the columns. That being the case, I am going to have to give the user the rights to format the column which potentially means they can accidentally clear the contents (I have to do a deeper dive into that). They cannot, however, insert or a delete a column because I did not check off those boxes.

Thanks again

 

[osx99]

makeitwork09 - sorry do destroy your thread...

Skip, I've appreciated many of your posts and you do know what your talking about but your becoming too long in tooth and negative with your posts! I appreciate people have to learn but there are many reasons for needing many obscure functions that require code rather than pivot tables and standard functions. The statement, "Find out how to PROTECT in accordance with your requirements, then turn on your macro recorder...." is not the best advice! For that reason I won't be back to Tek-Tips for a while....

 

[SkipVought]

osx99,

So what advice would you give? I certainly do not have all the answers or even the best answers.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

I don't think what you are asking is possible natively however there are ways around it in code if you really want to go there!

As you are already using userinterfaceonly as your protection argument, you can allow the selecting of a row / column and add an item to the right click menu - that could take you to a form which shows the current height / width of whatever is seelcted with an option to change it - code runs and changes it....not the most intuitive interface I'll grant you but it depends on what you are trying to achieve by letting users resize rows / columns...

osx99 - this is a technical forum for people who want to learn. All Skip was pointing out was that there are no hidden protection options in VBA - it's all right there on the menu and unfortunately there is no differentiation to allow for column / row resizing and applying any kind of format to the cells.
I did not see any negativity in either of his posts and I know form personal experience that Skip only ever has people's best interests at heart - yes there are situations that require obscure functions and code but people tend to jump into that before asking themselves whether what they are asking for could be done more simply by making some minor design changes.

The simplest advice is still often the best and the way to find out how to apply specific protection to a sheet is to record yourself doing it. If you can't do it manually then you cannot do it via code in this instance

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[makeitwork09]

For the record I got this working as I had wanted.

To the code in the original post I added

AllowFormattingColumns :=True

Thanks

 

[xlbo]

Well there you go - every time I do it I remind myself not to say it can't be done but I never learn.

thanks for posting back with the answer!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Out of interest - which version of excel are you using as I can see it is available in 2010 but don;t recall it being available in earlier versions (happy to be proved wrong though)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Geoff, here the template for 2007:
Sub Protect([Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly], [AllowFormattingCells], [AllowFormattingColumns], [AllowFormattingRows], [AllowInsertingColumns], [AllowInsertingRows], [AllowInsertingHyperlinks], [AllowDeletingColumns], [AllowDeletingRows], [AllowSorting], [AllowFiltering], [AllowUsingPivotTables])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DrSimon]

I've been sitting wondering whether to comment on all those posts and finally decided I couldn't let it rest. Sorry for using this post makeitwork09.

I was really sad to read that conversation ending in

For that reason I won't be back to Tek-Tips for a while....

I think that tek-tips is a fantastic forum where people give up time to provide their best attempts to help people out. Skip is one of the best exponents of this and he has provided so much help to me and others. Sure - sometimes the feeedback may not solve your problem and may seem a bit off hand, but what you get is FREE ADVICE and it is up to any recipient of that advice to consider it and test it.

It's wonderful when people answer questions in tek-tips and the other comments come back as almost instant peer review. In this case Skip's advice to use the Macro recorder was a sensible one. makeitwork09 clearly knows how to code in VBA but sought some advice about how to protect sheets - following the code produced by the macro recorder is nearly always a good entry into the investigation. Of course the advice he could have been provided was - 'Use Help' - searching for 'protect columns' will find 'AllowFormattingColumns'!

So in summary, please don't forsake tek-tips. If you need a laugh, try Making an Impression .

Simon

 

[xlbo]

Thanks PHV - anyone still working in xl2003 care to comment...?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question