How to prompt warning msg if user forgot to fill one cell???

akuccputsedut · #1 (**permalink**) 06-19-07, 04:18

hi..every body wanna to know how to warning user if they forgot to fill form completely...let says one important information that user must fill in but they forgot to fill in when they press button save one warning msg will appear warning that they must fill in the information before proceed and the page back to where they not fill in....and i also want to know source code for button save the function is same like button save as in toolbar...really need help..thank you..

SSpiro · #2 (**permalink**) 06-19-07, 11:21

I also would like to know how to do this!

MikeTheBike · #3 (**permalink**) 06-20-07, 04:18

Hi

It depends on how your workbook/sheets are is arranged and the required data cells are tagged (if at all), ie. with range names, but you can use the ThisWorkbook BeforeSave event, for example

Code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim cel As Range
    
    If Sheets("Sheet1").Range("A5") = "" Then
        MsgBox "Cell A5 in Sheet1 has no data entered!"
    End If
    
    For Each cel In Range("TestRange")
        If cel = "" Then
            MsgBox "Cell " & cel.Address & " in Sheet " & cel.Parent.Name & " has no data entered!"
        End If
    Next cel
End Sub

If you want to stop the book being closed without this, data then you can use the BeforeClose event, ie

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cel As Range
    
    If Sheets("Sheet1").Range("A5") = "" Then
        MsgBox "Cell A5 in Sheet1 has no data entered!"
            Cancel = True
            Exit Sub
    End If
    
    For Each cel In Range("TestRange")
        If cel = "" Then
            MsgBox "Cell " & cel.Address & " in Sheet " & cel.Parent.Name & " has no data entered!"
            Cancel = True
            Exit Sub
        End If
    Next cel
End Sub

I'm not sure if this will do it, but it is a start.

MTB

akuccputsedut · #4 (**permalink**) 06-20-07, 21:17

i'm not really sure if i do the right coding but the result is what i want...the problem is when i click button save my form will ask 'are you sure to save the data if i click yes i want the data save as...ermm...how to explain that...i want the function same like save As in word or excel any microsoft office or other software click yes for save and appear box where we want to save the data and save....that i really want to know....hope you understand...

Private Sub cmdsaveAs_Click()

Dim VarAnswer As String

If Range("C43") = "" Then
MsgBox "Please name your project leader", vbOKOnly, "confirm"
Range("C43").Select
Else
VarAnswer = MsgBox("Are you sure to save the data?", vbYesNo, "Warning")
If VarAnswer = vbNo Then Range("B3").Select
End If

End Sub

MikeTheBike · #5 (**permalink**) 06-21-07, 03:21

Hi

Try typing

GetSaveAsFilename

in the VBA help search and follow the link to the GetSaveAsFilename Method.

If you have problems with it then post back.

HTH

MTB

savbill · #6 (**permalink**) 06-21-07, 19:51

What really helps when creating forms is to use defined Named Ranges for your form fields. A "named range" is a defined name that you set for a cell or range of cells. You can name a range by selecting the cell and then typing a name for it in the name box located in the upper left corner of the Excel window. For example if you name your field "cField1" then you can refer to it like this in your script.

If len(Range(cField1)) = 0 Then
MsgBox "Please name your project leader", vbOKOnly, "confirm"
Range(cField1).Select

The beauty of using the Named Range is once you have your form setup and decide to change something add a row change the loction of a field, the defined name follows the cell. You will not need to update your code to reference the Range because it will still use the same defined name.

akuccputsedut · #7 (**permalink**) 06-21-07, 22:18

ermmm....thank's for ur advice...i appreciate it because i'm still in learning process how to use this macro... thank's :8) by the way mikethebike advice me to use GetSaveAsFileName method in help excel i already try about GetSaveAsFileName method but it's look like wrong...i don't understand example that they give in help excel...can you give me some example more clearly... thank you...

savbill · #8 (**permalink**) 06-21-07, 22:47

Another approach to the file save issue is to save your form as an Excel Template file. This way when the file is opened it will open as a new workbook. When the user attempts to save it they will be presented with the Save as dialog which is the default behavior for a new workbook.

There are several ways of using forms. How you plan to use the form and collect the information will influence the type of processing needed. For example you may want to email the form workbook, or save filled out workbooks on the server. you may want to compile the information in a list or database in which case it may not be necessary to save the form at all. Obviously some methods are going to require more complex processing.

MikeTheBike · #9 (**permalink**) 06-22-07, 08:03

Hi

Quote:

Originally Posted by akuccputsedut

by the way mikethebike advice me to use GetSaveAsFileName method in help excel i already try about GetSaveAsFileName method but it's look like wrong...i don't understand example that they give in help excel...can you give me some example more clearly... thank you...

This is (more or less) the help GetSaveAsFilename Method example

Code:

Sub GetFileToSave()
    Dim fileSaveName As Variant
    
    fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")
    
    If fileSaveName <> False Then
        MsgBox "Save as " & fileSaveName
    End If

End Sub

What exacty do you not understand?

MTB

akuccputsedut · #10 (**permalink**) 06-25-07, 21:03

This is the code that you give
Sub GetFileToSave()
Dim fileSaveName As Variant

fileSaveName = Application.GetSaveAsFilename ( _
fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If

End Sub

i'm not understand the function of this symbol in red color...i already try this code..yes window save as appear when i click button save as but after save there are no file in where i save the file....the save as box like just pretend like it save the file but there are no file save...
this is my code i'm not sure isn't correct or not...

Private Sub cmdsaveAs_Click()
Dim VarAnswer As String
Dim fileSaveName As Variant

If Range("ProjectLeader") = "" Then
MsgBox "Please name your project leader", vbOKOnly, "confirm"
Range("ProjectLeader").Select
End If
If Range("ContactNumber") = "" Then
MsgBox "Please insert your contact number", vbOKOnly, "confirm"
Range("ContactNumber").Select
Else
VarAnswer = MsgBox("Are you sure to save the data?", vbYesNo, "Warning")
If VarAnswer = vbNo Then Range("B3").Select
If VarAnswer = vbYes Then
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If
End If
End If

End Sub

please help...

MikeTheBike · #11 (**permalink**) 06-26-07, 03:47

Hi

This '<>' means Not Equal To, OR more explicitly, is less than OR is greater than.

However, the GetSaveAsFilename method does not acually save anything, just GETS the path/file name as the method name suggests.

I have changed the msgbox at the end of the code to a line that saves the active workbook to the file name selected.

Code:

Private Sub cmdsaveAs_Click()
Dim VarAnswer As String
Dim fileSaveName As Variant


If Range("ProjectLeader") = "" Then
   MsgBox "Please name your project leader", vbOKOnly, "confirm"
   Range("ProjectLeader").Select
End If
If Range("ContactNumber") = "" Then
   MsgBox "Please insert your contact number", vbOKOnly, "confirm"
   Range("ContactNumber").Select
Else
   VarAnswer = MsgBox("Are you sure to save the data?", vbYesNo, "Warning")
   If VarAnswer = vbNo Then Range("B3").Select
      If VarAnswer = vbYes Then
         fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel Files*.xls), *.xls")

         If fileSaveName <> False Then
            ActiveWorkbook.SaveAs FileName:=fileSaveName
         end if
End Sub

HTH

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech