Date List

[monagan]

I need a drop down list whether validation or combobox to take the current month and list all the days in the month and the last two days from last month. When the month changes, I need it to do the same thing with the new month.

Any Ideas?

Thank You in advance

 

[cLFlaVA]

Well, if you're looking to do it in VBA - I have some code that may help you.

1. My code was for 6 drop-down boxes, with 2 month drop-downs, 2 year drop-downs, and 2 day drop-downs. The code I pasted below shows you how to fill both year and month drop-downs, along with one day drop-down. When the doc opened, it would default these boxes to the start and end date of the current month.

2. You'll need to call the FillMonthBoxes and FillYearBoxes on the Open event of the document you're using.

3. You'll need to create the drop-down boxes.

Hope this helps.

Sub fillMonthBoxes()
    
    Dim monthArray(11) As String
    
    Dim i As Integer
    Dim m As Integer
    
    m = Month(Now())
    
    If m = 1 Then
        m = 13
    End If
    
    For i = 1 To 12 Step 1
        monthArray(i - 1) = MonthName(i)
    Next i
    
    'fill the month comboboxes and set value
    cmbToMonth.List() = monthArray
    cmbToMonth.Value = MonthName(m - 1)
    
    cmbFromMonth.List() = monthArray
    cmbFromMonth.Value = MonthName(m - 1)
    
End Sub

Sub fillYearBoxes()
    
    Dim yearArray(4) As Integer
    
    Dim i As Integer
    Dim y As Integer
    
    y = Year(Now()) - 2
    
    'populate combo boxes
    For i = 0 To 4 Step 1
        cmbFromYear.AddItem (y + i)
        cmbToYear.AddItem (y + i)
    Next i

    'set default value
    cmbFromYear.Value = Year(Now())
    cmbToYear.Value = Year(Now())
    
End Sub

Sub fillFromDayBox()
    Dim i As Integer
    Dim startDay As Integer
    Dim endDay As Integer
    Dim nextMonth As Integer
    Dim theYear As Integer
    Dim theMonth As Integer
    Dim monthString As String

    theMonth = cmbFromMonth.ListIndex + 1
    theYear = cmbFromYear.Value
    
    'set first and last days of month
    nextMonth = (theMonth + 1)
    If nextMonth = 13 Then
        nextMonth = 1
        theYear = theYear + 1
    End If
    startDay = 1
    
    monthString = CStr(nextMonth) & "-" & CStr(startDay) & "-" & CStr(theYear)
    endDay = Day(DateAdd("d", -1, monthString))
    
    'remove all days from box
    Do While cmbFromDay.ListCount > 0
        cmbFromDay.RemoveItem (0)
    Loop
    
    'enter days into box
    For i = startDay To endDay Step 1
        cmbFromDay.AddItem (i)
    Next i
    
    cmbFromDay.ListRows = endDay
    cmbFromDay.Value = startDay
End Sub

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[monagan]

Thanks a lot.
I'll check it out.

I appreciate it

 

[monagan]

I'm trying to run the code, and it gets to cmbToMonth and say that the variable isn't defined. I can't understand why?

 

[monagan]

i figured it out, at the top
dim cmbToMonth as Combobox

 

[cLFlaVA]

cmbToMonth, cmbToDay, cmbToYear, cmbFromMonth, cmbFromYear, cmbFromDay are all the names of Comboboxes that I created in my excel workbook.

If you have comboboxes, you'll need to change the names in the code to the names of your comboboxes.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[monagan]

I added the comboBoxes but it's not getting it

 

[cLFlaVA]

What application are you using?

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[monagan]

excel 2000 (atl+f11)

 

[cLFlaVA]

If you have create a combobox named cmbFromMonth in your actual worksheet, and the code I posted is in the code for that worksheet (Alt+F11, double-click the sheet name on the left), then I'm not sure why you wouldn't be getting this to operate.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[monagan]

Ok, It's getting better.

I'm at the point where I'm calling them,

But the "sub or function is not defined

 

[cLFlaVA]

For testing purposes: add a Command Button. Double Click it. In Your CommandButton1_Click() event, place the following code:

Call fillMonthBoxes
Call fillYearBoxes

Also, I didn't claim this code would work as-is. There may very well be some tweaking needed to satisfy your needs.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[monagan]

Yes I understand.
I was just working on filling the boxes and then work from there.

I used the sheet1.fillMonthBoxes to call it and seems to work fine

 

[SkipVought]

Hi,

Why not put a Calendar Control Object on your sheet, via Control Toolbox - More Controls.

You can POSITION and MAKE VISIBLE using the Worksheet_SelectionChange event. The user just selects a valid date. In the Calendar1_Click event, set the Visible property FALSE.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[monagan]

Hey Thanks Skip.

How come excel shuts down everytime I click on it?

And how to I have the calender put the date in a cell?

 

[SkipVought]

Private Sub Calendar1_Click()
   With Calendar1
      .TopLeftCell.Value = .Value
      .Visible = False
   End With
End Sub

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[monagan]

I put in that code but it is still not even getting to it becuase I get an error and excel closes

 

[SkipVought]

What version Excel & what operating system?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[monagan]

2000, xp home

 

[SkipVought]

...and you have toggled OFF the Design Mode?

How does the Calendar COntrol Object become Visible?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}