automatically fill cell with end of date monthly

**DonkeyOte** · 04-27-2010, 04:32 AM

Without knowing how you're controlling data entry on the UserForm (or indeed if) - perhaps:

Sheets("form").Copy After:=Sheets(Sheets.Count)
With ActiveSheet
    .Name = UserForm1.TextBox1.Text
    .Cells(1, "A") = DateAdd("m", 1, "01-" & Left(.Name, 3) & "-" & Right(.Name, 2)) - 1
End With

though as implied you will need to validate the TextBox string first - so as to ensure consistency.

**herukuncahyono** · 04-27-2010, 05:07 AM

hi DonkeyOte,

for more details, I attach an example.I hope you more easily understand what I mean.

regards
herukuncahyono

**DonkeyOte** · 04-27-2010, 05:10 AM

...and the problem is what exactly ?

I was simply making the point that unless you control the entry text the code could debug - eg I type in "apple" and hit OK.

**herukuncahyono** · 04-27-2010, 05:33 AM

did you've seen my last post with sample of userform?
if I use the code,the date is not in accordance with the sheet name.

thanks
herukuncahyono

**DonkeyOte** · 04-27-2010, 06:24 AM

Works for me I'm afraid (on both UK & Indonesian Regional Config.) - if I enter Jan10 or Jan-10 etc I get 31-Jan-10 as the date in A1 on the newly created sheet.
(above still ignores issue re: incompatible entry)

**herukuncahyono** · 04-27-2010, 08:38 PM

hi DonkeyOte,
I’m sorry for taking so long to write back and for my careless, I tried the code on another computer and it works.
This means about computer settings.

Thank you for your help and I appreciate much
regards
herukuncahyono