Problem with Format of Date Using Spinner. 2

[PBAPaul]

I have a userform which uses various text boxes operated via spinbuttons.

I successfully use the following code to vary the date:

Private Sub SpinButton1_SpinDown()
TextBox1.Text = Format(CDate(TextBox1.Text) - CDate(SpinButton1.SmallChange), "dd mmmm yyyy")
End Sub

Private Sub SpinButton1_SpinUp()
TextBox1.Text = Format(CDate(TextBox1.Text) + CDate(SpinButton1.SmallChange), "dd mmmm yyyy")
End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = Format(Date, "dd mmmm yyyy")
End Sub

I thought that life would be easier for the users if I also gave the weekday in the test box and so changed the format of "dd mmmm yyyy" which produces 19 April 2018 to "dddd dd mmmm yyyy" which should give Friday 19 April 2018.. Everything starts off OK, but when I try to spin up or down, I get the message "Run-time error 13.Type mismatch" Yet I have only changed the format. Can anyone please explain why I get the error message!

Thanks

 

[mintjulep]

I guess that CDate() doesn't know what to do with "Friday" in the string.

Possibly DateValue() is smarter.

If not you might need an additional helper function to rip off the day from the string so that it more closely resembles a "date expression" meeting MS's rather vague definition.

Date data type
A data type used to store dates and times as a real number. Date variables are stored as 64-bit (8-byte) numbers. The value to the left of the decimal represents a date, and the value to the right of the decimal represents a time.

date expression
Any expression that can be interpreted as a date, including date literals, numbers that look like dates, strings that look like dates, and dates returned from functions. A date expression is limited to numbers or strings, in any combination, that can represent a date from January 1, 100 - December 31, 9999.

Dates are stored as part of a real number. Values to the left of the decimal represent the date; values to the right of the decimal represent the time. Negative numbers represent dates prior to December 30, 1899.

date literal
Any sequence of characters with a valid format that is surrounded by number signs ( # ). Valid formats include the date format specified by the locale settings for your code or the universal date format.

For example, #12/31/92# is the date literal that represents December 31, 1992, where English-U.S. is the locale setting for your application. Use date literals to maximize portability across national languages.

 

[mintjulep]

Alternately, rather than a textbox, have you considered a Calendar control or DatePicker?

 

[Andrzejek]

should give Friday 19 April 2018

Well, today is 19th and it is not Friday. (is it.... )

You may try something like this:

Option Explicit

Private Sub UserForm_Initialize()
TextBox1.Tag = Format(Date, "dd mmmm yyyy")
TextBox1.Text = WeekdayName(Weekday(CDate(TextBox1.Tag))) & " " & TextBox1.Tag
End Sub

Private Sub SpinButton1_SpinDown()
Call Go(-1)
End Sub

Private Sub SpinButton1_SpinUp()
Call Go(1)
End Sub

Private Sub Go(ByRef intX As Integer)
TextBox1.Tag = Format(CDate(TextBox1.Tag) + intX, "dd mmmm yyyy")
TextBox1.Text = WeekdayName(Weekday(CDate(TextBox1.Tag))) & " " & TextBox1.Tag
End Sub

---- Andy

There is a great need for a sarcasm font.

 

[DjangMan]

I would strongly consider adding a property to the userform that holds the date. Your spin buttons would work upon that variable and when the variable changes you update the text box. It separates your presentation from the representation of the data.

Option Explicit

Private m_dtTheDate As Date

Public Property Get TheDate() As Date
    TheDate = m_dtTheDate
End Property

Public Property Let TheDate(ByVal dtNewValue As Date)
    m_dtTheDate = dtNewValue
    TextBox1.Text = Format(m_dtTheDate, "dd mmmm yyyy")
End Property

Private Sub SpinButton1_SpinDown()
    TheDate = DateAdd("d", -SpinButton1.SmallChange, TheDate)
End Sub

Private Sub SpinButton1_SpinUp()
    TheDate = DateAdd("d", SpinButton1.SmallChange, TheDate)
End Sub

 

[PBAPaul]

Thank you all for your very helpful replies.

mintjulep: I was wondering that I might have to change CDate() to a different function like DateValue().
mintjulep: I am using Excel 2010. MS very kindly stopped offering DatePicker etc in this version onwards.
Andrzejek: I tried your code which worked perfectly! Yes the 19th was a Thursday not Friday!
DjangMan: I tried your code but had problems getting it to work as I wanted.

Again, thanks to everybody for their help.

Paul

 

[Andrzejek]

Text box suggests (to me) that you can type something in it.
Consider changing it to a Label


---- Andy

There is a great need for a sarcasm font.

 

[combo]

PBAPaul,
DatePicker was never a part of VBA controls, it is an item in VB6 common controls.
I agree with DjangMan to keep date as explicit Date type variable instead of converting text to date and rely on VBA conversions. If his solution does not work for you, what is the error? If you use the date in multiple places, use rather simple variable to store the date. Also, you can change date directly: [tt]TheDate = TheDate + SpinButton1.SmallChange[/tt]

combo

 

[mintjulep]

I have Excel 2013 and a datepicker like control is available.

From the VBA editor
Tools|Additional Controls...

Check "Microsoft Date and Time Picker Control 6.0 (SP4)

 

[combo]

Common controls can be installed and registered by other application. In this case it is possible to add them to the toolbox, as many other components. You can download sp6 from here:

https://www.microsoft.com/en-ie/download/details.aspx?id=10019

. People use them in VBA applications (if available), but MS clearly state that the license is for:
1. Customers who are Microsoft Visual Basic 6.0 and/or Microsoft Visual Studio 6.0 licensees.
2. Customers who are not licensees of Microsoft Visual Basic 6.0 nor Microsoft Visual Studio 6.0 but are using older versions of the two Common Controls.
So there are availability and licensing issues.
VBA has its own set of controls in MSForms library, some office specific applications controls can be added (as RefEdit for excel or outlook components).


combo