I wish to put add a "Date" box to a form, which will then populate
sheet. I wish it to default to the day the entry is made, but need th
option of a pop up calender to change this if needed. Any code help i
appreciated as I'm new to VBA.

I have also noticed that as data is entered (in say A1,2,4 etc), i
moves down the sheet to find the next available row. As it does this i
does not copy the formulas (on A3,5 etc), which are used to analyse th
input results. How can I solve this?

Many Thanks

And

--
weedevi
-----------------------------------------------------------------------
weedevil's Profile: http://www.excelforum.com/member.php...fo&userid=1680
View this thread: http://www.excelforum.com/showthread.php?threadid=32056

This link should help with the calendar question.

http://www.fontstuff.com/vba/vbatut07.htm

"weedevil" wrote:


I wish to put add a "Date" box to a form, which will then populate a
sheet. I wish it to default to the day the entry is made, but need the
option of a pop up calender to change this if needed. Any code help is
appreciated as I'm new to VBA.

I have also noticed that as data is entered (in say A1,2,4 etc), it
moves down the sheet to find the next available row. As it does this it
does not copy the formulas (on A3,5 etc), which are used to analyse the
input results. How can I solve this?

Many Thanks

Andy


--
weedevil
------------------------------------------------------------------------
weedevil's Profile: http://www.excelforum.com/member.php...o&userid=16807
View this thread: http://www.excelforum.com/showthread...hreadid=320567

Andy

I've used the code below and it seems to work OK, but why not just
present a calendar that defaults to todays date?

To do that you will need to add the ActiveX calendar control to a form
(right click on Toolbox-Additional Controls). Then on the form's code
sheet, add something like:

Private Sub UserForm_Initialize()
Calendar1 = Date
End Sub

Private Sub CommandButton1_Click()
ActiveCell = Format(Calendar1.Value, "m/d/yy")
Unload Me
End Sub

If you're dead set on having it first show as a textbox, you could
always load a textbox using the "=Date" and provide a button to change
the date by having the button change the visibility property of the
calendar control and point the calendar to the textbox:

Private Sub UserForm_Initialize()
Textbox1 = Date
Calendar1 = Date
Calendar1.Visible = False
End Sub

Private Sub CommandButton1_Click()
Calendar1.Visible = True
End Sub

Private Sub CommandButton2_Click()
Textbox1 = Calendar1.Value
End Sub

Private Sub CommandButton3_Click()
ActiveCell = Format(Textbox1.Value, "m/d/yy")
Unload Me
End Sub

Hope that helps.

I'm not sure I quite follow your second question.

Dave Freidel


On Fri, 26 Nov 2004 07:46:14 -0600, weedevil
wrote:


I wish to put add a "Date" box to a form, which will then populate a
sheet. I wish it to default to the day the entry is made, but need the
option of a pop up calender to change this if needed. Any code help is
appreciated as I'm new to VBA.

I have also noticed that as data is entered (in say A1,2,4 etc), it
moves down the sheet to find the next available row. As it does this it
does not copy the formulas (on A3,5 etc), which are used to analyse the
input results. How can I solve this?

Many Thanks

Andy