How do you get a VBA Userform to insert row according to date?

[freddie mitchell]

Hello everyone,

I have been working on a calendar of events worksheet that requires a VBA userform. The form is up and running, sort of, but I haven't managed to crack what code I need to create a new row according to the date the user has entered in the form.

For example, if someone enters 01.11.2020 in the userform's date textbox, I'd like the worksheet to create a new row after October 31st and before November 2nd.

The real kicker is that not every calendar day is represented in the worksheet, just those days that events are happening on (typically 15 to 20 entries per month), so a precise calendar reference brings up an error as it is unlikely that a new event will fall neatly one day after and one day before existing entries.

Thank you to the community for your help with this, especially John W who gave a fantastic piece of code at the start of this difficult but fascinating project!

 

[freddie mitchell]

You're brilliant John, thank you. I should have seen the "r" value would replace "emptyrow", I've learnt so much because of your help!
I'm now trying to get CDate to accept a whole month value. So if someone puts in "January" 2020" it will accept this value. Would MonthName be the correct function to use?

Freddie

 

[John_w]

MonthName converts a month number to its month name.

What date should "January 2020" be converted to? For example, if you want the first day of the month you could use CDate with 1 prepended:

    Dim d As String
    d = "January 2020"
    Debug.Print CDate("1 " & d)

That also works with abbreviated month names, e.g. "Jan 2020".

However, you're asking for problems if you allow the user to enter a date in a text box, as you can't control what they enter and the parsing is difficult.

IMO it's far better to use a pop-up calendar, either the Microsoft Date and Timer Picker Control (mscomct2.ocx) or, if you're not allowed to install that on the computer, a calendar which is made from standard userform controls.

This page has a link to download mscomct2.ocx and instructions for installing it:

How to install mscomct2.ocx file from .cab file (Excel User Form and VBA)

I have an Excel spreadsheet with a user form that uses the calendar control. It works fine on my machine, but others can't use it because they are missing the mscomct2.ocx file. I found where to

stackoverflow.com

This page has a link to download a calendar made from standard userform controls:

Get Userform Calendar Control to give date in a certain format - OzGrid Free Excel/VBA Help Forum

Hi, I have attached the code for a Calendar Control created on a userform I found on the Internet. I don't know how to get it to give me the date in DD/MMM/YYYY format. I have attached my workbook in this post. It's giving me the date in MM/DD/YY…

www.ozgrid.com

 

[freddie mitchell]

Thank you John for this and the advice. I'll take a look at both the downloads and go from there, but you're definitely right that it would be much simpler to only allow users to input the date correctly, rather than a month.

To answer your question, the idea would be that a user could put in January 2020 instead of 01/01/2020. This is required because some events don't have a specific date, but are rather happening across a whole month. I expect your prepended code will work as 'January 2020' would place itself right at the beginning of the month. Will try the code out now

Your help with this has been truly invaluable, thank you so much! After I've fixed the month problem I'm going to call time on adding more things to the workbook, its now up and running and should, fingers crossed, be very useful for the whole team.

Thank you so much for everything

Freddie

 

[freddie mitchell]

Apologies, I meant to add this message to the earlier one, but the forum timed out due to poor internet connection on my part!

With the prepended code, would I add that into the Install row module that we were talking about earlier?

I'm not allowed to install the package on the laptop, but saw your 266 lines of code for a userform calendar, which is amazing!! I'll definitely try it out as it looks like the best option. Would I create a new box on the userform for that calendar you created to work correctly?


Freddie

 

[dmt32]

the idea would be that a user could put in January 2020 instead of 01/01/2020.

using the DateValue Function may do what you want

Dim dte As String
    dte = "January 2020"
   
    MsgBox DateValue(dte)

just be aware that if the function cannot recognise entered text as something that looks like date it will error.

You can read more here:DateValue function (Visual Basic for Applications)

Dave

 

[John_w]

With the prepended code, would I add that into the Install row module that we were talking about earlier?

Here is the modified Insert_Row routine which handles date inputs in the format "Jan 2021" or "January 2021" or "01.01.2021" for the start date. With the first 2 formats the start date is calculated as the first day of the month. With the same inputs for the end date, the last day of the month is calculated.

Public Sub Insert_Row()

    Dim r As Variant
    Dim userformStartDate As Date, userformEndDate As Date
   
    If IsDate("1 " & StartDate.Value) Then
        userformStartDate = CDate("1 " & StartDate.Value)                   'first day of month
    Else
        userformStartDate = CDate(Replace(StartDate.Value, ".", "-"))
    End If
   
    If IsDate("1 " & EndDate.Value) Then
        userformEndDate = CDate("1 " & StartDate.Value)
        userformEndDate = DateSerial(Year(userformEndDate), Month(userformEndDate) + 1, 0)      'last day of month
    Else
        userformEndDate = CDate(Replace(StartDate.Value, ".", "-"))
    End If
   
    With Worksheets("CEE rolling calendar of events")
   
        r = Application.Match(CLng(userformStartDate), .Columns(1), 1)
        If IsError(r) Then
            r = 2
        ElseIf .Cells(r, 1).Value < userformStartDate Then
            r = r + 1
        End If
   
        .Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  
        'Transfer information from userform to new row
       
        .Cells(r, 4).Value = NameTextBox.Value
        .Cells(r, 1).Value = userformStartDate
        .Cells(r, 2).Value = userformEndDate
        .Cells(r, 13).Value = ClientComboBox.Value
        .Cells(r, 5).Value = CountryComboBox.Value
   
    End With

End Sub

You could make the Calendar userform appear when the user clicks in the StartDate textbox using its Enter event handler, like this assuming the Calendar userform is named CalendarFrm.

Private Sub StartDate_Enter()
    CalendarFrm.Show
    EndDate.SetFocus
End Sub

After the user closes the Calendar the code moves the focus to the EndDate textbox so that the Enter event is fired again if they click on the StartDate textbox.

 

[freddie mitchell]

Hi Dave and John,

Thank you for your guidance on fixing the issue and apologies for the delay replying, busy time at work. Both of these options look good, I'll try them out and see how it goes.
Overall the worksheet is well received and is proving useful! Thank you to everyone on this forum. I never knew there was so much to learn about Excel when I started, but I've really enjoyed developing my skills and experimenting with the form.