How can I make a certain cell in a sheet mandatory for a user to complete

[ramatthews]

Hi All,

I hope someone can help me. I have specific cells in certain sheets to be completed by the user before they can move away from the sheet. I gather that I have to build a macro for this. The one specific sheet I am referring to Cell "C103" is crucial to complete. I do have the following macro already on this sheet as follows for other functions:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$103" Then
        If Range("'Client info'!C103").Value = "Large Enterprise" Then
            Sheets("MC Calculations").Rows("1:134").EntireRow.Hidden = False
            Sheets("Skills Spend & Learnerships").Rows("1:108").EntireRow.Hidden = False
            Sheets("BEE Spend").Rows("24:27").EntireRow.Hidden = True
            Sheets("BEE Spend").Rows("11:22").EntireRow.Hidden = False
            Sheets("Supplier and ED Development").Rows("5:42").EntireRow.Hidden = False
            Sheets("Supplier and ED Development").Rows("43:71").EntireRow.Hidden = True
            Sheets("ENTERPRISE AND SUPPLIER DEV").Rows("66:100").EntireRow.Hidden = True
            Sheets("ENTERPRISE AND SUPPLIER DEV").Rows("7:64").EntireRow.Hidden = False
        ElseIf Range("'Client info'!C103").Value = "Qualifying Small Enterprise" Then
            Sheets("MC Calculations").Rows("1:134").EntireRow.Hidden = True
            Sheets("Skills Spend & Learnerships").Rows("1:108").EntireRow.Hidden = True
            Sheets("BEE Spend").Rows("11:22").EntireRow.Hidden = True
            Sheets("BEE Spend").Rows("24:27").EntireRow.Hidden = False
            Sheets("Supplier and ED Development").Rows("5:42").EntireRow.Hidden = True
            Sheets("Supplier and ED Development").Rows("43:71").EntireRow.Hidden = False
            Sheets("ENTERPRISE AND SUPPLIER DEV").Rows("7:64").EntireRow.Hidden = True
            Sheets("ENTERPRISE AND SUPPLIER DEV").Rows("66:100").EntireRow.Hidden = False
        End If
    End If
End Sub

Can someone please assist me in building the new macro into the existing one?

[daffodil11]

It wouldn't be a part of the existing one. You can add another which is a Worksheet_Deactivate() event.

Private Sub Worksheet_Deactivate()

'repeat this block of code as needed
 If Range("C1") = "" Then
    MsgBox "You must fill in C1"
    Me.Activate
    Range("C1").Select
End If

End Sub

[ramatthews]

Thank you sooo much,this worked perfectly.

How can I create a message which will show as soon as the user access a specific worksheet i.e to give them instructions how to complete the sheet?

[JOHN H. DAVIS]

Maybe:

Private Sub Worksheet_Activate()
Msgbox "This is how to complete this sheet:" & vbcrlf & "Step1 - Fill this in as desired" & vbcrlf & "Step2 - Fill this in as desired" 'ect ect
End Sub

[ramatthews]

That works perfectly, thank you! How do I ensure that a user only sees this message the first time it visits the sheet, and not everytime when it toggles between the sheets?

[daffodil11]

You could do it by declaring a variable as public, which retains its value for the life of the application. Declarations go at the very top of a module.

If this displays once, it won't show again during that application's instance.

Public SheetActivated as Boolean
Private Sub Worksheet_Activate()
If SheetActivated = False Then
   Msgbox "This is how to complete this sheet:" & vbcrlf & "Step1 - Fill this in as desired" & vbcrlf & "Step2 - Fill this in as desired" 'ect ect
   SheetActivated = True
End If
End Sub

[ramatthews]

Thank you soo much - this worked perfectly!!! I appreciate all your assistance!

[JOHN H. DAVIS]

daffodil11 has a better solution on the last question. This uses a helper cell in A1.

Private Sub Worksheet_Activate()
If Range("A1") = 1 Then Exit Sub
MsgBox "This is how to complete this sheet:" & vbCrLf & "Step1 - Fill this in as desired" & vbCrLf & "Step2 - Fill this in as desired" 'ect ect
End Sub

Private Sub Worksheet_Deactivate()
Range("A1") = 1
End Sub