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

freddie mitchell

New Member
Joined
Apr 14, 2011
Messages
33
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

New Member
Joined
Apr 14, 2011
Messages
33
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,722
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:
VBA Code:
    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:


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

 

freddie mitchell

New Member
Joined
Apr 14, 2011
Messages
33
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

New Member
Joined
Apr 14, 2011
Messages
33
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,490
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,722
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.

VBA Code:
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.

VBA Code:
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

New Member
Joined
Apr 14, 2011
Messages
33
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,078
Messages
5,628,536
Members
416,323
Latest member
65563

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top