Put a messagebox in my formula!!!
Posted by Message Box on September 25, 2001 12:53 AM
I've got a formula in which I want that when cell "D11" and cell "G11" are not empty that an messagebox will appear which you can answer with yes or no and in case it's yes then it end the if and continue's doing the rest of the formula and if no then it's supposed to exit the sub. The formula I've got is:
Sub DeleteOrders()
Dim Var1 As Date
Dim Var2 As Date
Dim StartLoop As Long
Dim RowLoop As Long
Dim Check As Date
Dim Orders As Worksheet
Dim Verw As Worksheet
Set Orders = Workbooks("Orderoverzicht.xls").Worksheets("Orders")
Set Verw = Workbooks("Orderoverzicht.xls").Worksheets("Verwijderen")
Var1 = Worksheets("Verwijderen").Range("D11").Value
Var2 = Worksheets("Verwijderen").Range("G11").Value
With Verw
If .Range("D11") = "" And .Range("G11") = "" Then
Display = MsgBox("Geef op vanaf welke datum tot en met welke datum u de orders wilt verwijderen.", 48, "Datum ontbreekt")
.Range("D11").Select
Exit Sub
Else
End If
If Not .Range("D11") Is Nothing And .Range("G11") = "" Then
Display = MsgBox("U moet opgeven tot en met welke datum u de orders wilt verwijderen.", 48, "Datum ontbreekt")
.Range("G11").Select
Exit Sub
Else
End If
If Not .Range("G11") Is Nothing And .Range("D11") = "" Then
Display = MsgBox("U moet een datum opgeven vanaf wanneer u de orders wilt verwijderen.", 48, "Datum ontbreekt")
.Range("D11").Select
Exit Sub
Else
End If
End With
With Orders
.Range("CZ1").ClearContents
.Range("CZ5").ClearContents
.Range("DB5").ClearContents
.Range("DD1").ClearContents
.Range("DE5").ClearContents
.Range("DG5").ClearContents
End With
StartLoop = Worksheets("Orders").Columns(3).End(xlDown).Row
For RowLoop = StartLoop To 1 Step -1
If IsDate(Range("'Orders'!C" & RowLoop).Value) Then
Check = Range("'Orders'!C" & RowLoop).Value
If Check >= Var1 And Check <= Var2 Then
Sheets("Orders").Rows(RowLoop & ":" & RowLoop).Delete Shift:=xlUp
End If
End If
Next RowLoop
With Orders
.Columns("CZ:DG").ClearContents
.Range("CZ1") = "Debiteurennummer:"
.Range("CZ1").Font.Bold = True
.Range("CZ5") = "Datum"
.Range("CZ5").Font.Bold = True
.Range("DB5") = "Volgnummer"
.Range("DB5").Font.Bold = True
.Range("DD1") = "Naam:"
.Range("DD1").Font.Bold = True
.Range("DE5") = "Tot. Prijs Gulden"
.Range("DE5").Font.Bold = True
.Range("DG5") = "Tot. Prijs Euro"
.Range("DG5").Font.Bold = True
End With
With Worksheets("Orders").CommandButton1
.Caption = "Andere Klant"
.Left = 478
.Top = 25
.Height = 24
End With
With Worksheets("Orders").CommandButton2
.Caption = "Orderbon"
.Left = 478
.Top = 51
.Height = 24
End With
Display = MsgBox("Verwijderen van orders is compleet", 48, "Orders verwijderd")
End Sub
I hope someone can tell me where to put the messagebox and how to? Hope to see an reply soon!
Thanks!