User MUST Enter Data before selecting tabs

[surkdidat]

Hi there

I have a main data sheet users are not filling in, so what I am trying to look at as a solution is on the "Brief" tab the use MUST enter data in cells D16 and D17 (D17 being a Dropdown only) before moving onto filling data out in the other tabs on the worksheet. Ideally a error box would flash up and say Please enter "Requested By" (D16) and Source (D17).

 

[Anthony47]

Use the Worksheet_Deactivate event to check that those cells are filled:
-rightclick on the tab with the name of the sheet; select Display code. This will open the editor of the macro at the right position
-copy the following code and parte it into the "empty" frame at the right of the vba window:

Private Sub Worksheet_Deactivate()
Dim myMsg As String
'
If Me.Range("D16").Value = "" Then myMsg = " D16  "
If Me.Range("D17").Value = "" Then myMsg = myMsg & " D17  "
If Len(myMsg) > 0 Then
    MsgBox ("Please fill " & myMsg)
    Me.Activate
    Range(Trim(Left(myMsg, 6))).Select
End If
End Sub

Now return to excel, and try to exit from the sheet

If there is other code on the vba window then publish it, because we need to check possible compatibility problems.

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.ScreenUpdating = False
    If ActiveSheet.Name <> "Brief" Then
        With Sheets("Brief")
            If .Range("D16") = "" Or .Range("D17") = "" Then
                .Activate
                MsgBox ("Please enter 'Requested By' (D16) and 'Source' (D17)")
            End If
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[RobP]

@mumps, do you need to auto run this code before the user gets locked out ?

(I mean it did nothing if it wasnt run beforehand for me.. meaning people can get around it ?)

 

[mumps]

Make sure you place the macro in the code module for "ThisWorkbook". You don't have to run the code manually. The macro works in the following way:
-When a user selects a sheet other that "Brief", the macro checks cells D16 and D17 in "Brief" to see if they are blank. If either or both cells are blank, the user is taken back to "Brief" to fill in the missing data. The end result of the macro is that the user will not be allowed to select a different sheet unless both D16 and D17 in "Brief" are filled in. I hope that makes sense.

 

[RobP]

ah.. I see.. that was my issue - I had it pasted into codespace for Sheet1... many thanks for clarification - I'd not seen this before so was keen to learn.

hopefully it works also for surkdidat..

 

[dmt32]

Hi,
another suggestion using the Worksheet_Deactivate event

Place code in your worksheets CODE PAGE

Private Sub Worksheet_Deactivate()
    Dim cell        As Range
    Dim i           As Long
    
    For Each cell In Range("D16:D17")
        i = i + 1
        If Len(cell.Value) = 0 Then
            MsgBox Choose(i, "Requested By (", "Source (") & cell.Address(0, 0) & _
            ") Must Be Completed To Continue", 48, "Entry Required"
            Me.Activate
            cell.Select
            Exit For
        End If
    Next cell
    
End Sub

Just be aware that all these ideas only work if your users enable macros.

Dave

 

[mumps]

You are very welcome.