VBA Prevent user from entering data into cell

[GeorgeWhite]

I am looking for some help to add too the following 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?

 

[JackDanIce]

Try:

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

 

[GeorgeWhite]

Cannot get this to work, I recieve the following error "Run-time error '1004': Method 'Visible' of object'_Worksheet' failed" ?

Try:

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

 

[JackDanIce]

Try:

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.

 

[dmt32]

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