Consulting

Results 1 to 16 of 16

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  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.

Posting Permissions

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