Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
Private Sub UserForm_Initialize()
With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
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 wrote: Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
Actually that was a typo on my part the actual value(Unless it is now
different) is: 39083 "Scott" wrote in message ups.com... 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 wrote: Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
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 -- Regards, Tom Ogilvy "Corey" wrote in message ... Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
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 ?? "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Corey" wrote in message ... Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
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.... ? "Scott" wrote in message ups.com... 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 wrote: Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
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. -- Regards, Tom Ogilvy "Corey" wrote in message ... 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 ?? "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Corey" wrote in message ... Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
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" wrote in message ... 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. -- Regards, Tom Ogilvy "Corey" wrote in message ... 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 ?? "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Corey" wrote in message ... Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the date change from "January 07" to a numercial value of "39038 "
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. -- Regards, Tom Ogilvy "Corey" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Corey" wrote in message ... 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 ?? "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Corey" wrote in message ... Private Sub UserForm_Initialize() With Sheet1 Me.ComboBox1.RowSource = "B17:B28" Me.ComboBox1.Text = Format(Me.ComboBox1.Text, "mmmm yy") End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
=IF(D13="PAID","YES","NO") Can I change fonts colour | Excel Discussion (Misc queries) |