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!

 

[josullivan601632]

Hi everyone, I been working really hard to make this worksheet work, and be able to post a positive response and thank everyone for the help...but no. I have double checked all codes and followed instructions, sometimes it works but then for some reason reverts and allows me to save again. I have recreated the spreadsheet and lists (to which the drop down lists and VLOOKUP's refer to in case there was any corruption going on), checking the codes work at different stages of the recreation but seems to be temperamental... or I am missing something! Any ideas would be a massive help!

 

[dmt32]

Suggest you place a copy of your workbook (with any sensitive data removed) in a dropbox & provide a link to it here - this will give forum better opportunity to understand why any of suggestions offered are not fully working for you.

Dave

 

[josullivan601632]

Book1Jo.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

The macro is looking for the text "Select" in the range F55:F58. If it finds "Select" anywhere in that range, the user will be prompted to select "Yes" or "No" and will not be allowed to save or close the file. All the cells in the range "F55:F58" in the file you posted say "Yes" so since the text "Select" is not present, the user is allowed to save or close the file. Try changing one or more of the "Yes" values to "Select" and then try to save or close the file.

 

[josullivan601632]

Thank you for your help. If I change range to Select, I can save instead of being prevented.

Book1Jo version 2.xlsm

Shared with Dropbox

www.dropbox.com

 

[dmt32]

Not surprised solutions have not been working for you - in earlier post you stated

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

Form you are using is a in a sheet named "Sheet1" which is no real issue but what is there are no values in ranges you specified earlier in this thread which solution was based on. In addition to this, I note that some of the data validation values in your form have, in addition to Yes & No, option "Na" included?

Dave

 

[mumps]

Try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim Rng As Range
    For Each Rng In Sheets("Sheet1").Range("F55:F69")
        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("Sheet1").Range("F55:F69")
        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

 

[josullivan601632]

Apologies for the confusion, I had ended up re-doing the worksheet in case it had got corrupted and didn't name it the same but had changed the VBA code wording. Anyway, don't know what i was doing wrong but above works a treat. I AM REALLY SORRY FOR BEING SO THICK, I now have the perfect sheet which works great so want to save the template for staff to work on, but I can't save the template because the above code works so great. How do I do this... how do I have a template for users to start with, with all the "select" boxes prompting the user to actually select an option, but then prevents a save (overwriting master) with a select option not choosen! I need a lie down

 

[mumps]

Do the following:
In the "Developer" tab showing in your top menu, click the "Design Mode" button in the "Controls" group. You will see that it changes color to orange. What this does is that it disables the macros temporarily. Once you have clicked the "Design Mode" button, you can now save the file. After saving the file, click "Design Mode" button again to enable the macros.

 

[josullivan601632]

Thank you so much for all the help. I have done that. Have a great weekend everyone.