oh dear - why isn't my simple IF working?

[drucey]

It's an order form.

If someone saves it as a "draft", cell A101 has a "1" value in.

Now, my macro below is supposed to:

Save as a new name, email it, and IF A101 > 0, then delete the old sheet.

Please Login or Register  to view this content.

[Gary''s Student]

1. try setting Draft=ws1.Range("A101").Value
2. MSGBOX(Draft) just before the kill to make sure it has been set
--
Gary's Student


"drucey" wrote:

>
> It's an order form.
>
> If someone saves it as a "draft", cell A101 has a "1" value in.
>
> Now, my macro below is supposed to:
>
> Save as a new name, email it, and IF A101 > 0, then delete the old
> sheet.
>
>
> Code:
> --------------------
> Sub Save_Click()
> Dim lStr_CurFileName As String
> Dim ws1 As Worksheet
> Dim wb1 As Workbook
>
> Set ws1 = Sheets("Sheet1")
> sent = ws1.Range("A100")
> Draft = ws1.Range("A101")
>
>
> Range("T8").Value = Date
> Application.DisplayAlerts = False
> On Error Resume Next
> myarray = Array("Completed Orders", "Partially Arrived Orders", "Draft Orders", "Placed Orders")
> Sheets(myarray).Delete
> ThisFile = Range("T3").Value
> ThisDept = Range("S3").Value
> ActiveWorkbook.SaveAs Filename:="J:\Purchase Orders\FM\Order " & ThisDept & ThisFile
> Dim NextNo
>
> If Draft > 0 Then
> Kill lStr_CurFileName
> End If
>
>
> InvNo = ws1.Range("A100")
> NextNo = 1
> Range("A100").Select
>
> ActiveCell.Formula = InvNo + NextNo
> Range("A1").Select
> ActiveWorkbook.Save
> With ActiveWorkbook
> .SendMail Recipients:="[email protected]", _
> Subject:="Purchase Order " & Format(Date, "dd/mmm/yy")
> Application.DisplayAlerts = True
> End With
>
> Range("C46:F47").Select
> Selection.ClearContents
> ActiveSheet.Shapes("Picture 10").Select
> Selection.Delete
> ActiveSheet.Shapes("Picture 105").Select
> Selection.Delete
> Range("E45:F48").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> Selection.Borders(xlEdgeTop).LineStyle = xlNone
> Selection.Borders(xlEdgeBottom).LineStyle = xlNone
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> ActiveSheet.Shapes("Picture 106").Select
> Selection.ShapeRange.IncrementLeft -1
> Selection.ShapeRange.IncrementTop -530
> Range("C46").Select
> ActiveCell.FormulaR1C1 = "Save Delivery"
> Range("C47").Select
> ActiveCell.FormulaR1C1 = "Information"
> Range("C46:C47").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .VerticalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
>
>
>
>
> Range("A1").Select
> Sheets("Sheet1").Activate
> End Sub
>
>
>
> --------------------
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523878
>
>

[Tom Ogilvy]

change

Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")



to

lStr_CurFileName = ActiveWorkbook.FullName
Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")

so the kill command knows what to delete.

That would be my guess.

--
Regards,
Tom Ogilvy


"drucey" <[email protected]> wrote in
message news:[email protected]...
>
> It's an order form.
>
> If someone saves it as a "draft", cell A101 has a "1" value in.
>
> Now, my macro below is supposed to:
>
> Save as a new name, email it, and IF A101 > 0, then delete the old
> sheet.
>
>
> Code:
> --------------------
> Sub Save_Click()
> Dim lStr_CurFileName As String
> Dim ws1 As Worksheet
> Dim wb1 As Workbook
>
> Set ws1 = Sheets("Sheet1")
> sent = ws1.Range("A100")
> Draft = ws1.Range("A101")
>
>
> Range("T8").Value = Date
> Application.DisplayAlerts = False
> On Error Resume Next
> myarray = Array("Completed Orders", "Partially Arrived Orders", "Draft
Orders", "Placed Orders")
> Sheets(myarray).Delete
> ThisFile = Range("T3").Value
> ThisDept = Range("S3").Value
> ActiveWorkbook.SaveAs Filename:="J:\Purchase Orders\FM\Order " &
ThisDept & ThisFile
> Dim NextNo
>
> If Draft > 0 Then
> Kill lStr_CurFileName
> End If
>
>
> InvNo = ws1.Range("A100")
> NextNo = 1
> Range("A100").Select
>
> ActiveCell.Formula = InvNo + NextNo
> Range("A1").Select
> ActiveWorkbook.Save
> With ActiveWorkbook
> .SendMail Recipients:="[email protected]", _
> Subject:="Purchase Order " & Format(Date, "dd/mmm/yy")
> Application.DisplayAlerts = True
> End With
>
> Range("C46:F47").Select
> Selection.ClearContents
> ActiveSheet.Shapes("Picture 10").Select
> Selection.Delete
> ActiveSheet.Shapes("Picture 105").Select
> Selection.Delete
> Range("E45:F48").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> Selection.Borders(xlEdgeTop).LineStyle = xlNone
> Selection.Borders(xlEdgeBottom).LineStyle = xlNone
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
> ActiveSheet.Shapes("Picture 106").Select
> Selection.ShapeRange.IncrementLeft -1
> Selection.ShapeRange.IncrementTop -530
> Range("C46").Select
> ActiveCell.FormulaR1C1 = "Save Delivery"
> Range("C47").Select
> ActiveCell.FormulaR1C1 = "Information"
> Range("C46:C47").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .VerticalAlignment = xlCenter
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
>
>
>
>
> Range("A1").Select
> Sheets("Sheet1").Activate
> End Sub
>
>
>
> --------------------
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=523878
>

[drucey]

thanks chap

but still not working - it comes up with the prompt box (after the draft setting) but it's a blank box

[drucey]

oooh second answer worked fine! thank you so much!