×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Problem with Format of Date Using Spinner.
2

Problem with Format of Date Using Spinner.

Problem with Format of Date Using Spinner.

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

I successfully use the following code to vary the date:

CODE -->

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

RE: Problem with Format of Date Using Spinner.

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.

Quote (MSDN)

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.

RE: Problem with Format of Date Using Spinner.

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

RE: Problem with Format of Date Using Spinner.

Quote (PBAPaul)

should give Friday 19 April 2018

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

You may try something like this:

CODE

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.

RE: Problem with Format of Date Using Spinner.

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.

CODE

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 

RE: Problem with Format of Date Using Spinner.

(OP)
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

RE: Problem with Format of Date Using Spinner.

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.

RE: Problem with Format of Date Using Spinner.

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: TheDate = TheDate + SpinButton1.SmallChange

combo

RE: Problem with Format of Date Using Spinner.

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)"

RE: Problem with Format of Date Using Spinner.

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.a... . 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close