Forum Discussion

PHILIP ROCHESTER's avatar
PHILIP ROCHESTER
Brass Contributor
Aug 13, 2018

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

excel
Formulas and Functions
  • Lorenzo Kim's avatar
    Lorenzo Kim
    Aug 14, 2018

    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

     

     

     

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Aug 14, 2018

    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

     

     

     

    • PHILIP ROCHESTER's avatar
      PHILIP ROCHESTER
      Brass Contributor
      Aug 14, 2018

      Hi, thanks for this - it works a treat! I knew i was missing something, perfect! Thanks

    • PHILIP ROCHESTER's avatar
      PHILIP ROCHESTER
      Brass Contributor
      Aug 14, 2018

      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

       

      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor
        Aug 14, 2018
        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..
    • PHILIP ROCHESTER's avatar
      PHILIP ROCHESTER
      Brass Contributor
      Sep 23, 2018

      Hi 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

Resources