Load Week Ending Date From Numbers List

**Reafidy** · Jan 19th 2007

Hi All

I have a list of entrys (41 in total so far) from cell A1 down starting with the value 1. This entry represents the week ending 04/06/06. ie each entry represents a date for the week ending. So cell A2 will contain the no 2 and represent the week ending 11/06/06. Cell A3 will contain the no 3 and represent the week ending 18/06/06. It goes in order right down to number 41.

I have a userform, what I want to do is have a combobox load all the entrys in as dates, not as the entry no. Then when I choose a week in the combobox it will load the appropriate entry no into another textbox?

I dont want to add the dates to the spreadsheet.

Simple? not for me!

All Help Appreciated.

GeorgS · Jan 19th 2007

Re: Load Week Ending Date From Numbers List

Reafidy,

Code

Private Sub UserForm_Activate()
    Call LoadCombo
End Sub


Private Function LoadCombo() As Boolean
Dim startDate As Date
startDate = CDate("19/1/2007")
'Abover: Get this from your worksheet


For x = 1 To 41
    Me.ComboBox1.AddItem CStr(DateAdd("d", 7 * x, startDate))
Next


End Function


Private Sub ComboBox1_Change()
    Me.TextBox1.Text = Me.ComboBox1
End Sub

Display More

Need an explanation?

G.

**Reafidy** · Jan 19th 2007

Re: Load Week Ending Date From Numbers List

Hi G,

Excellent, I understand your code perfectly.

However the hard part for me is that when updating the combobox, I want the entry no. (some number between 1 to 41) to appear in the textbox not the date.

Thanks.

GeorgS · Jan 19th 2007

Re: Load Week Ending Date From Numbers List

Reafidy,

Code

Private Function LoadCombo() As Boolean
Dim startDate As Date
startDate = CDate("19/1/2007")


Dim MyArray(41, 1) As String
'Above: Get this from your worksheet


Me.ComboBox1.ColumnCount = 2
Me.ComboBox1.ColumnWidths = "0;55"


For x = 0 To 40
    MyArray(x, 0) = x
    MyArray(x, 1) = CStr(DateAdd("d", 7 * x, startDate))
Next


    Me.ComboBox1.List() = MyArray


End Function


Private Sub ComboBox1_Change()
    Me.TextBox1.Text = Me.ComboBox1.Column(0)
End Sub

Display More

Note that this is Zero based.
i.e. add 1 to it.

Edit: Previous answer only accommodated consecutive weeks in your list.
G.

**Reafidy** · Jan 19th 2007

Re: Load Week Ending Date From Numbers List

Excellent.

Now I should have figured that out on my own! Its been a long day.

Thanks G, Legend.

Sam

Load Week Ending Date From Numbers List

Participate now!

Share

Users Viewing This Thread