COMBO BOXES 1

[VC2002]

Hi,

I have a VBA form containing a Combo Box that reads in dates from cells in an Excel Spreadsheet.

When I click the drop-down button, the dates are shown, but when I select one, its integer value appears in the box, how do I fix this so the date format appears and not the integer?

Also, how do I set combo-box to initially contain the first cell of a range of cells that it takes its data from?

Thanks a lot!

 

[RamziSaab]

can u post the code u are using...

 

[VC2002]

Sure, code is as follows:

Private Sub UserForm_Initialize()
cbxPeriodStart.ColumnCount = 1
cbxPeriodStart.RowSource = "S4:S106"
cbxPeriodEnd.ColumnCount = 1
cbxPeriodEnd.RowSource = "T5:T107"
End Sub

Where "S4:T107" are my date values in an Excel spreadsheet, any ideas?

 

[RamziSaab]

yeah i dont know if there is a setting but placing this code behind the combo box will fix your problems

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "dd/mm/yy&quot
End Sub

combobox1 is the name of the combobox

tell me if u it works for you

Ramzi

 

[VC2002]

That's great! The format is perfect.

Do you know how to set the combo box to the initial value, or a value that I specify?

Thanks again for all your help!

 

[RamziSaab]

sorry forgot about that bit, put the following under the intialize code:

ComboBox1.Value = Cells(1, 1)

this will set the value to A1, so for S4 it would be
ComboBox1.Value = Cells(4, 19) and so on...

 

[VC2002]

That's great Ramzi, thanks a lot for all your help!