I am using the Merged Cell Line Height Macro, and it is a great macro.

However I am having a problem with it. It works great on an unprotecte
worksheet, but when I protect the worksheet it causes the macro to hal
with an error.

My sheet is a report that is protected to prevent changes and allo
data entery only in allowed fields. The allowed fields are unlocked
merged cells. When the text entered is too long for the merged cel
width, I would like to be able to use the macro to have users easil
adjust the height. (<Ctrl h is the assigned name). It works grea
until I protect the worksheet. Once I protect the sheet, pressing <Ctr
h in the merged cell runs the macro, but it errors at the line "
.MergeCells = False".

I have tried playing with the options for the protection, but can'
seem to find a way to have the page protected, and still have the macr
be able to run.

Any ideas greatly appreciated

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

I don't know what the macro is that you are talking about but if you
add the following code at the start of the macro it will remove
protection from the sheet

ActiveSheet.Unprotect

Then add to the bottom of the macro

ActiveSheet.Protect

If you have passwords in the protection the code will have to be
adapted accordingly

DavidP


On Sun, 16 Nov 2003 21:24:54 -0600, Mike M
wrote:


I am using the Merged Cell Line Height Macro, and it is a great macro.

However I am having a problem with it. It works great on an unprotected
worksheet, but when I protect the worksheet it causes the macro to halt
with an error.

My sheet is a report that is protected to prevent changes and allow
data entery only in allowed fields. The allowed fields are unlocked,
merged cells. When the text entered is too long for the merged cell
width, I would like to be able to use the macro to have users easily
adjust the height. (<Ctrl h is the assigned name). It works great
until I protect the worksheet. Once I protect the sheet, pressing <Ctrl
h in the merged cell runs the macro, but it errors at the line "
MergeCells = False".

I have tried playing with the options for the protection, but can't
seem to find a way to have the page protected, and still have the macro
be able to run.

Any ideas greatly appreciated?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Sorry, found the macro on this site ... http://tinyurl.com/vg1h

I'll see if I can work around by unprotecting the password protecte
sheet from within the macro, let the line height re-adjust, and the
have the sheet reprotect with the same password.

If when you look at the code (from the link) you can see anything els
that might work, it is appreciated.

Thanks,
Mik

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

I'm still having troubles getting this figured out. When I try to add
and run the ActiveWorksheet.Unprotect - with protection password added,
it gives me the error, you can't run this command on a protected page!
Well OK, I'm certainly doing something wrong, as that's the whole idea.
Maybe I'm missunderstanding how I need to fit it in. Can this Unprotect
line, and the protect line afterwards, be part of the same VBA macro,
or do I actually need to make it three seperate macro items to run -
Run unprotect, then run AutoFitMergedCellRowHeight() on as many lines
as required, then run protect, or should I not be able to get it all to
run under one macro command?

When I run debug to see where it gets stuck, it stops on the unprotect
line indicating 'invalid outside procedure'. Is this just telling me I
haven't got the unprotect command entered correctly?

Thanks for any assistance.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Just FYi and anyone interested I have sorted out geting this to work.

Built 3 Subs making 3 macros. Unprotect, Auto adjust merged cell lin
height, and Protect. Then in the Unprotect macro, call line adjust an
protect subs. Then running the unprotect macro does it all in one shot
Just could not get it to run all in one macro.

Thanks for input

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com