Excel VBA Question on Dates

[atlantis_sweet]

Hi,

I am new to the forum. I am trying to develop a spreadsheet in Excel in which i enter some vales and store it in a different sheet. I am using Excel VBA for that. One of my fields is a Date column. I tried to use the date picker from excel 2003. But when i tried to open it in 2002, it doesnt work. So i am trying to find a combobox or some form of calendar that is compatabile with all the versions of excel. I am using VBA, so any hints or some sample codes should be helpful. Thanks in advance.

[New2vba]

Here's a calendar Macro written in vba by Martin Green (http://www.fontstuff.com). It is also available as an Excel add-in if you visit the site. To activate the calendar simply run the macro through the tools menu. Whichever date you click will be entered in the active cell.

If you visit the above site there is a full tutorial on how the calendar was created - probably worthwhile visiting as there are quite a few useful tutorials and you can also see how to create your own custom version of the calendar.

Does this help?

EDIT - I've been reading a little more myself on fontstuff.com and highly recommend that you visit - there is a section in the tutorial concerning compatibility issues and you can also download a copy of the calendar control for office 97, which is supposed to be compatible with all versions of office. There is also an update concerning an issue with the Macro I have attached.

[DKenny]

Why not have the user enter the value directly into a cell on your worksheet. Then you can Data Validation ( -->>Data-->>Validation ) to ensure that the value entered is a date.

[New2vba]

I don't think data validation is totally fool proof.

For example, consider a spreadhseet set up for dd-mm-yyyy and we want to enter 1st April 2006.

Entering the date (incorrectly) in the format mm-dd-yyyy will be valid, but incorrect - it will actually give us 4th January 2006.

Don't you agree?

[Webtest]

atlantis ...

Backwards compatibility is always a problem. I opened the "Calendar" control in Excel '97 and saved it. I was able to open it and resave it in Excel 2003 and it looks like it works just fine, though I don't really have it doing anything other than sitting on the page looking pretty. As far as I know MS is reasonable at continuing support of old functions, so start with the oldest version you want to support and keep your fingers crossed.

[atlantis_sweet]

Thanks for all the replies.
I wanted to start at 97 and start working backwards, but the problem is that many of the functions are not available at an older version. Another problem is that I started using the VBA textboxes and dropdown lists to enable the user to enter the values.

Thanks

[New2vba]

Thanks for all the replies.
I wanted to start at 97 and start working backwards, but the problem is that many of the functions are not available at an older version.

You want your calendar to work on something older than office 97? I didn't realise there was a version of office older than this (I'm not saying there isn't, but if there is does anybody still use it?)

[atlantis_sweet]

I want my calendar to work in 97,02 and 03 versions of office.

[New2vba]

I'm not sure which calendar control version the calendar attached to my first post uses, but if you visit www.fontstuff.com, and go through the Excel calendar tutoral, it will give you everything you need, including a copy of the office 97 calendar control.

Hope this helps.

[atlantis_sweet]

Thanks for your reply. I will visit the website and see if i can find the calendar.

[james5478]

Hi Everyone,

I am relatively new to the forum and was searching for some discussion on Excel userforms when I came across the thread. I wanted to know if someone could help me?

I have a Userform that I created for users in the HR group to use to enter information on offers of individuals joining the firm, but haven't been entered into the system yet. The form works, but as you know, one needs the validation piece to ensure data integrity.
Below is my code for this userform with red text denoting the txtboxes I need validation on. Could you help me with showing me where to insert the validation code? Thanks!

Option Explicit
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("AcceptedOffers")
'find first empty row in spreadsheet
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a name
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please Enter the Employee Name"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value (Needs to be text, LastName,FirstName with no spaces)
ws.Cells(iRow, 4).Value = Me.cmbDept.Value
ws.Cells(iRow, 5).Value = Me.cmbJobCode.Value
ws.Cells(iRow, 7).Value = Me.cmbSup.Value
ws.Cells(iRow, 8).Value = Me.cmbReg.Value
ws.Cells(iRow, 9).Value = Me.cmbDFA.Value
ws.Cells(iRow, 10).Value = Me.cmbCode.Value
ws.Cells(iRow, 11).Value = Me.cmbEMP.Value
ws.Cells(iRow, 12).Value = Me.cmbRep.Value
ws.Cells(iRow, 13).Value = Me.cmbRec.Value
ws.Cells(iRow, 14).Value = Me.cmbHire.Value
ws.Cells(iRow, 15).Value = Me.cmbPosted.Value
ws.Cells(iRow, 16).Value = Me.txtDatePosted.Value (Needs to be a Date in the form of mm/dd/yyyy)
ws.Cells(iRow, 17).Value = Me.txtAcceptedDate.Value (Needs to be a Date in the form of mm/dd/yyyy)
ws.Cells(iRow, 18).Value = Me.txtEffDate.Value (Needs to be a Date in the form of mm/dd/yyyy)ws.Cells(iRow, 23).Value = Me.txtComments.Value
ws.Cells(iRow, 24).Value = Me.txtSysDate.Value
ws.Cells(iRow, 25).Value = Me.txtUserID.Value
'clear the data
Me.txtName.Value = ""
Me.cmbDept.Value = ""
Me.cmbJobCode.Value = ""
Me.cmbSup.Value = ""
Me.cmbReg.Value = ""
Me.cmbDFA.Value = ""
Me.cmbCode.Value = ""
Me.cmbEMP.Value = ""
Me.cmbRep.Value = ""
Me.cmbRec.Value = ""
Me.cmbHire.Value = ""
Me.cmbPosted.Value = ""
Me.txtDatePosted.Value = ""
Me.txtAcceptedDate.Value = ""
Me.txtEffDate.Value = ""
Me.txtComments.Value = ""
'Me.txtSysDate.Value = ""
'Me.txtUserID.Value = ""
Me.txtName.SetFocus
Unload Me
frmOfferEntry.Show
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
txtSysDate.Value = Now()
End Sub
Private Sub UserForm_Activate()
txtUserID.Value = Environ("USERNAME")
End Sub