VBA Question - Do not save 'til all fields are filled on a form

[JrClown]

Hello all, long time no see.

I have created an internal test excel form with 3 fields (Name, email, department) I need to know how in VBA I can make this form not be able to save unless all fields have been filled. I would appreciate it fellas, Thanks.

[/u]p.s. Or a vba code that would not allow the user to leave a field blank.[/u]

"The reward of one duty done is the power to fulfill another"
<%
Jr_Clown@Yahoo.com
%>
​

 

[Goska]

Do you know of a way to detect a &quot;save&quot; command? I have been looking for that info for awhile, and have ahd no luck. If you can help me with that, I have everything else lined up to go.

 

[JrClown]

No I don't have the command, but do you know how not to allow them to leave empty fields??

&quot;The reward of one duty done is the power to fulfill another&quot;
<%
Jr_Clown@Yahoo.com
%>
​

 

[Goska]

Here comes a big mess of code that I use for one of my programs, with error message boxes included.

I set this to a button click command that the user has to hit to continue in my program. If we can find how to do this on the save event, then you are set. If you find anythign on the save event, I am needing for something else.

OverallValid = 0
Call FILEFUNCTIONS.CheckNull(&quot;L13&quot;, Valid, &quot;PROPOSAL NUMBER&quot
'Passes a cell reference (L13) and a Description (PROPOSAL NUMBER) into the subroutine
'Gets a 1(true) or 0(false) value passed out as the Valid integer
OverallValid = OverallValid + Valid
'Adds up all of the valid values. I have 5 things I am checking, so if everything is good, OverAll Valid will be 5 at the end of this run
Call FILEFUNCTIONS.CheckNull(&quot;L39&quot;, Valid, &quot;REVISION LEVEL&quot
OverallValid = OverallValid + Valid
'Does the same thing for the next value I am checking

'My sub is below
Sub CheckNull(CellName As String, Valid As Integer, Field As String)
Dim Pass As String

Pass = Range(CellName)
'Stores the value in the cell that was passed in into the string variable Pass
Valid = 1
If (Pass = &quot;&quot; Or Pass = &quot; &quot Then
Valid = 0
'If that cell is empty by either criteria, then it sets valid to 0(false)
FILEFUNCTIONS.ErrorBox (Field)
'Calls a sub to bring up an error message with the description in it. I can post this sub too if you want it for your app
End If
End Sub


I think this should be able to be modified fairly easily. If you have questions, post again.

 

[JrClown]

Thanks Goska, I'll let u know later this afternoon. I appreciate your input bud.

&quot;The reward of one duty done is the power to fulfill another&quot;
<%
Jr_Clown@Yahoo.com
%>
​

 

[segmentationfault]

This is essentially answered in a nearby thread. Stick this in ThisWorkbook.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If (One or more fields are empty, etc.) Then
Cancel = True
End If
End Sub

 

[JrClown]

Thanx segmentationfault

&quot;The reward of one duty done is the power to fulfill another&quot;
<%
Jr_Clown@Yahoo.com
%>
​