Saving a worksheet with a condition
 

Hi,
I have the following macro for a button that saves a file in two locations.
I want to put a condition between the code that would create a pop up message
saying that Date cell (Which is in C6) must be filled before saving the file.

My code is as follows:


Private Sub CmdSubmit_Click()

If MsgBox("Submit: '" & Sheet1.Cells(1, 1) & "?", vbYesNo Or vbQuestion _
Or vbDefaultButton2) = vbYes Then


Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Census\Archive\" & Sheet1.Cells(1, 1).Value &
".xls"
Application.DisplayAlerts = True

Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Census\Batch_Files\" & Sheet1.Cells(1, 2).Value
& ".xls"
Application.DisplayAlerts = True

MsgBox ("File Submitted Successfully")

Application.ActiveWorkbook.Close

End If

End Sub

Saving a worksheet with a condition
 

Near the top:

Private Sub CmdSubmit_Click()

if isempty(sheet99.range("C6").value) then
msgbox "Please fill in that date"
exit sub
end if

'...rest of code

====

Ps. Remember that if sheet1.cells(1,1) contains a date, you'll want to format
it nicely.

ThisWorkbook.SaveAs _
"C:\Census\Archive\" & format(Sheet1.Cells(1, 1).Value,"yyyymmdd") & ".xls"

Since you can't have /'s in the file name (well in the wintel world).

yasser wrote:

Hi,
I have the following macro for a button that saves a file in two locations.
I want to put a condition between the code that would create a pop up message
saying that Date cell (Which is in C6) must be filled before saving the file.

My code is as follows:

Private Sub CmdSubmit_Click()

If MsgBox("Submit: '" & Sheet1.Cells(1, 1) & "?", vbYesNo Or vbQuestion _
Or vbDefaultButton2) = vbYes Then


Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Census\Archive\" & Sheet1.Cells(1, 1).Value &
".xls"
Application.DisplayAlerts = True

Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Census\Batch_Files\" & Sheet1.Cells(1, 2).Value
& ".xls"
Application.DisplayAlerts = True

MsgBox ("File Submitted Successfully")

Application.ActiveWorkbook.Close

End If

End Sub

--

Dave Peterson

Saving a worksheet with a condition
 

Fantastic! - It works like a charm.

"Dave Peterson" wrote:

Near the top:

Private Sub CmdSubmit_Click()

if isempty(sheet99.range("C6").value) then
msgbox "Please fill in that date"
exit sub
end if

'...rest of code

====

Ps. Remember that if sheet1.cells(1,1) contains a date, you'll want to format
it nicely.

ThisWorkbook.SaveAs _
"C:\Census\Archive\" & format(Sheet1.Cells(1, 1).Value,"yyyymmdd") & ".xls"

Since you can't have /'s in the file name (well in the wintel world).

yasser wrote:

Hi,
I have the following macro for a button that saves a file in two locations.
I want to put a condition between the code that would create a pop up message
saying that Date cell (Which is in C6) must be filled before saving the file.

My code is as follows:

Private Sub CmdSubmit_Click()

If MsgBox("Submit: '" & Sheet1.Cells(1, 1) & "?", vbYesNo Or vbQuestion _
Or vbDefaultButton2) = vbYes Then


Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Census\Archive\" & Sheet1.Cells(1, 1).Value &
".xls"
Application.DisplayAlerts = True

Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Census\Batch_Files\" & Sheet1.Cells(1, 2).Value
& ".xls"
Application.DisplayAlerts = True

MsgBox ("File Submitted Successfully")

Application.ActiveWorkbook.Close

End If

End Sub

--

Dave Peterson