Forum Discussion
Auto Uppercase issues!
Hi, i have a doc that i set the first 20 rows to change to Uppercase automatically. Now, when i protect the sheet, the formula doesn't work, but i cant see why. It works fine when the sheet is not protected.
The formula i have used is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 20 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub
I know i am missing something and "cant see the trees for the woods", but i am at a loss. I am hoping someone on the community will be able to help.
Thanks
Phil
Mr. Rochester
make a copy of your work then test it there.
always make a copy of your work.
I am not really sure if the syntax is correct, but you have to unprotect the sheet first before you can make changes, then protect it afterwards.
if you notice, I inserted two lines in your code.
HTH
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 20 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
ActiveSheet.UnProtect Password:="your password"
Target.Formula = UCase(Target.Formula)
ActiveSheet.Protect Password:="your password"
ErrHandler:
Application.EnableEvents = True
End Sub
6 Replies
Mr. Rochester
make a copy of your work then test it there.
always make a copy of your work.
I am not really sure if the syntax is correct, but you have to unprotect the sheet first before you can make changes, then protect it afterwards.
if you notice, I inserted two lines in your code.
HTH
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 20 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
ActiveSheet.UnProtect Password:="your password"
Target.Formula = UCase(Target.Formula)
ActiveSheet.Protect Password:="your password"
ErrHandler:
Application.EnableEvents = True
End SubHi Lorenzo
i have changed my sheet and now your code works spot on - thanks for taking time to look at this (back in August, i know!)
thanks again
Phil
- glad to help..
hello again, this works great for the Auto Upper, but i still need to be able to set Hyperlinks in cells, sort etc. These functions can be allowed when setting Protect Sheet, but with this function used, it doesn't - any ideas?
thanks again
Philip
- maybe post this query (re-titled) in another conversation, I am sure somebody here in the forum can help you with that...
and elaborate your query (if possible, in detail) so that they will easily get what you needed..
Hi, thanks for this - it works a treat! I knew i was missing something, perfect! Thanks
