none
MsgBox Error Creating a Work Sheet with Auto Locking Individual Cells RRS feed

  • Question

  • Hi everyone,

    I am having a little bit of an issue with my auto locking cells and a message box that pops up when you move out of the cell.  Unfortunately since I have limited coding ability I was forced to look something up.  I found this code below but the section in BOLD font isn't working.  Which is preventing me from running the program.  

    What I am looking for in the program is that when a user inputs data into a cell on Microsoft Excel and they move away from the cell it prompts them to confirming there entry. Once confirmed that individual cell will lock but leave the rest of the work sheet able to input information.  I have tried a couple different work around's I know and a couple I have researched but it keeps giving me the same message even if I take the BOLD  section out.  The message is: Compile Error: Expected:list separator or ) and it highlights the word you.  Also, I am looking for it to do a whole Column in the excel.  Any help would be most appreciated.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    If Target.Column = 2 Then
    confirm = MsgBox(“Do you wish to confirm entry of this data?” _
    & vbCrLf & “You will not be allowed to change it!”, vbYesNo, “confirm Entry”)
    Select Case confirm
    Dim Cell As Range
    With ActiveSheet
    .Unprotect "hello"
    .Cells.Locked = False
    For Each Cell In ActiveSheet.UsedRange
    If Cell.Value = “” Then
    Cell.Locked = False
    Else
    Cell.Locked = True
    End If
    Next Cell
    .Protect "hello"
    End With
    Case Is = vbNo
    Application.Undo
    End Select
    End If
    Application.EnableEvents = True
    End Sub

    Tuesday, August 11, 2015 3:15 PM

Answers

  • ​>>>Compile Error: Expected:list separator or )

    I have used your vba code, and reproduce your issue, you can modify by referring to below code

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
     Application.EnableEvents = False
     If Target.Column = 2 Then
    
     confirm = MsgBox("Do you wish to confirm entry of this data?" _
    
     & vbCrLf & "You will not be allowed to change it!", vbYesNo, "confirm Entry")
    
     Select Case confirm
      Case Is = vbYes
        Dim Cell As Range
        With ActiveSheet
        .Unprotect "hello"
        .Cells.Locked = False
        For Each Cell In ActiveSheet.UsedRange
        If Cell.Value = "" Then
        Cell.Locked = False
        Else
        Cell.Locked = True
        End If
        Next Cell
        .Protect "hello"
        End With
    
    'Can only use .Undo to undo the last action in the worksheet.Cannot use it to undo vba actions.
    ' Case Is = vbNo
    ' Application.Undo
     End Select
     End If
    
     Application.EnableEvents = True
    
    End Sub



    Wednesday, August 12, 2015 8:42 AM

All replies

  • This forum supports Visual Basic in Visual Studio (VB 7 and later). You seem to be using Visual Basic for Applications (VBA) in Excel.

    For help with the VBA language, try the VBA Forum

    For help using VBA in Excel, try the Excel for Developers Forum

    Tuesday, August 11, 2015 3:33 PM
  • Thank you.
    Tuesday, August 11, 2015 3:43 PM
  • ​>>>Compile Error: Expected:list separator or )

    I have used your vba code, and reproduce your issue, you can modify by referring to below code

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
     Application.EnableEvents = False
     If Target.Column = 2 Then
    
     confirm = MsgBox("Do you wish to confirm entry of this data?" _
    
     & vbCrLf & "You will not be allowed to change it!", vbYesNo, "confirm Entry")
    
     Select Case confirm
      Case Is = vbYes
        Dim Cell As Range
        With ActiveSheet
        .Unprotect "hello"
        .Cells.Locked = False
        For Each Cell In ActiveSheet.UsedRange
        If Cell.Value = "" Then
        Cell.Locked = False
        Else
        Cell.Locked = True
        End If
        Next Cell
        .Protect "hello"
        End With
    
    'Can only use .Undo to undo the last action in the worksheet.Cannot use it to undo vba actions.
    ' Case Is = vbNo
    ' Application.Undo
     End Select
     End If
    
     Application.EnableEvents = True
    
    End Sub



    Wednesday, August 12, 2015 8:42 AM