Need help!
Posted by Becky on January 07, 2002 8:04 AM
Need help. I am making a form in Excel for my vendors. I have learned how to put a button on the bottom that when hit will send them to a "save as" screen, and I have also learned how to make the form so it has required field entries. The problem I am having is that now when I hit the "save" button it will send warning that not all questions were answered, BUT it will still send them to the "save as" screen. How do I make it so that the form cannot be saved unless all required fields are filled out??
Posted by Dank on January 07, 2002 9:04 AM
Hello,
You need to carry out some validation before using Application.SaveAs or whatever it is you're using. E.g.
Private Sub CommandButton1_Click()
Dim s As String
If Me.TextBox1 = "" Then
MsgBox "Textbox 1 must contain a value"
Exit Sub
ElseIf Me.TextBox2 = "" Then
MsgBox "Textbox 2 must contain a value"
Exit Sub
ElseIf Me.TextBox3 = "" Then
MsgBox "Textbox 3 must contain a value"
Exit Sub
End If
s = Application.GetSaveAsFilename
End Sub
It's always easier if you post your code and you'll normally get more responses because people will copy and paste the code into a module, run in and normally idenitfy the problem very quickly.
HTH,
Daniel.
Posted by Becky on January 07, 2002 10:06 AM
I tried this, and it's not what I'm looking for. I am not using a textbox, I think that might be the problem. I'll paste what code I have.. maybe you can help me figure out what I'm doing wrong??
Here it is:
Sub Save()
'
' Save Macro
' Macro recorded 12/17/01 by TMS User
'
'Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("A7") = "" Then
MsgBox ("Must answer all questions")
Range("A7").Select
Else
If Range("A9") = "" Then
MsgBox ("Must answer all questions")
Range("A9").Select
Else
If Range("A11") = "" Then
MsgBox ("Must answer all questions")
Range("A11").Select
Else
If Range("A13") = "" Then
MsgBox ("Must answer all questions")
Range("A13").Select
Else
If Range("A15") = "" Then
MsgBox ("Must answer all questions")
Range("A15").Select
Else
End If
End If
End If
End If
End If
ActiveWindow.ScrollRow = 1
Application.Dialogs(xlDialogSaveAs).Show
On Error GoTo File_Exists
ActiveSheet.SaveAs FileName:="C:\My Documents\Emily\" & Whatever & ".xls"
On Error GoTo 0
Exit Sub
File_Exists:
MsgBox "File name already exists, choose another"
Range("B1").Select
Selection.ClearContents
Range("B2").Select
Selection.ClearContents
Range("B3").Select
Selection.ClearContents
Range("B4").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("D4").Select
Selection.ClearContents
Range("A7:D7").Select
Selection.ClearContents
Range("A9:D9").Select
Selection.ClearContents
Range("A11:D11").Select
Selection.ClearContents
Range("A13:D13").Select
Selection.ClearContents
ActiveWindow.ScrollRow = 12
Range("A15:D15").Select
Selection.ClearContents
Range("C23").Select
Selection.ClearContents
Range("D26").Select
Selection.ClearContents
Range("D27").Select
Selection.ClearContents
ActiveWindow.ScrollRow = 1
Range("B1").Select
End Sub
Let me know if you can.. thanks!
-Becky
Posted by Dank on January 07, 2002 11:55 AM
Save Macro Macro recorded 12/17/01 by TMS User Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Hello,
How about this? I've taken the liberty of making your code slightly sleeker e.g you don't need to have this:-
Range("B1").Select
Selection.ClearContents
you can just use
Range("B1").ClearContents.
This is faster because it doesn't select an item before processing it.
Sub Save()
'
' Save Macro
' Macro recorded 12/17/01 by TMS User
'
'Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("A7") = "" Then
MsgBox ("Must answer all questions")
Range("A7").Select
Exit Sub
ElseIf Range("A9") = "" Then
MsgBox ("Must answer all questions")
Range("A9").Select
Exit Sub
ElseIf Range("A11") = "" Then
MsgBox ("Must answer all questions")
Range("A11").Select
Exit Sub
ElseIf Range("A13") = "" Then
MsgBox ("Must answer all questions")
Range("A13").Select
Exit Sub
ElseIf Range("A15") = "" Then
MsgBox ("Must answer all questions")
Range("A15").Select
Exit Sub
End If
ActiveWindow.ScrollRow = 1
Application.Dialogs(xlDialogSaveAs).Show
On Error GoTo File_Exists
ActiveSheet.SaveAs Filename:="C:\My Documents\Emily\" & Whatever & ".xls"
On Error GoTo 0
Exit Sub
File_Exists:
MsgBox "File name already exists, choose another"
Range("B1:b4").ClearContents
Range("D1").ClearContents
Range("D4").ClearContents
Range("A7:D7").ClearContents
Range("A9:D9").ClearContents
Range("A11:D11").ClearContents
Range("A13:D13").ClearContents
Range("A15:D15").ClearContents
Range("C23").ClearContents
Range("D26").ClearContents
Range("D27").ClearContents
Range("B1").Select
End Sub
HTH,
Daniel.
Posted by Becky on January 07, 2002 12:16 PM
That works well, but now when I hit the "save as" button it will save the file as a different name, but also save it as ".xls" with no name, and doesn't clear the fields like starting over on a new form.
Have any ideas?? Save Macro Macro recorded 12/17/01 by TMS User Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Sub Save() Save Macro Macro recorded 12/17/01 by TMS User Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) MsgBox ("Must answer all questions") Range("A7").Select If Range("A9") = "" Then MsgBox ("Must answer all questions") Range("A9").Select If Range("A11") = "" Then MsgBox ("Must answer all questions") Range("A11").Select If Range("A13") = "" Then MsgBox ("Must answer all questions") Range("A13").Select If Range("A15") = "" Then MsgBox ("Must answer all questions") Range("A15").Select Application.Dialogs(xlDialogSaveAs).Show On Error GoTo File_Exists ActiveSheet.SaveAs Filename:="C:\My Documents\Emily\" & Whatever & ".xls" Range("B1:b4").ClearContents Range("D1").ClearContents Range("D4").ClearContents Range("A7:D7").ClearContents Range("A9:D9").ClearContents Range("A11:D11").ClearContents Range("A13:D13").ClearContents Range("A15:D15").ClearContents Range("C23").ClearContents Range("D26").ClearContents Range("D27").ClearContents End Sub
