date drop down box in vba
 

Hello,
I am new to vba for excel. Ive tried finding the solution in many books but
was unable.
I need to create date drop down box that will defualt to the current day,
but give the user the option to select another day up to at least 32 days
before the current day. I also need the selected day to appear in seveal
cells in the workbook. And if it is possible to show the day of week next to
the selected date.
Format mm/dd/yyyy

Thank for your Help

Jim

date drop down box in vba
 

Here is some code

Sub Jim()
With Range("D1:D12").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$M$1:$M$33"
End With
Range("M1").Formula = "=TODAY()"
Range("M2").Formula = "=M1-1"
Range("M2").AutoFill Destination:=Range("M2:M33"), Type:=xlFillDefault
Range("M1:M33").NumberFormat = "ddd dd mmm yyyy"
Columns("D:D").ColumnWidth = 16
Columns("M:M").ColumnWidth = 16
Range("D1:D12") = Date
Range("D1:d12").NumberFormat = "ddd dd mmm yyyy"
Range("D1").Select
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fuzzy31" wrote in message
...
Hello,
I am new to vba for excel. Ive tried finding the solution in many books
but
was unable.
I need to create date drop down box that will defualt to the current day,
but give the user the option to select another day up to at least 32 days
before the current day. I also need the selected day to appear in seveal
cells in the workbook. And if it is possible to show the day of week next
to
the selected date.
Format mm/dd/yyyy

Thank for your Help

Jim

date drop down box in vba
 

Hi Bob
thanks for writing back.
I'm having trouble with this line
..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
I'm getting a compile error: syntax error
any help would be great
"Bob Phillips" wrote:

Here is some code

Sub Jim()
With Range("D1:D12").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$M$1:$M$33"
End With
Range("M1").Formula = "=TODAY()"
Range("M2").Formula = "=M1-1"
Range("M2").AutoFill Destination:=Range("M2:M33"), Type:=xlFillDefault
Range("M1:M33").NumberFormat = "ddd dd mmm yyyy"
Columns("D:D").ColumnWidth = 16
Columns("M:M").ColumnWidth = 16
Range("D1:D12") = Date
Range("D1:d12").NumberFormat = "ddd dd mmm yyyy"
Range("D1").Select
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fuzzy31" wrote in message
...
Hello,
I am new to vba for excel. Ive tried finding the solution in many books
but
was unable.
I need to create date drop down box that will defualt to the current day,
but give the user the option to select another day up to at least 32 days
before the current day. I also need the selected day to appear in seveal
cells in the workbook. And if it is possible to show the day of week next
to
the selected date.
Format mm/dd/yyyy

Thank for your Help

Jim

date drop down box in vba
 

Thanks for your help Bob, I figured out what I did wrong with your code. It
was late & I wasn't thinking

"Bob Phillips" wrote:

Here is some code

Sub Jim()
With Range("D1:D12").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$M$1:$M$33"
End With
Range("M1").Formula = "=TODAY()"
Range("M2").Formula = "=M1-1"
Range("M2").AutoFill Destination:=Range("M2:M33"), Type:=xlFillDefault
Range("M1:M33").NumberFormat = "ddd dd mmm yyyy"
Columns("D:D").ColumnWidth = 16
Columns("M:M").ColumnWidth = 16
Range("D1:D12") = Date
Range("D1:d12").NumberFormat = "ddd dd mmm yyyy"
Range("D1").Select
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fuzzy31" wrote in message
...
Hello,
I am new to vba for excel. Ive tried finding the solution in many books
but
was unable.
I need to create date drop down box that will defualt to the current day,
but give the user the option to select another day up to at least 32 days
before the current day. I also need the selected day to appear in seveal
cells in the workbook. And if it is possible to show the day of week next
to
the selected date.
Format mm/dd/yyyy

Thank for your Help

Jim

date drop down box in vba
 

The old NG wrap-around I see. I try to account for it, but I get caught
frequently ... grrrr

Bob


"Fuzzy31" wrote in message
...
Thanks for your help Bob, I figured out what I did wrong with your code.
It
was late & I wasn't thinking

"Bob Phillips" wrote:

Here is some code

Sub Jim()
With Range("D1:D12").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=$M$1:$M$33"
End With
Range("M1").Formula = "=TODAY()"
Range("M2").Formula = "=M1-1"
Range("M2").AutoFill Destination:=Range("M2:M33"),
Type:=xlFillDefault
Range("M1:M33").NumberFormat = "ddd dd mmm yyyy"
Columns("D:D").ColumnWidth = 16
Columns("M:M").ColumnWidth = 16
Range("D1:D12") = Date
Range("D1:d12").NumberFormat = "ddd dd mmm yyyy"
Range("D1").Select
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fuzzy31" wrote in message
...
Hello,
I am new to vba for excel. Ive tried finding the solution in many
books
but
was unable.
I need to create date drop down box that will defualt to the current
day,
but give the user the option to select another day up to at least 32
days
before the current day. I also need the selected day to appear in
seveal
cells in the workbook. And if it is possible to show the day of week
next
to
the selected date.
Format mm/dd/yyyy

Thank for your Help

Jim