VBA to delete specified row

Will85

Board Regular
Joined
Apr 26, 2012
Messages
230
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.
 

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Without protecting the workbook can I protect only the name of a particular sheet.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,415
Office Version
  1. 365
Platform
  1. Windows
Without protecting the workbook can I protect only the name of a particular sheet.
Not sure I follow you. You don't protect the "name" of the sheet, you protect the sheet.
 

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
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.
 

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
He should not be able to tamper the name of the sheet nor be able to delete the sheet.
 

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,415
Office Version
  1. 365
Platform
  1. Windows
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?
 

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
You mean like this
Rich (BB code):
Set SourceSheet = Worksheets("Data To Clean")
change to sheet1
Rich (BB code):
Set SourceSheet = Worksheets("sheet1")
 

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,415
Office Version
  1. 365
Platform
  1. Windows
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...
 

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Thanks Kevin. I will give it a try and update you later.
 

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Kevin. The query is solved by @rollis13 not as I expected but that was the best option.
 

Forum statistics

Threads
1,186,006
Messages
5,955,273
Members
438,188
Latest member
DLJ

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
Top