Calendar Problem

[Hazel]

Hi Everybody

I have added a calendar to a userform and used Ron DB's code to get it
working e.g

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)

ActiveCell.NumberFormat = "dd/mm/yy"
ActiveCell.Select
End Sub

My problem is the date enters the ActiveCell OK (UK DATE) 01/07/07 no
problem and in a Listbox on viewing the row it reads as 01/07/07 however on
highlighting the Listbox Row in the TextBox assigned to the date it is the
serial number. Have tried Value - Text - formatting everyway but which all to
no avail. If I enter the date manually in the Cell and use Text as the format
in the Textbox everything is Ok. Any idea's to help solve this would be much
appreciated.

 

[Joel]

Don't format the cell. Convert date to a string and write the string to the
cell

Private Sub Calendar1_Click()
ActiveCell.Value = Now()

ActiveCell = Format(ActiveCell, "dd/mm/yy")
ActiveCell.Select
End Sub

 

[Hazel]

Hi Joel
Thanks that solved my problem -- changed the Now() to Calendar1 and it
worked fine -- can I just ask you another question ? I am entering dates from
the same calendar in to a couple of TextBoxes e.g.

Private Sub Add1_Click()
UserForm1.Tb1.Text = UserForm2.Calendar1.Value
End Sub

When I use it for the above at the same time it enters the date in of course
which ever Cell is active on the sheet is there any way of stopping this
happening or would another Calender be the answer?

 

[Joel]

There isn't enough info to answer your question. You could add an if
statement into the macro to add the date under certain conditions. The code
below is only a sample and not to be the real code.

Private Sub Add1_Click()
if (UserForm2.Calendar1.Value = 1) then
UserForm1.Tb1.Text = UserForm2.Calendar1.Value
end if
End Sub

 

[Hazel]

Hi Joel

thanks again for the help - I don't really know how much more info I can
give you? will probably have to use spinbuttons or another calendar for the
other textboxes its now getting a shade to complicated for me. All my lot in
this office want - is for me to try and make things as easy as possible for
them when they have to enter data in to the workbook.