msg box to display specials
 

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

msg box to display specials
 

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 Auto_Open()
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" wrote in message
...
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

msg box to display specials
 

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.

"Chip Pearson" wrote:

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 Auto_Open()
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" wrote in message
...
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