+ Reply to Thread
Results 1 to 3 of 3

Prevent rows being deleted

Hybrid View

  1. #1
    Registered User
    Join Date
    08-05-2011
    Location
    Kaavi
    MS-Off Ver
    Excel 2007
    Posts
    45

    Prevent rows being deleted

    Hello, could you help with this please?

    I have code that copies cells A5:D8 and inserts them below. And also have a code that deletes wanted rows, it asks me which row do I wanna delete etc. BUT some people just dont' understand and sometimes they delete those A5:D8 rows also, and then whole workbook is ruined. Is it possible to protect these cells A5:D8 so that they are still able to be copied but users cannot delete them??

    this is the code that deletes rows

    Sub delete()
    ActiveSheet.Unprotect
       Dim a As Long, response As Long
       a = Application.InputBox( _
          Prompt:="Row-number", _
          Title:="Delete row:", Type:=1)
       If a <> False Then
         response = MsgBox("Are u sure u wanna delete row", vbYesNo)
         If response = vbYes Then Rows(a).Delete
       End If
    ActiveSheet.Protect
    End Sub

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Prevent rows being deleted

    The intersect function will check if the row a user want to delete INTERSECT your range A5:D8. If so, it will end the Sub. You can also prompt the user with a message telling not to delete this row. I'll leave this to you.
    
    Sub delete()
    ActiveSheet.Unprotect
       Dim a As Long, response As Long, asd As Range
       a = Application.InputBox( _
          Prompt:="Row-number", _
          Title:="Delete row:", Type:=1)
       If a <> False Then
         response = MsgBox("Are u sure u wanna delete row", vbYesNo)
         If Not Intersect(Rows(a), Range("a5:d8")) Is Nothing Then Exit Sub
         If response = vbYes Then Rows(a).delete
       End If
    ActiveSheet.Protect
    End Sub

  3. #3
    Registered User
    Join Date
    08-05-2011
    Location
    Kaavi
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Prevent rows being deleted

    Thank you!

+ 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