Why does the date change from "January 07" to a numercial value of "39038 "

[Corey]

Private Sub UserForm_Initialize()
With Sheet1
Me.ComboBox1.RowSource = "B17:B28"
Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy")
End With
End Sub

 

[Scott]

Hmm, I would have thought that 39038 would correspond with November 06.

The date is stored as a decimal number, where 1 is the equivalent of 1
day. 1 represents 1/1/1900, 2 represents 1/2/1900, 39038 represents
11/17/2006.

Hours and minutes are represented by the fractional part of the number.

Are you sure it wasn't 39088? (That would correspond to January 07)

Scott

 

[Corey]

Actually that was a typo on my part the actual value(Unless it is now
different) is:
39083

 

[Tom Ogilvy]

I would not use a rowsource or a controlsource with dates, but

Private Sub UserForm_Initialize()
With Sheet1
Me.ComboBox1.RowSource = ""
for each cell in .Range("B17:B28")
me.combobox1.AddItem = cell.Text
Next
End With
me.combobox1.ListIndex = 0 ' or whatever you want
End Sub


Private Sub Combobox1_Click()
with Sheet1
.Range("F11").Value = Me.Combobox1.Value
End With
End Sub

 

[Corey]

Tom,
I get an error in this line:

Private Sub UserForm_Initialize()
With Sheet1
Me.ComboBox1.RowSource = ""
for each cell in .Range("B17:B28")
me.combobox1.AddItem = cell.Text' <======= Expected Function or Variable
Next
End With
me.combobox1.ListIndex = 0 ' or whatever you want
End Sub


ALSO


Was this:
Private Sub Combobox1_Click()
with Sheet1
.Range("F11").Value = Me.Combobox1.Value
End With
End Sub

Suppose to be:


Private Sub Combobox1_Click()
with Sheet1
.Range("B17:B28").Value = Me.Combobox1.Value
End With
End Sub
??

 

[Corey]

The range of cell values that popultae the combobox is:
January 07
........
December 07

These are the values i get:

January 07 = 39083
February 07 = 39114
March 07= 39142


The actual difference is the days in the month.

By why is it so.... ?

 

[Tom Ogilvy]

me.combobox1.AddItem = cell.Text'

should be

me.combobox1.AddItem cell.Text


Private Sub UserForm_Initialize()
Dim cell as Range
With Sheet1
Me.ComboBox1.RowSource = ""
For Each cell In .Range("B17:B28")
Me.ComboBox1.AddItem cell.Text
Next
End With
Me.ComboBox1.ListIndex = 0 ' or whatever you want
End Sub



Private Sub Combobox1_Click()
With Sheet1
.Range("F11").Value = Me.ComboBox1.Value
End With
End Sub

worked fine for me.

Don't be afraid to roll up your sleeves and do a little debugging - that's
how you will learn.

 

[Corey]

Thanks Tom.
I understand what the F11 was for now.
To get the value and place it somewhere OUTSIDE the range to be used.
I simply added a range("F11").value="" at the end of th Code to clear it.
Don't know why i was getting strange numbers, but the problem is solved now,
thanks
Corey....

 

[Tom Ogilvy]

F11 was to demonstrate placing the value with code. I have no idea where
you want to place it. Just replace F11 with the cell where you want the
selection to appear. If you don't want it to appear anywhere, then you
don't need that code.