New Excel question/challenge for you

[Guest awarner (MVP)]

Completely different subject to the last excel question.

Ok the problem is I've written a macro with the VBE to show a calender to which a date can be selected and then entered into the required filed. (my first ever macro and technically built with help)

Now what I need is to have some other fileds auto fill. Each new field will have one of the following,

Day of the week

Month

Year

I could just create more drop down boxes but to save time auto fill with the informatino needed would save a lot of time.

It would also be less messy :D

any ideas? i've done a lot of googling which has helped a lot but I'm now stuck again :/

Cheers

[Guest chucky.egg]

How about this

In the cell(s) that you want to show the Day Of Week:

=WEEKDAY($A$1,1)

And, if you want the text-version of the day (eg Sunday), format the cells with a Custom format of "DDDD" (without the quotes) In the cell(s) that you want to show the Month:

=MONTH($A$1)

And, if you want the text version (eg May), format the cells with a Custom format of "MMMM" (without the quotes) In the cell(s) that you want to show the Year:

=YEAR($A$1)

All of that assumes that your source date (the one you choose from the Calendar selector) is stored in cell A1 ($A$1 in the formulas)

Edited May 26, 2008 by chucky.egg

[Guest awarner (MVP)]

EDIT I was reading it wrong. it does appear to work correctly, cheers :D

Thanks for the reply but I do not think that will work unfortunately as the source date is not stored in a cell afaik.

The code in VB is as follows

Private Sub Calendar1_DblClick() 


ActiveCell.Value = Calendar1.Value 

UserForm1.Hide 

Unload UserForm1 

End Sub[/code]




After that the following code was written to make the calender appear.



[code]Private Sub Calendar1_DblClick() 


ActiveCell.Value = Calendar1.Value 

UserForm1.Hide 

Unload UserForm1 

End Sub

Then Ctrl+D is the designated shortcut to make the calender appear.

So what happens is that you choose the cell you need the date entered and pressing Ctrl+D makes a small calender popup window appear which you can then select the date you want. Once selected the date is completed in that cell.

What I need is to able to do this operation once and have multiple cells filled with the selected date.

[Guest awarner (MVP)]

Ok must be doing something stupid, when clicking on the calender the weekday is one out for some strange reason?

[Guest chucky.egg]

Ahh, I see what you mean.

The other option is to add code to your existing function to fill the other cells. You'll still need to know, relative to the selected cell, where the Month, Year and Weekday values should be stored though.

Replace your current code in the calendar1 section with this code on a copy of your spreadsheet (it will change values in the three cells immediately to the right of your selected cell):

Private Sub Calendar1_DblClick()

	ActiveCell.Value = Calendar1.Value


	'First number is the number of ROWS to move down

	'Second number is the number of COLUMNS to move across

	ActiveCell.Offset(0, 1).Value = WEEKDAY(ActiveCell.Value,1)

	ActiveCell.Offset(0, 2).Value = ActiveCell.Value

	ActiveCell.Offset(0, 3).Value = Year(ActiveCell.Value)


	UserForm1.Hide

	Unload UserForm1


End Sub

You'll still need the cell formatting for Weekday and Month to display the resulting numbers in text form (Sunday instead of 1, and so on) as in my earlier reply

EDIT: Corrected code for Month to fix the wrong Month (January) being identified

Edited May 27, 2008 by chucky.egg

[Guest chucky.egg]

Just corrected my code, see above

[Guest chucky.egg]

What date have you set, and what day does it think it is?

If weekday is one out it may be the different ways VBA can handles days.

By default (and with the ",1" option) they start on Sundays, if you change it to ",2" it starts the week on Monday. Don't ask me why.

=WEEKDAY(ActiveCell.Value, 2)  'Start the week on a Monday

[Guest awarner (MVP)]

I've managed to sort it all out now :D

First off thank you for your time and I will play with those codes shortly.

Secondly I'm a complete MUPPET as I made a VERY stoopid mistake by having the cells I wanted validated with a dropdown list but also have error alert activated.

Finally I must be sad as I'm getting to enjoy using excel and looking forward to a few more challenges which I'm sure to find. :(

[Guest awarner (MVP)]

Just as a subnote I found this site really useful.

http://www.mrexcel.com/forum/index.php