Running macro in protected sheet

[David]

I recorded a macro to do word wrap which works fine when
the sheets are unprotected. When sheets are protected, it
works in one sheet where the cells are protected and
driven by a formula importing data from another sheet. In
the unprotected cells where the data comes from the macro
won't work. It returns Run time error 1004 Unable to set
the HorizontalAlignment property of the Range class. What
do I need to change to make it work?
David

 

[firefytr]

You can put these lines in your code ...

ActiveSheet.Unprotect Password:="helloWorld"

'The meat of your macro here

ActiveSheeet.ProtectPassword:="helloWorld"

... It's fairly easy to refer to sheet protection via code

 

[David]

It works but returns Run time eror 424 and when I open VB
the last line
ActiveSheeet.Protect Password:="helloWorld"
is highlighted in yellow.
The other problem here is that the password is no longer
hidden from users - all they have to do is to edit the
macro. How do I hide macros?
I should add that I have wordwrap set for the column but
when text is pasted from Word when sheet protection is on
it needs to be wrapped - it does not do it automatically
even though I select special paste/text
David

 

[Don Guillett]

It's remotely possible that you have a different password, or no password.??

 

[David]

Hi Don
I locked the cells except for those I want users to insert
text. I proteced the sheet with a password. In taking the
advice of firefytr I simply replaced the password with
that of my own - unfortunately it locks the whole sheet
including the cell I didn't want locked. I reverted back
to the macro:-
Sub Wordwrap()
'
' Wordwrap Macro
' Macro recorded 01/10/2004
'
' Keyboard Shortcut: Ctrl+w
'
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
End Sub
With protection off this works, but with protection on I
get the VB Alert: Run time error 1004 Unable to set the
HorizontalAlignment property of the range class. However,
and this is the strange thing, the macro works when used
on another protected sheet in the same workbook. How can
that be?
David

 

[Don Guillett]

maybe merged cells??

 

[David]

Don
There are no merged cells. Another querk is that if I
paste using Ctrl+V it does not wrap and when I try to
delete it, it tells me that the cell is password
protected. It wasn't before I pasted from the clipboard.
If I paste using paste special and select text, it wraps
in the cell but overflows the cell whioch I why I wanted
the macro to wrap the text. Maybe there is another macro I
can write - but I still can't understand why it works on
one sheet and not another.
David