VBA button lock specific cells in a sheet that is already password protected?

s_macloskey

New Member
Joined
Jul 1, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Please see the screenshot I've attached.

I have made a holiday request sheet for staff.

The sheet is protected by a password (excluding the blue cells to allow for requests to be entered)

Is there a way I can make a VBA utton at the end of each line so that the staff can LOCK each line seperately (to keep thier request safe) and to then be unlocked by the main sheet password by a manager?

Any suggestions at all are welcome,

Thanks!

S Macloskey
 

Attachments

  • LEAVE SHEET.png
    LEAVE SHEET.png
    133 KB · Views: 34

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Instead of using a button to lock each individual row, it would be easier if you could insert a column to the right of column DH. Each cell in the new column could have a drop down with one item ("Lock") that could be selected to lock that row. You could use a button for the manager to unlock the sheet. Would that work for you?
 
Upvote 0
Instead of using a button to lock each individual row, it would be easier if you could insert a column to the right of column DH. Each cell in the new column could have a drop down with one item ("Lock") that could be selected to lock that row. You could use a button for the manager to unlock the sheet. Would that work for you?

Mumps, This would be great. Do you know how I would do this?

I've had a look online but can't find exactly what you're referring to.

Thanks,

S Macloskey
 
Upvote 0
Instead of using a button to lock each individual row, it would be easier if you could insert a column to the right of column DH. Each cell in the new column could have a drop down with one item ("Lock") that could be selected to lock that row. You could use a button for the manager to unlock the sheet. Would that work for you?

Just a thought. Maybe I could make a button that would add data validation to the row of cells to stop anyone else from making changes it after? But I also don't really know how the VBA codes well enough to do it.. It wouldn't have to be unlocked as only the cells next to request need to be populated with "approved" etc.

S Macloskey
 
Upvote 0
UPDATE:

I used this on the Sheet's VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("AI6:GA232"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="2840"
If xRg.Value <> mStr Then xRg.Locked = True
Target.Worksheet.Protect Password:="2840"
End Sub

But It's not perfect and I'd still prefer a Drop-Down to lock a row!!

S Macloskey
 
Upvote 0
SOLVED

I worked it out myself. (see picture)

I created an VBAbutton in each row with:

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="2840"
Worksheets("Sheet1").Range("FW6:GA6").Locked = True
ActiveSheet.Protect Password:="2840"
End Sub

Thank you Mumps for your suggestion at least.,

S Macloskey
 

Attachments

  • BUTTON.png
    BUTTON.png
    116.1 KB · Views: 72
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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