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: 15

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
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?
 

s_macloskey

New Member
Joined
Jul 1, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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
 

s_macloskey

New Member
Joined
Jul 1, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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
 

s_macloskey

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

ADVERTISEMENT

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
 

s_macloskey

New Member
Joined
Jul 1, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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: 20

Forum statistics

Threads
1,144,628
Messages
5,725,378
Members
422,621
Latest member
dfrare

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