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? Think calendar view


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 :( :confused: :mad:


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


Thanks in advance!


/Alex

[sjeter]

Dim LWeekday As Integer
Dim weekday As String
Dim mnth As String

'use this line of code to get your month from the selected date.
mnth = Monthname(Month("12/31/2001")) ''replace this date with your selected date variable

'use this code to get the weekday.
LWeekday = Weekday("12/31/2001", vbSunday) 'replace this date with your selected date variable
Select Case LWeekday
Case 1
weekday = "Sunday"
Case 2
weekday = "Monday"
Case 3
weekday = "Tuesday"
Case 4
weekday = "Wednesday"
Case 5
weekday = "Thursday"
Case 6
weekday = "Friday"
Case 7
weekday = "Saturday"
End Select