automatically fill cell with end of date monthly

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.