Results 1 to 5 of 5

Thread: VBA Prevent user from entering data into cell

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Prevent user from entering data into cell

    I am looking for some help to add too the following code
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)    Dim ws As Worksheet
        Dim rng As Range
        Set rng = Range("C11")
      If Target.Address = rng.Address Then
        For Each ws In Worksheets
            Select Case UCase(ws.Name)
            Case "MENU", UCase(rng.Value)
                ws.Visible = xlSheetVisible
            Case Else
                ws.Visible = xlSheetVeryHidden
            End Select
        Next ws
        Sheets(rng.Value).Select
      End If
    End Sub
    I have noticed some users deleting and typing there own text into Cell "C11" and although the data validation shows an error the code then goes into a "Run-time error". Does anyone know a way I could bypass this? I was thinking maybe locking the worksheet and leaving Cell "C11" as unlocked then using VBA to prevent the user from doing anything but use the mouse to select the drop down?

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,294
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Prevent user from entering data into cell

    Try:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim w   As Worksheet
        
        If Not Intersect(Target, Cells(11, 3)) Is Nothing Then
            On Error GoTo InvalidName
            Set w = Sheets(Cells(11, 3).Value)
            On Error GoTo 0
            For Each w In ThisWorkbook.Worksheets
                w.Visible = xlSheetVeryHidden
                If InStr("|MENU|" & UCase(Target.Value) & "|", w.Name) > 0 Then w.Visible = xlSheetVisible
            Next w
        End If
        
        Exit Sub
        
    InvalidName:
        MsgBox "Worksheet: " & Target.Value & vbCrLf & vbCrLf & "Not found! Please check and try again", vbExclamation, "Invalid Sheet Name"
        
    End Sub
    Last edited by JackDanIce; Aug 27th, 2019 at 08:35 AM. Reason: Check for "MENU" or Target.Value


  3. #3
    New Member
    Join Date
    Apr 2017
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Prevent user from entering data into cell

    Cannot get this to work, I recieve the following error "Run-time error '1004': Method 'Visible' of object'_Worksheet' failed" ?
    Quote Originally Posted by JackDanIce View Post
    Try:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim w   As Worksheet
        
        If Not Intersect(Target, Cells(11, 3)) Is Nothing Then
            On Error GoTo InvalidName
            Set w = Sheets(Cells(11, 3).Value)
            On Error GoTo 0
            For Each w In ThisWorkbook.Worksheets
                w.Visible = xlSheetVeryHidden
                If InStr("|MENU|" & UCase(Target.Value) & "|", w.Name) > 0 Then w.Visible = xlSheetVisible
            Next w
        End If
        
        Exit Sub
        
    InvalidName:
        MsgBox "Worksheet: " & Target.Value & vbCrLf & vbCrLf & "Not found! Please check and try again", vbExclamation, "Invalid Sheet Name"
        
    End Sub

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,294
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Prevent user from entering data into cell

    Try:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim w   As Worksheet
        Dim s   As String
        
        If Not Intersect(Target, Cells(11, 3)) Is Nothing Then
            On Error GoTo InvalidName
            Set w = Sheets(Cells(11, 3).Value)
            On Error GoTo 0
            For Each w In ThisWorkbook.Worksheets
                With w
                    .Visible = xlSheetVisible
                    If InStr("|MENU|" & UCase(Target.Value) & "|", w.Name) > 0 Then
                        .Visible = xlSheetVisible
                    Else
                        .Visible = xlSheetHidden
                    End If
                End With
            Next w
        End If
        
        Exit Sub
        
    InvalidName:
        MsgBox "Worksheet: " & Target.Value & vbCrLf & vbCrLf & "Not found! Please check and try again", vbExclamation, "Invalid Sheet Name"
        
    End Sub
    If it doesn't work, please state what line the error occurs on as well as error message.
    Last edited by JackDanIce; Aug 27th, 2019 at 09:59 AM.


  5. #5
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,042
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Prevent user from entering data into cell

    Quote Originally Posted by GeorgeWhite View Post
    Cannot get this to work, I recieve the following error "Run-time error '1004': Method 'Visible' of object'_Worksheet' failed" ?
    What you did not mention is that the Menu Sheet is hidden when selected sheet made visible and reason reason the code you posted was written in that manner.

    Just add the onerror trap shown in JackDanceIce code to your own & see if that helps with your issue.

    Dave
    Last edited by dmt32; Aug 27th, 2019 at 10:02 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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