Prevent saving a worksheet is some cells contain certain text!

[josullivan601632]

Hi everyone. I have created a rather nifty worksheet for my colleague to complete, I have added a macro that jumps to the next cell to complete so users dont have to manually move to the next cell and that works great. There are cells which at the moment I have a drop down box for users to select "yes" or "no" and the master is saved with "select" in those cells to prompt the user to fill in that cell. As the "yes" and "no" options are really important and the users are now forgetting to "select", I want to add something that stops the users saving the worksheet if those cells still contain "select". Can I do this, would this effect the other macro I have? Any help would be fantastic!

 

[mumps]

In which sheet and cells is the drop down list with "Yes" and "No"?

 

[Legacy 456155]

This does not answer your question, but it may help by providing a stronger hint. It simply expands your validation list when the cell is selected. The user cannot tab out of it, but must select an item or press the escape key. There may be other ways, but at his point, the use's actions would be deliberate and not forgetful. I would use a named range that contains your Yes/No validation cells and forego using the 'HasYesNoValidation' function.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        If HasYesNoValidation(Target) Then
            With Target.Validation
                .Delete
                .Add xlValidateList, xlValidAlertStop, , "Yes, No"
                .InCellDropdown = True
                .ErrorMessage = "Please select 'Yes' or 'No'"
                .ShowError = True
                .IgnoreBlank = False
            End With
            Application.SendKeys "%{DOWN}"
        End If
    End If
End Sub

Private Function HasYesNoValidation(Target As Range)
    On Error Resume Next
    HasYesNoValidation = InStr(Target.Validation.Formula1, "Yes, No")
    On Error GoTo 0
End Function

 

[josullivan601632]

In which sheet and cells is the drop down list with "Yes" and "No"?

Worksheet is called "BuyersWorksheet", Yes and No in M46, M47, M48, M49, M50, M51 & M2

 

[josullivan601632]

This does not answer your question, but it may help by providing a stronger hint. It simply expands your validation list when the cell is selected. The user cannot tab out of it, but must select an item or press the escape key. There may be other ways, but at his point, the use's actions would be deliberate and not forgetful. I would use a named range that contains your Yes/No validation cells and forego using the 'HasYesNoValidation' function.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        If HasYesNoValidation(Target) Then
            With Target.Validation
                .Delete
                .Add xlValidateList, xlValidAlertStop, , "Yes, No"
                .InCellDropdown = True
                .ErrorMessage = "Please select 'Yes' or 'No'"
                .ShowError = True
                .IgnoreBlank = False
            End With
            Application.SendKeys "%{DOWN}"
        End If
    End If
End Sub

Private Function HasYesNoValidation(Target As Range)
    On Error Resume Next
    HasYesNoValidation = InStr(Target.Validation.Formula1, "Yes, No")
    On Error GoTo 0
End Function

That certainly would give the same result! I am not confident with macros, I have one on the worksheet already for the user to just tab into the cells for completing which is nice, do I just copy and paste the above at the end of that macro, something tells me it wouldn't be that straight forward!

 

[mumps]

Place these macros 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 macros into the empty window that opens up. Close the window to return to your sheet. These two macros will check the range M46:M52 for the word "Select". If any cell in that range contains "Select", the user will be prompted to select "Yes" or "No" in that cell and will not be allowed to save or close the file. You can use both macros or only the Workbook_BeforeSave macro if you don't want to check the range if the user tries to close the file.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Sheets("BuyersWorksheet").Range("M46:M52")
        If rng = "Select" Then
            MsgBox ("Please select 'Yes' or 'No' in cell " & rng.Address(0, 0))
            Cancel = True
            Exit For
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Sheets("BuyersWorksheet").Range("M46:M52")
        If rng = "Select" Then
            MsgBox ("Please select 'Yes' or 'No' in cell " & rng.Address(0, 0))
            Cancel = True
            Exit For
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

These macros should affect any other macros you are currently using.

 

[dmt32]

Hi,
you could create simple function to Cancel the Close or Save events

Thisworkbook code page

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = NotComplete
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = NotComplete
End Sub

Standard module

Function NotComplete() As Boolean
    Dim Cell As Range, Rng As Range
    
    Set Rng = Worksheets("BuyersWorksheet").Range("M46:M52")
    For Each Cell In Rng.Cells
        NotComplete = IsError(Application.Match(Cell.Value, Array("Yes", "No"), 0))
        If NotComplete Then
             Cell.Parent.Activate
             Cell.Select
            MsgBox "Please Select Yes or No From List", 48, "Entry Required"
            Exit For
        End If
    Next Cell
End Function

Dave

 

[josullivan601632]

I tried the codes above but I can still save the worksheet with those cells having "Select" in the cell from the droplist! Any ideas what I am doing wrong?

 

[dmt32]

Just tested my suggestion & worked OK with "Select" in range

Have you placed codes where directed?

Dave

 

[mumps]

The macros in Post #6 also worked when tested.