Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2007
    Posts
    35

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

    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..
    **akuccputsedut**
    Intelligent is Me!!But a little confusing
    sometimes needs HELP!!

  2. #2
    Join Date
    Jan 2007
    Posts
    4
    I also would like to know how to do this!

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  4. #4
    Join Date
    Jun 2007
    Posts
    35

    I'm not sure...

    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
    **akuccputsedut**
    Intelligent is Me!!But a little confusing
    sometimes needs HELP!!

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

  7. #7
    Join Date
    Jun 2007
    Posts
    35
    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...
    **akuccputsedut**
    Intelligent is Me!!But a little confusing
    sometimes needs HELP!!

  8. #8
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  10. #10
    Join Date
    Jun 2007
    Posts
    35

    Not Understand...

    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...
    **akuccputsedut**
    Intelligent is Me!!But a little confusing
    sometimes needs HELP!!

  11. #11
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •