VBA:comboboxes to present same date in different formats...ADAPT WITH CHANGE IN DATE

[AlexDobbin]

Hi all,


I'm very new to VBA and excel development, so please take that into consideration as you read on.


I'm trying to create a work form for a database that (should) collect various information in comboboxes, including the date, the weekday, and the month... Note: most of the time, the data will be inserted the day after it's been collected. So, I wanted to create one combobox for the date, one for the weekday, and one for the month, because I want columns for each of these in the database (If this is not necessary/efficient, please help! ) .


Here's what I've got so far:

    'worksheet setup
    Dim ws As Worksheet
    Set ws = Worksheets("LookupList")
    
    'Date dropdown setup
    Dim cDateToday As Range
    
    For Each cDateToday In ws.Range("DateList")
        With Me.cboDate
            .AddItem cDateToday.Value
        End With
    Next cDateToday
    
    'If today is Monday, then set date to last friday, if any other weekday, set it to day before that
    If Weekday(Date - 1) <> 2 Then
        Me.cboDate.Value = Format(DateAdd("D", -Weekday(Date) - 1, Date), "Medium Date")
    Else
        Me.cboDate.Value = Format(Date - 1, "Medium Date")
    End If

Problem #1: Right now, I am getting the date values from the list DateList in the worksheet LookupList...is there a better way of doing this?


Problem #2: When the user form is run, the correct date and format shows up. However, if I change the date, the dropdown list in the combobox starts from the initial date in given list...is there anyway to bring the list closer to the current date?


Problem #3: I want the weekday and month values to be directly correlated to the date value discussed above, so hypothetically when the user form is run the correct date, weekday, and month all show up (based on the same date value), then if I change the date, the weekday and month automatically update. Is this possible, if so how?


The reason I want this functionality is so that on a Monday, I can run the work form and it will automatically have Friday's date, weekday, month info as it opens. When I'm done with Friday, I can run it again, switch the date to the Saturday's date (weekday and month automatically update), then repeat for Sunday.


I have tried running the same code for the weekday and month as I have for the date, and they work until the date is changed. Once the date is changed, I have to manually change the weekday, which leads to format change, and same for month, which all leads to


Again, I am very new to vba and don't know much about it all and appreciate any/all help!


Thanks in advance!


/Alex

 

[AlphaFrog]

Problem #1: Right now, I am getting the date values from the list DateList in the worksheet LookupList...is there a better way of doing this?


Problem #2: When the user form is run, the correct date and format shows up. However, if I change the date, the dropdown list in the combobox starts from the initial date in given list...is there anyway to bring the list closer to the current date?


Problem #3: I want the weekday and month values to be directly correlated to the date value discussed above, so hypothetically when the user form is run the correct date, weekday, and month all show up (based on the same date value), then if I change the date, the weekday and month automatically update. Is this possible, if so how?

1.) What dates are on the list?

2.) Again, you need to better explain the date list and what exactly do "closer" mean.

3) Use the Combobox's _Change event procedure to set the weekday and month values based on the changed combobox value e.g.;

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cboDate_Change()
    [color=darkblue]If[/color] IsDate(cboDate.Value) [color=darkblue]Then[/color]
        cboMonth.Value = Format(cboDate.Value, "mmmm")
        cboWeekday.Value = Format(cboDate.Value, "dddd")
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[AlexDobbin]

Thank you for your reply!!

1.) What dates are on the list?

The dates start October 1st, 2014, and go on everyday from then until 2020. I want to be able to use this everyday for the next few years.

2.) Again, you need to better explain the date list and what exactly do "closer" mean.

The date list is a list of dates ranging from October 1st, 2014 - January 1st 2020. That is not a specific end of the time period, but I want to be able to use this user form everyday for an 'infinite' amount of time. By 'closer', I mean that once I click the dropdown menu, it automatically starts at the 'start' of the list which is October 1st.

Let's say it shows April 14th, but I want the 13th. If I click the drop down, it'll automatically start at Oct 1st, and then I have to scroll down to the 13th. Ideally, it would be nice to have a "calendar" type view where if it displays the 14th, and I click the dropdown it might show the 14th as the first option, followed by the 15th, 16th, etc. or something similar where the dates displayed in the dropdown are relatively "close" to the initial date displayed.

I hope I'm clear enough...err..sorry.

3) Use the Combobox's _Change event procedure to set the weekday and month values based on the changed combobox value e.g.;

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cboDate_Change()
    [COLOR=darkblue]If[/COLOR] IsDate(cboDate.Value) [COLOR=darkblue]Then[/COLOR]
        cboMonth.Value = Format(cboDate.Value, "mmmm")
        cboWeekday.Value = Format(cboDate.Value, "dddd")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

This worked perfectly!! Thank you so much! It was exactly what I meant and needed!

 

[AlphaFrog]

There is a calendar-like DatePicker control. Do a web search for something like Excel VBA userform Datepicker and you will find many Guides\Tutorials\Videos.


This will populate the cboDate combobox with Date minus 10 days for 365 days

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    
    [COLOR=darkblue]Dim[/COLOR] d [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    
    [COLOR=green]'A year's worth of dates starting ten days ago.[/COLOR]
    [COLOR=darkblue]For[/COLOR] d = [COLOR=darkblue]Date[/COLOR] - 10 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]Date[/COLOR] + 355
        Me.cboDate.AddItem Format(d, "Medium [COLOR=darkblue]Date[/COLOR]")
    [COLOR=darkblue]Next[/COLOR] d
    
    [COLOR=green]'If today is Monday, then set date to last friday, if any other weekday, set it to day before that[/COLOR]
    [COLOR=darkblue]If[/COLOR] Weekday(Date - 1) <> 2 [COLOR=darkblue]Then[/COLOR]
        Me.cboDate.Value = Format(DateAdd("D", -Weekday(Date) - 1, Date), "Medium Date")
    [COLOR=darkblue]Else[/COLOR]
        Me.cboDate.Value = Format(Date - 1, "Medium Date")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[AlexDobbin]

There is a calendar-like DatePicker control. Do a web search for something like Excel VBA userform Datepicker and you will find many Guides\Tutorials\Videos.


This will populate the cboDate combobox with Date minus 10 days for 365 days

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    
    [COLOR=darkblue]Dim[/COLOR] d [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    
    [COLOR=green]'A year's worth of dates starting ten days ago.[/COLOR]
    [COLOR=darkblue]For[/COLOR] d = [COLOR=darkblue]Date[/COLOR] - 10 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]Date[/COLOR] + 355
        Me.cboDate.AddItem Format(d, "Medium [COLOR=darkblue]Date[/COLOR]")
    [COLOR=darkblue]Next[/COLOR] d
    
    [COLOR=green]'If today is Monday, then set date to last friday, if any other weekday, set it to day before that[/COLOR]
    [COLOR=darkblue]If[/COLOR] Weekday(Date - 1) <> 2 [COLOR=darkblue]Then[/COLOR]
        Me.cboDate.Value = Format(DateAdd("D", -Weekday(Date) - 1, Date), "Medium Date")
    [COLOR=darkblue]Else[/COLOR]
        Me.cboDate.Value = Format(Date - 1, "Medium Date")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

THANK YOU! Perfect instructions, solved my problems and taught me some!