VBA to delete specified row

Will85

Board Regular
Joined
Apr 26, 2012
Messages
240
Office Version
  1. 365
Platform
  1. Windows
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.
 
Without protecting the workbook can I protect only the name of a particular sheet.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
My code runs depending on the name of the default sheet. If the name is changed the code will run into an error. There are chances that the user may try to change the name of the sheet or edit the name of the sheet. To be on the safer side, I want only the name of the sheet to be protected so that the user can paste data each time in that sheet and customize it.
 
Upvote 0
He should not be able to tamper the name of the sheet nor be able to delete the sheet.
 
Upvote 0
My code runs depending on the name of the default sheet. If the name is changed the code will run into an error. There are chances that the user may try to change the name of the sheet or edit the name of the sheet. To be on the safer side, I want only the name of the sheet to be protected so that the user can paste data each time in that sheet and customize it.
Why not use the sheet's code name in your code - there's less chance of a user changing that and causing problems?
 
Upvote 0
You mean like this
Rich (BB code):
Set SourceSheet = Worksheets("Data To Clean")
change to sheet1
Rich (BB code):
Set SourceSheet = Worksheets("sheet1")
 
Upvote 0
You mean like this
Rich (BB code):
Set SourceSheet = Worksheets("Data To Clean")
change to sheet1
Rich (BB code):
Set SourceSheet = Worksheets("sheet1")
No. That's still the Worksheets names (in quotation marks, at least that's what it looks like). Look in the VBA Editor to see what the codename are. I am about to go off-line now. Best wishes...
 
Upvote 0
Thanks Kevin. I will give it a try and update you later.
 
Upvote 0
Kevin. The query is solved by @rollis13 not as I expected but that was the best option.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top