Need help with adding to a current sub to have certian cells change to uppercase

dwb108

New Member
Joined
Mar 20, 2013
Messages
2
Hello all,

I'm somewhat new at this VBA stuff so any help would be great. Here's what I'm trying to do. Have a cell be locked when something is entered into it so the user couldn't change the data once entered. I was able to get a code that does this and even gives you a pop up box prompting you for a password if you want to change it.
But what I am trying to do now is to have certian cells change to uppercase when data is entered into it. (such as J:2 to J:30) but also keep the current sub for locking the data. Any help would be great, Thanks in advance. Here is the current sub I'm using now:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range
Set changed = Intersect(Target, Range("A:A , B:B , C:C , D:D , E:E , F:F , G:G , H:H , I:I , J:J , K:K , L:L , M:M , N:N , O:O , P:P , Q:Q , R:R"))
 
If Not changed Is Nothing Then
    If TargetLocked <> True Then
       
       ActiveSheet.Unprotect ("ya52vrHq")
       Target.Locked = True
       ActiveSheet.Protect "ya52vrHq", DrawingObjects:=True, Contents:=True, Scenarios:=True
       
    Else
        
    End If
    End If
        
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    
    Dim Pword As String
    Dim changed As Range
    
    Set changed = Intersect(Target, Range("A:A , B:B , C:C , D:D , E:E , F:F , G:G , H:H , I:I , J:J , K:K , L:L , M:M , N:N , O:O , P:P , Q:Q , R:R"))
    
    If Not changed Is Nothing Then
    
        If Target.Locked = True Then
            
            If MsgBox("Needs Correction?", vbYesNo, "Warning") = vbYes Then
                Pword = InputBox("Enter Password", "Warning")
                On Error GoTo Getout
                ActiveSheet.Unprotect Pword
            Target.Locked = False
                
                ActiveSheet.Protect Pword
            End If
            
        End If
        
    End If
    Exit Sub
Getout: MsgBox "Wrong Password See The Boss to Unlock", vbCritical, "Password Error"
    
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,215,457
Messages
6,124,941
Members
449,197
Latest member
k_bs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top