How to prevent from saving if some cells + textbox + combobox are blank ?

francois.lemarie

Board Regular
Joined
Mar 24, 2010
Messages
54
Hello
I am preparing a template with different sheets that users will have to fill in. I use cells and 2 activeX controls (textboxes and comboboxes):

I would like the users cannot be able to save the template if the following elements are blank:
-specific cells (eg. A1 in sheet1, A2 in sheet2)
-textboxes (eg Textbox1 in sheet1, Textbox2 in sheet2)
-comboboxes (eg. Combobox1 in sheet 1, Combobox2 in sheet2)

However, these specific cells, textbox and comboboxes blank must appear in blank when they will open it. So it implies to be able to save a file with it blank ONLY IF YOU KNOW THE PASSWORD (As the creator of the template, I would be the only person to know it). If you don't know the password you have to complete cells, textboxes and comboboxes to be able to save.


I thought about a message before saving for example : "Some elements are blank, please check it all, or enter the password to save with blanks" and a little space to put the password. If the password is not the good one another message would appear : "Please put correct password or fill the blanks". I am a newbie so if you have another idea, please be free to purpose !

Would anybody be kind to tell me how to do it ? ?

Regards,

Francois
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Something based on this ... ?
Code:
'===============================================================
'- CHECK CELL BEFORE SAVE
'- goes into ThisWorkbook code module
'===============================================================
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Worksheets("Sheet1").Range("A1").Value = "" Then
        MsgBox ("Cannot save yet. A1 empty.")
        Cancel = True
    End If
End Sub
'---------------------------------------------------------------
 
Upvote 0
Completely BrianB !

I have not trew it (did not have time up to know) but I will in the next few days but it will probably work.

When I will have trown I will give you feedback.

Thanks a lot !

Best regards.
 
Upvote 0
Another thing : how could we adapt the macro of Brian in order the user can choose to enter a password or to cancel if he does not know it, BEFORE the message "Can't Save, A1 is Blank". Appears ?

=> If you put the correct password, it must be possible to save with blanks.
 
Upvote 0
'===============================================================
'- CHECK CELL BEFORE SAVE
'- goes into ThisWorkbook code module
'===============================================================
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Worksheets("Sheet1").Range("A1").Value = "" Then
        rsp = InputBox("A1 is empty" & vbCr _
            & "Please enter password to save anyway")
        If rsp = "password" Then      ' NB. case sensitive
            ThisWorkbook.Save
        Else
            Cancel = True
        End If
    End If
End Sub
'---------------------------------------------------------------
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top