Code works but can't save template!

[shazadhussain88]

Hi all,

Hopefully you guys can help a real newbie with an issue im having with a Macro.

I have a excel file which i would like to share with my team to populate. There is a field where either a Yes or No option must be filled. The default field is 'Please Select'. I have added the below code which prevents the users from saving the file without entering either Yes or No.
Looks to be working fine, however now I am unsure how to save this template to distribute to the team!, as it keeps asking me to select yes or no. I want to save it as 'please select' and share with the team who from this point must select either yes or no. hopefully this makes sense!

ANy help would be appreciated !

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Sheets("DCR").Range("E18:F18")
        If rng = "Please 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("DCR").Range("E18:F18")
        If rng = "Please 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

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags in post #1 for you this time.

 

[shazadhussain88]

Do you have the above code in the ThisWorkbook module?

Do you have 'Please Select' on sheet 'DCR' in one of the cells E18:F18 without any trailing/ leading spaces?

Hi sorry for the delay

Please see below - I can share the document with you if you would be ok with that? seriously appreciate your help with this!

 

[Georgiboy]

If you can share the workbok, it would be easier to look at.

 

[shazadhussain88]

If you can share the workbok, it would be easier to look at.

would you know how I can share an excel file on here? seem to be only able to upload images for some reason?

 

[Georgiboy]

You can use something like OneDrive, the forum itself does not accept attachments.

If you use OneDrive, make sure to share the file access.

 

[shazadhussain88]

ah ****, its on a work drive which I dont think I will have access to share

 

[Georgiboy]

You would also need to update the below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Application.UserName <> "Hussein, Shazad (S.)" Then ' change the Joe Bloggs to your username
        Application.ScreenUpdating = False
        Dim rng As Range
        For Each rng In Sheets("DCR").Range("E18:F18")
            If rng = "Please 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 If
End Sub

 

[shazadhussain88]

yep did that

 

[Georgiboy]

so ended up with the below, and I was able to save it. however when any other user opens it they seem to be able to save it without any issue?

Do they have the same username as you?
Are they enableing macro's when they open the workbook? Check if thire macro's are disabled.

 

[shazadhussain88]

Do they have the same username as you?
Are they enableing macro's when they open the workbook? Check if thire macro's are disabled.

They have a different user name and are enabling the macros but still not working for some reason.....

Would you know a different code that I could try to acheive the same intended outcome? i.e. I just want to prevent anyone being able to save the document without answering either Yes or No

I am stumped!