Excel 2003: Autofit in protected shared workbook

[garoe]

Hi,

I am stuck as to what to do with one of my workbooks - I need the sheet to be protected, and I need the workbook to be shared, but I also need the rows to autofit when information is entered in a cell.

I have attached my file which will hopefully explain what I am trying to achieve;
A person selects a code in column A - source for list is in column D
Through VLOOKUP, clarification will be entered in column B, based on the code selection in column A - it looks up information in column E

However, when selecting a code in A2, the copy in cell B2 is not always showing (or the height of the row is really tall) - and it's random which way it goes. So I applied a macro to autofit the height, which works great when the worksheet is not protected, or if I am able to add an unprotect code in the macro, but as I am sure people know, 'unprotect' does not work for shared workbooks.

Is there a way of getting the row height adjusted, within all these restrictions? Is there another macro I can apply that does not need the sheet to be unprotected?

Thanks,
Elin

[scott.s.fower]

when you protect the worksheet, make sure to tick "Format columns" this way the user can use the mouse to auto fit the column. if you want excel to automatically autofit the column you will have to use VBA,

i'm sending you a copy of your workbook with the macro and the "Format columns" ticked so you can see how it works, select D03 from your dropdown menu (because it is the shortest) then select B05 from the dropdown (because it is the longest) and column B will autofit automatically


the macro is written on the sheet4 code to run when any cell in the A column is changed
sample_autofit.xls

[garoe]

Hi Scott,

Thank you for getting back to me, although it didn't really answer my question unfortunately. I was aware that I needed a VBA to get the row (not column) to autofit, but my issue was that if the sheet is protected (which I want it to be) then the macro needs an unprotect command in the code, something that's not supported when the workbook is shared.

I am coming to realize that it appears I can't have all my requirements met? The requirements are as follows:
Sheet protected, with only unlocked cells, sort and autofilter enabled
Autofit of entire row
Workbook shared

I had hoped I could find a solution which would enable me to quite heavily protect the sheet, without allow "Format Rows" enabled, but as it appears I cannot have all my requirements met? And it seems I have to succumb to allowing "Format Rows" enabled as well?

Thanks,
Elin