Consulting

Results 1 to 16 of 16

Thread: Select cells with input box and unlock them in a protected worksheet

  1. #1
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location

    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:

    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.
    Last edited by Paul_Hossler; 02-23-2020 at 03:25 PM.

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello caudillo,

    Welcome.

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


    1. Are the cells to be unlocked fixed addresses or to be selected by the user?
    2. What would trigger the InputBox to be displayed: clicking a button on the worksheet or something else?
    3. What is the password for the protected worksheet?
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  5. #5
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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...
    Last edited by caudillo; 02-29-2020 at 11:42 AM.

  6. #6
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello caudillo,

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

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  7. #7
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  8. #8
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  9. #9
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  10. #10
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  11. #11
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  12. #12
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  13. #13
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.0001.jpg

  14. #14
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  15. #15
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  16. #16
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    No worries, stay safe
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •