MrExcel Publishing
Your One Stop for Excel Tips & Solutions

need more help!


Posted by Becky on January 07, 2002 11:11 AM

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?? I am posting the code that I have already below so if anyone knows the answer, pleas refer to that.

Thanks!

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



Posted by Dank on January 07, 2002 11:59 AM

No need to post again - see my earlier answer..

Save Macro Macro recorded 12/17/01 by TMS User Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)