Macro command button

Question

Hello,

I have created a worksheet for other users to enter some data. I want the users to be able to work on specific cells which will be always unprotected and not be able to work on the protected cells unless i unlock it. And i want a second button to apply the Protect save and exit the file when finished. Is it possible to create a button and assign it with macro/VBA code so that the user can click the button to lock, save and exit the worksheet ?? If possible please share the macro/VBA code.

Answer 1

Sorry about that - try this

Sub Test()

strWorkSheet = "Sheet1"

Worksheets(strWorkSheet). Protect "123456" Worksheets(strWorkSheet). Unprotect "123456" End Sub

Answer 2

Hello Diane,

Unfortunatly it does not work, it gives me back the following msg :

Answer 3

This will protect or unprotect a sheet -

strWorkSheet = "sheet2" strWorkSheet.Protect Password:="myPassword" strWorkSheet.Unprotect Password:="myPassword"

But if they should only have the ability ranges, locking / unlocking cells or ranges would be better (you'll leave it protected)

unlock: strWorkSheet.Range("A1"). Locked = False

lock strWorkSheet.Range("A1"). Locked = True

Answer 4

Hello Diane,

Thank's for your reply, i am familliar with the features of the Excel regarding the protection of the sheets, the thing is that i'm looking for a button that will handle all procedures (Save, Protect and Exit) with 1 click, because i will remotely connect to the user's pc in order to unlock and then lock again the file.

Answer 5

Hello Triantafyllos1982

You can use VBA to lock and unlock a workbook (but need the password stored in the VBA).

Or you can use features in Excel to lock parts of the workbook - VBA not required. https://support.microsoft.com/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a

-- Diane

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.