msg box to display specials

[pswanie]

hey guys

we got a number of specials runing at once but they dont all start or end on
the same day. now i need a msg box to pop up when the workbook are opened
and tell the user what specials are currently running and when it started and
when its going to end.

so i guess i need a msg box to pop up with a label and below that three in
put boxes and a okay button. in the three boxes we can type a short
description of the specials and next to it when it start and next to that
when it ends.

on the page it self (called "inputpage") i need the discription in column c
down and column d will be the start date and column e the end date. and if
possible if the system can see when it ends and the day after it end delete
the entry. if the system cant see and delete a "expired" special i can work
with a delete button. so u pick/choose the one thats expired and hit delete.

i did not use userforms to much yet but are starting so if a userform will
do the trick thats cool to

thanx

happy 2008 to all

Phillip

 

[Chip Pearson]

You can try something like the following. Change the worksheet and range
reference to the first cell that has the specials text. The code will check
all dates in D and E until a blank cell is found in C.

Sub Autpen()
Dim S As String
Dim R As Range
'<<< CHANGE SHEET AND RANGE AS REQUIRED
Set R = ThisWorkbook.Worksheets("Sheet1").Range("C1")

Do Until R.Value = vbNullString ' loop until empty cell in C
If R(1, 2).Value <= Now Then ' Start Date
If R(1, 3).Value >= Now Then ' End Date
S = S & R.Text & vbCrLf
End If
End If
Set R = R(2, 1) ' move down in C
Loop

If S <> vbNullString Then
MsgBox S, vbOKOnly, "Specials"
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[pswanie]

thanx chip that will work but...

on that i need 3 input boxes to add the name, start date, end date

and how do i program the in put boxes to put it in the next open line
respectively in colums c and d and e.

and if possible to delete expired specials.