+ Reply to Thread
Results 1 to 2 of 2

Combine 2 Macros Help Required

  1. #1
    Forum Contributor
    Join Date
    11-11-2010
    Location
    England
    MS-Off Ver
    Excel 2019
    Posts
    203

    Combine 2 Macros Help Required

    Combine 2 macros together - can someone help combine these together

    THIS DOES NOT ALLOW DELETE

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CountAfter As Long
    If Target.Count > 1 Then
    CountAfter = Application.CountA(Target)
    Application.EnableEvents = False
    If CountAfter = 0 Then
    Application.Undo
    MsgBox "Clearing of cells is not permitted.", 16, "ERROR"
    End If
    Application.EnableEvents = True
    Exit Sub
    End If
    If IsEmpty(Target.Value) Then
    Application.EnableEvents = False
    Application.Undo
    MsgBox "text cannot be deleted.", 16, "ERROR"
    Application.EnableEvents = True
    End If
    End Sub

    ............................................................................

    THIS JUST PUTS THE TEXT BACK BUT ALLOWS DELETE

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sel, sv
    Dim rng As Range, cell As Range, plan As Range
    Dim rng2 As Range, cell2 As Range, WO As Range
    Set sel = Selection
    sv = sel.Value
    Application.EnableEvents = False
    On Error GoTo e
    Application.Undo
    Set rng = Range([A1], [IV1].End(xlToLeft))
    For Each cell In rng
    If cell = "Plan" Then
    If plan Is Nothing Then
    Set plan = Intersect(cell.EntireColumn, ActiveSheet.UsedRange)
    Else
    Set plan = Union(plan, Intersect(cell.EntireColumn, ActiveSheet.UsedRange))
    End If
    End If
    Next
    If Not plan Is Nothing Then
    Set rng2 = Application.Intersect(Target, plan)
    If Not rng2 Is Nothing Then
    For Each cell2 In rng2
    If cell2 = "WO" Then
    MsgBox "You cannot change " & cell2.Address(False, False)
    GoTo e
    End If
    Next
    End If
    End If
    sel.Value = sv
    e:
    Application.EnableEvents = True
    End Sub

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Combine 2 Macros Help Required

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    HTH
    Regards, Jeff

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1