VBA to delete specified row

[Will85]

I do not trust my users to have access to this workbook without it being protected.

Users need to be able to delete rows on a protected sheet.

I want vba code that asks the user what account number they want to delete "What customer ID to delete?"

The vba box should accept a user input for a number, then go to SheetABC finds the matching user input in Column A (if no match is found then respond with a message saying "The account deosnt exist), unprotect the sheet, go to that specific row, delete the row in its entirety, then reprotect the sheet.

For exmaple

VBA msgbox
"What customer ID to delete?"

User input
206700

VBA goes to sheetABC, finds 206700 in column A row 419, unprotects the sheet, deletes row 419, reprotects the sheet.

 

[kevin9999]

Try this:

Public Enum appInputBox
IBNumber = 1
End Enum
Sub Will85()
    Dim accNo As String, RowDelete As Range
    Dim ws As Worksheet: Set ws = Worksheets("sheetABC")
    
    accNo = Application.InputBox("What customer ID to delete?", Type:=IBNumber)
    Set RowDelete = ws.Range("A:A").Find(what:=accNo, LookIn:=xlValues, lookat:=xlWhole)
    If Not RowDelete Is Nothing Then
        ws.Unprotect Password:="123"
        RowDelete.EntireRow.Delete
        ws.Protect Password:="123"
        Else
        MsgBox "The account doesn't exist"
    End If
End Sub

 

[Will85]

Try this:

Public Enum appInputBox
IBNumber = 1
End Enum
Sub Will85()
    Dim accNo As String, RowDelete As Range
    Dim ws As Worksheet: Set ws = Worksheets("sheetABC")
   
    accNo = Application.InputBox("What customer ID to delete?", Type:=IBNumber)
    Set RowDelete = ws.Range("A:A").Find(what:=accNo, LookIn:=xlValues, lookat:=xlWhole)
    If Not RowDelete Is Nothing Then
        ws.Unprotect Password:="123"
        RowDelete.EntireRow.Delete
        ws.Protect Password:="123"
        Else
        MsgBox "The account doesn't exist"
    End If
End Sub

That worked perfectly. I have never seen Enum before. Thank you for teaching me something new, now I have to go and try and understand what its doing!

 

[kevin9999]

That worked perfectly. I have never seen Enum before. Thank you for teaching me something new, now I have to go and try and understand what its doing!

Glad I could help, and thanks for the feedback ?
In short, it's forcing the user to input a number. Try inputting an alpha string (or mixed) and you'll see what I mean.

 

[Will85]

Glad I could help, and thanks for the feedback ?
In short, it's forcing the user to input a number. Try inputting an alpha string (or mixed) and you'll see what I mean.

Ok, im glad you mentioned that, some of our account numbers have leading alpha characters, I just handt thought to test that just yet.

So an account number could be 5978 or it could be ABC5978

Thoughts?

 

[kevin9999]

Ok, im glad you mentioned that, some of our account numbers have leading alpha characters, I just handt thought to test that just yet.

So an account number could be 5978 or it could be ABC5978

Thoughts?

Leave it with me, it'll need to change.

 

[kevin9999]

Just lose the Enum

Sub Will85_2()
    Dim accNo As String, RowDelete As Range
    Dim ws As Worksheet: Set ws = Worksheets("sheetABC")
    
    accNo = Application.InputBox("What customer ID to delete?")
    Set RowDelete = ws.Range("A:A").Find(what:=accNo, LookIn:=xlValues, lookat:=xlWhole)
    If Not RowDelete Is Nothing Then
        ws.Unprotect Password:="123"
        RowDelete.EntireRow.Delete
        ws.Protect Password:="123"
        Else
        MsgBox "The account doesn't exist"
    End If
End Sub

 

[kevin9999]

Did the code in post#7 work for you?

 

[RAJESH1960]

Did the code in post#7 work for you?

Kevin. I was looking for something similar as above. Is it possible to delete a particular sheet from a protected workbook.?
I have posted a query in the new thread. Please have a look.
If I protect sheet name, code generates error

 

[kevin9999]

Kevin. I was looking for something similar as above. Is it possible to delete a particular sheet from a protected workbook.?
I have posted a query in the new thread. Please have a look.
If I protect sheet name, code generates error

As far as I know, no, you can't delete a particular sheet when the entire workbook is protected.