Results 1 to 3 of 3

Thread: Combine 2 separate VBA

  1. #1
    Neophyte wikiriki's Avatar
    Join Date
    May 2021
    Posts
    1
    Articles
    0
    Excel Version
    MS 365

    Combine 2 separate VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, i am new to VBA and need some assistance.

    I wanted to lock cells for a certain range (below):
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim xRg As Range
        On Error Resume Next
        Set xRg = Intersect(Range("A1:A10,C1:C10,E1:E10"), Target)
        If xRg Is Nothing Then Exit Sub
        Target.Worksheet.Unprotect Password:="0"
        If xRg.Value <> mStr Then xRg.Locked = True
        Target.Worksheet.Protect Password:="0"
    End Sub
    And also make certain cells to force uppercase on them (below):
    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rngCell As Range
        If Intersect(Target, Range("B1:B1O,D1,F1")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
        For Each rngCell In Target.Cells
        rngCell = UCase(rngCell)
        Next
        Application.EnableEvents = True
    End Sub
    The codes are able to function separately but when i combined them together, there is an error and I learnt that Worksheet_Change cannot have duplicates. How do i combine them together in order to work? Kindly advise.
    Have attached the excel document for my codes. Thank you.
    Attached Files Attached Files

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    312
    Articles
    0
    Excel Version
    2007
    Place all the code inside one Private Sub Worksheet_Change(ByVal Target As Range)

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,854
    Articles
    0
    Excel Version
    O365
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    Dim cell As Range
        
        On Error GoTo wc_Exit
        Application.EnableEvents = False
        
        Me.Unprotect Password:="0"
        
        Set xRg = Intersect(Range("A1:A10,C1:C10,E1:E10"), Target)
        If Not xRg Is Nothing Then
        
            If xRg.Value <> mStr Then xRg.Locked = True
        ElseIf Not Intersect(Target, Range("B1:B1O,D1,F1")) Is Nothing Then
        
            For Each rngCell In Target.Cells
            
                cell.Value = UCase(cell.Value)
            Next
        End If
        
    wc_Exit:
        Me.Protect Password:="0"
        Application.EnableEvents = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •