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

 

[Georgiboy]

It seems you have quite a few event codes in one file, are you able to share the file, or a copy of it with sensitive data removed?

 

[shazadhussain88]

It seems you have quite a few event codes in one file, are you able to share the file, or a copy of it with sensitive data removed?

i dont think its possible as if you try and open it on a non-work computer it asks for you to sign in

 

[Georgiboy]

You can try running the below then save the file and reopen and see if it works then:

Sub enEvents()
    Application.EnableEvents = True
End Sub

Failing that try creating a new Excel workbook with only the one piece of event code to test if you have any conflicting code.

Do you have any event code that fires when the workbook is opened?
Do you have any code that contains the line below?

Application.EnableEvents = False

 

[shazadhussain88]

Hi all,

I somehow got this to work in the end thats you all.

I have one more question if I can please,

In my below code it currently works if 'Please Select' is chosen from the cell dropdown options provided to the user. However there also seems to be a blank text option available to them to choose too. (only want them to be able to save if Yes or No is selected)

Would you guys know what I need to edit so I can include both the blank text and 'please select' options in the below code?




Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.UserName <> "Hussein, Shazad (S.)" Then
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

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName <> "Hussein, Shazad (S.)" Then
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

 

[Georgiboy]

Change this line

If rng = "Please Select" Then

To

If rng = "Please Select" or rng = "" Then

 

[shazadhussain88]

Change this line

If rng = "Please Select" Then

To

If rng = "Please Select" or rng = "" Then

Thanks mate you've been a big help.

 

[shazadhussain88]

Sorry guys just come across yet another issue!

My excel file works as it should for other users, however if I send this file to them via email to open on their location of choice, the macros do not work.

They receive a red bar across the top of the document stating 'macros have been blocked for security reasons etc' with the below link provided;

A potentially dangerous macro has been blocked - Microsoft Support

support.microsoft.com

They can manually unblock this file by right clicking the icon and going into properties etc, but I can't expect every user to do this every time.

Is there a way for me to somehow make my file to comply with these security issues by default, so when they open it / share it the doc will still work?

We are all using this file on a work network so unsure why it is restricted for them ?