+ Reply to Thread
Results 1 to 3 of 3

Add Delete Button To Macro

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

    Add Delete Button To Macro

    Hi guys the following formula works great and if text is written over then it will put the original text back in automatically.

    only problem is if i hit delete on the keyboard it deletes it - i want to stop this delete button from working.

    any1 know how? please see macro belo

    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 Contributor
    Join Date
    06-23-2010
    Location
    Iowa, United States
    MS-Off Ver
    Excel 2007
    Posts
    145

    Re: Add Delete Button To Macro

    Mods,

    Should this be moved to the "Excel Programming" forum?

    Steve
    A VBA novice, excited to learn!

  3. #3
    Forum Contributor tkowal's Avatar
    Join Date
    11-20-2010
    Location
    Miami, Fl
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: Add Delete Button To Macro

    I highly recommend not Disabling the "DELETE KEY" -- Consider yourself warned!

    A Simple way to disable the Delete Key is :

    In the ThisWorkbook module add this code:
    Please Login or Register  to view this content.
    Save the workbook and re-open it --- Delete Key is now disabled.

    This tells the application to do nothing when the Delete Key is pressed.....

    Again, best advise is to not to use it! I would work out other strategies.....
    Ted
    "Live Long and Prosper"

+ 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