PDA

View Full Version : Select cells with input box and unlock them in a protected worksheet



caudillo
02-22-2020, 02:45 PM
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:


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:


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:


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.

Leith Ross
02-23-2020, 03:08 PM
Hello caudillo,

Welcome.

The three macro can be merged into one. But to do that, I need a little more information about your workbook.



Are the cells to be unlocked fixed addresses or to be selected by the user?
What would trigger the InputBox to be displayed: clicking a button on the worksheet or something else?
What is the password for the protected worksheet?

Paul_Hossler
02-23-2020, 03:27 PM
Welcome to the forum

Please take a few minutes to read the FAQs in my signature

I added the CODE tags to your first post, details are in the FAQ so you can add them next time

caudillo
02-24-2020, 02:00 AM
Hello Leith,
thank you for your response. Here are the answers to your questions:

1.The cells will be selected by the user, since they will be different every now and then.
2. The InputBox will be displayed after clicking a button.
3. The password for the worksheet is "esp".

I will be happy to give you any other information needed. And I am really looking forward to hearing from you again. Thank you in advance for all your time and effort.

P.S.: Hello Paul. Sorry for any inconvenience. I will surely read all your suggestions.

caudillo
02-29-2020, 08:05 AM
Hello again Leith,
it's been a while since your last reply and I was wondering if it is too difficult or even impossible to accomplish my request. I don't want to rush you, just wondering...

Leith Ross
03-01-2020, 04:09 PM
Hello caudillo,

I had to leave yesterday for an emergency. I won't be back for a week.

caudillo
03-01-2020, 05:58 PM
Hi Leith,
I hope that all will end well, regarding your emergency. As for my request, I can surely wait for you to get back and with good news for you.

caudillo
03-13-2020, 03:33 AM
Hello Leith,
I really hope that all went well regarding your emergency. I hope I am not troubling you, but I was wondering if you are willing to deal with my request. That is, in case you have the chance and you are not bothered by other emergencies. In any case, thank you for your response.

caudillo
03-13-2020, 06:05 AM
Hello again,
I tried to merge the three parts of code that I mention in my original message and came up with the following:

Sub RangeSelectionPrompt()
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Sheet1")
Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

MsgBox "The cells selected were " & rng.Address
If Intersect(Target, rng) Is Nothing Then
Else

ws.Unprotect Password:="password"
Target.Locked = False
ws.Protect Password:="password"

End If
If Not rng Is Nothing Then
Sheets("Sheet1").Unprotect password:="hello"
rng.Locked = True
Sheets("Sheet1").Protect password:="hello"
End If

End Sub

Then I tried to see if it's working with an online vba editor, which returned the following errors:
/home/main.vb (10,11) : error VBNC90019: Expected 'End'.
/home/main.vb (11,8) : error VBNC90019: Expected 'End'.
/home/main.vb (13,11) : error VBNC30203: Identifier expected.
/home/main.vb (14,3) : error VBNC30203: Identifier expected.
/home/main.vb (15,5) : error VBNC30203: Identifier expected.
/home/main.vb (17,7) : error VBNC30203: Identifier expected.
/home/main.vb (18,11) : error VBNC30203: Identifier expected.
/home/main.vb (19,7) : error VBNC30203: Identifier expected.
/home/main.vb (21,7) : error VBNC30203: Identifier expected.
/home/main.vb (22,3) : error VBNC30203: Identifier expected.
/home/main.vb (23,15) : error VBNC30203: Identifier expected.
/home/main.vb (24,12) : error VBNC30203: Identifier expected.
/home/main.vb (25,15) : error VBNC30203: Identifier expected.
/home/main.vb (26,11) : error VBNC30203: Identifier expected.
/home/main.vb (28,8) : error VBNC30203: Identifier expected.
/home/main.vb (30,11) : error VBNC30203: Identifier expected.
I have no idea what to do to correct the errors. I am posting my effort just to help anyone point me in the right direction.
Thanks.

caudillo
04-20-2020, 02:22 PM
Hello,
I hope and wish everyone is ok and healthy, away from anything that has to do with this coronavirus. I was just wondering if anyone could help me with merging the three parts of code that I mention in my original message. It would be of great importance to me and deeply appreciated.
Thank you and I wish that all will end well for everyone.

paulked
04-20-2020, 04:29 PM
Hi
I think this is the simplest way to do it:



Sub ChangeRange()
Dim rng As Range, cel As Range
Set rng = InputBox("Select a range", "Obtain Range Object", Type:=8)
Sheet1.Unprotect ("esp")
For Each cel In rng
cel = InputBox("Enter value", "Change cells...", cel)
Next
Sheet1.Protect ("esp")
End Sub

caudillo
04-21-2020, 03:02 AM
Hello paulked,
I really appreciate the time and effort you took to answer my message. I will try your solution and let you know. Thanks again.

caudillo
04-21-2020, 06:29 AM
Hi paulked,
I 've just tried your code and I get an error message, the one in the photo I attach. I think it will work, but I need your help with this error.
Thanks in advance.26404

paulked
04-21-2020, 06:52 AM
My apologies. To use the Type the Application has to be specified. This should do it:



Sub ChangeRange()
Dim rng As Range, cel As Range
Set rng = Application.InputBox("Select a range", "Obtain Range Object...", Type:=8)
Sheet1.Unprotect ("esp")
For Each cel In rng
cel = InputBox("Enter value", "Change cells...", cel)
Next
Sheet1.Protect ("esp")
End Sub

caudillo
04-21-2020, 10:29 AM
Hello again paulked,
I 've just tried your code and it worked perfectly. I really appreciate your help. It saved me a lot of hous of work. I will continue testing it and hope that all will go well.
Thank you again for your aid. I wish you all the best.

paulked
04-21-2020, 10:37 AM
No worries, stay safe :thumb