ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   oh dear - why isn't my simple IF working? (https://www.excelbanter.com/excel-programming/356406-oh-dear-why-isnt-my-simple-if-working.html)

drucey[_12_]
oh dear - why isn't my simple IF working?
 

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 ol
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 ", _
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



-------------------

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=52387

Gary''s Student
oh dear - why isn't my simple IF working?
 
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 ", _
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
oh dear - why isn't my simple IF working?
 
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" wrote in
message ...

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 ", _
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[_13_]
oh dear - why isn't my simple IF working?
 

thanks chap

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

--
druce
-----------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255
View this thread: http://www.excelforum.com/showthread.php?threadid=52387

drucey[_14_]
oh dear - why isn't my simple IF working?
 

oooh second answer worked fine! thank you so much!


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=523878


All times are GMT +1. The time now is 01:54 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com