Lock Cell Macro

C

ccoleman

Board Regular
Joined
Dec 2, 2005
Messages
70
Ok I wrote up a macro that can lock cells using the following

Private Sub Lock_Click()
Sheets("Raw Assay").Protect UserInterFaceOnly:=True, Password:="Password"
Sheets("Raw Assay").Range("C6:D53").Locked = True
Sheets("Raw Assay").Range("J6:L53").Locked = True
Sheets("Raw Assay").Range("D1:F1").Locked = True
Sheets("Raw Assay").Range("K1").Locked = True
End Sub

Private Sub Unlock_Click()
Sheets("Raw Assay").Protect UserInterFaceOnly:=True
Sheets("Raw Assay").Range("C6:D53").Locked = False
Sheets("Raw Assay").Range("J6:L53").Locked = False
Sheets("Raw Assay").Range("D1:F1").Locked = False
Sheets("Raw Assay").Range("K1").Locked = False
End Sub

###
The code will lock the cells just fine as well as unlock them. The problem is that I want it to require the password to unlock the cells. I thought passing this into the lock and not the unlock would suffice but the password given to lock the cells doesn't seem to apply. Any suggestions?

Thanks
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
How's this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Unlock_Click()
    <SPAN style="color:#00007F">Dim</SPAN> response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
        response = InputBox("Please Enter the Password", "Password Required", "*****")
        <SPAN style="color:#00007F">With</SPAN> Sheets("Raw Assay")
            .Unprotect Password:=response
            .Range("C6:D53, J6:L53,D1:F1, K1").Locked = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty
 
Upvote 0
One more question, do you by any chance know what the command is to make it treat the entered text like a password (so that *** comes up as you type instead of the letters). I remember there is one but remembering what it is is a different story
 
Upvote 0
The easiest thing to do is create a UserForm to mimic an InputBox and in the Password textbox set the Password Character to "*".

Smitty
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
540
dmt32
D
N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
252
Peter_SSs
Peter_SSs
J
  • Question Question
Macro to "Finalize" and lock a workbook
Replies
2
Views
200
JimmyHack
J
S
  • Question Question
Lock Script misses first Range
2
Replies
10
Views
150
Micron
M
GomaPile
  • Question Question
Protect Sheets and Allow Editing
Replies
6
Views
321
Joe4
Joe4

Forum statistics

Threads
1,225,156
Messages
6,183,227
Members
453,152
Latest member
ChrisMd

Share this page

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