Select cells with input box and unlock them in a protected worksheet
Hello,
I am a newcomer to this forum and this is my first post. First of all, I would like to thank you for giving us the opportunity to learn things and to solve problems we encounter. I really appreciate the time and effort all of you take to respond to our questions.
I think that it's proper, since I am new here, to introduce myself. My name is Kostas and I am from Greece. I found your forum as I was searching the web for a solution to a problem I am having with a workbook that I am working on.
I am trying to create a button that runs (when pressed) a macro. The macro will unlock a range of cells for data entry while the whole worksheet will remain locked and protected. As I was trying to find help from the internet (since my vba knowledge is very limited), I found the next three parts of code:
1. The first one pops an input box and lets the user choose a range of cells:
Code:
Sub RangeSelectionPrompt()
Dim rng As Range
Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
MsgBox "The cells selected were " & rng.Address
End Sub
2. The second one claims to unlock cells on aprotected worksheet:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim inputRange As Range
Set ws = Worksheets("WorkSheetName")
'tell this sub to unprotect only these cells
Set inputRange = Range("I5,I7,I11")
' If the selected cell is not in the range keep the sheet locked
If Intersect(Target, inputRange) Is Nothing Then
'else unprotect the sheet by providing password
'(same as the one that was used to protect this sheet)Else
ws.Unprotect Password:="password"
Target.Locked = False
ws.Protect Password:="password"
End If
End Sub
3. The third one automatically locks the cells after data entry:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Intersect(Range("A1:100"), Target)
If Not MyRange Is Nothing Then
Sheets("Sheet1").Unprotect password:="hello"
MyRange.Locked = True
Sheets("Sheet1").Protect password:="hello"
End If
End Sub
Now I would like to merge the codes, so when I press the command button, the input box appears, I select a range of cells, the cells unlock for data entry and, finally, they lock automatically on complision. I really don't know if I am asking too much. As I mentioned before, my VBA is very poor. The thing is that I need this worksheet for my job and any help finishing it will be very appreciated.
P.S.:In case it helps, I am using Office 2016.
