VBA Prevent user from entering data into cell

GeorgeWhite

New Member
Joined
Apr 20, 2017
Messages
27
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:
Upvote 0
Cannot get this to work, I recieve the following error "Run-time error '1004': Method 'Visible' of object'_Worksheet' failed" ?
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top