Combobox Selection Problem

[f2e4]

Hi guys,

I have two comboboxs on 1 sheet and a further 2 other data sheets.

1st data sheet = list of projects
2nd data sheet = list of dates

Combobox code as follows:

[vba]
'Project Drop Down List
Private Sub ComboBox7_DropButtonClick()
ActiveSheet.ComboBox7.Clear
'This will check through the entire list and list only
'unique vales - no duplicated projects
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim I As Integer, j As Integer
Dim Swap1, Swap2, Item

Set AllCells = Worksheets("projects").Range("C:C")

On Error Resume Next

For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
For I = 1 To NoDupes.Count - 1
For j = I + 1 To NoDupes.Count
If NoDupes(I) > NoDupes(j) Then
Swap1 = NoDupes(I)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=I
NoDupes.Remove I + 1
NoDupes.Remove j + 1
End If
Next j
Next I

For Each Item In NoDupes
ActiveSheet.ComboBox7.AddItem Item
Next Item

End Sub
'Date Drop Down Box
'As all the dates are in one row, this code selects the data in that row and transposes it into my list
Private Sub ComboBox9_DropButtonClick()
ActiveSheet.ComboBox9.Clear
Set subs = Worksheets("workload").Range("K3")
col = subs.End(xlToRight).Column
For x = 11 To col
ActiveSheet.ComboBox9.AddItem Worksheets("Workload").Cells(3, x).Value
Next
End Sub
[/vba]

Problem:

The comboboxes disply the data fine but i can not select any of the values with my mouse. the only way i can select anything in scrolling down with the direction keys on my keyboard and pressing enter.

Can anyone see anything wrong or can make any suggestions as to why I can't select with my mouse from the drop downs.

All my other drop downs use Named Ranges, and you can select the data fine.

Additional problem with Date combobox:

Would anyone know why the date format in the combobox is mm/dd/yy but in my datat sheet it is dd/mm/yy (the way i want it)? And possibly how to fix this?


As always, thanks for the help

F

[Bob Phillips]

To get the date the way you want it, either use the Text property or explicitly format it.

[f2e4]

How do I use the text property or format it?

Any ideas on the selection problem

[Djeetn]

Hi,

To format the text you should change your code:

Private Sub ComboBox9_DropButtonClick()
...
For x = 11 To col
ActiveSheet.ComboBox9.AddItem Worksheets("Workload").Cells(3, x).Value
Next
End Sub

Into

[VBA]
Private Sub ComboBox9_DropButtonClick()
...
For x = 11 To col
ActiveSheet.ComboBox9.AddItem Format(Worksheets("Workload").Cells(3, x).Value,"dd-mm-yyyy")
Next
End Sub
[/VBA]

Regards,

Dieter

[f2e4]

Thanks a lot Djeetn

That worked perfectly

I just have to figure out why i can't select anything from the drop down now!!!

[f2e4]

Hey guys,

This is really buging me now

Anyone know why i can't select the value from the drop down when i click on it? The only way to select at the minute is with the keyboard direction keys and pressing enter

[Djeetn]

Hi,

Your problem is caused by the initialisation of the comboboxes. You can't initialize them in the DropButtonClick event.
You have to set them up in the workbook_open event. (located in the module 'ThisWorkbook'.
If you want to change the combobox when adding new dates in the workload sheet, you can initialize the combobox also with the workbook_activate event.


[VBA]
Private Sub Workbook_Open()
Dim col, x

col = Worksheets("workload").Range("K3").End(xlToRight).Column
For x = 11 To col
Worksheets("sheet").ComboBox9.AddItem Application.Worksheets("workload").Cells(3, x).Value
Next
End Sub

[/VBA]

Good luck,

Dieter

[f2e4]

Hi,

Your problem is caused by the initialisation of the comboboxes. You can't initialize them in the DropButtonClick event.
You have to set them up in the workbook_open event. (located in the module 'ThisWorkbook'.
If you want to change the combobox when adding new dates in the workload sheet, you can initialize the combobox also with the workbook_activate event.


[vba]
Private Sub Workbook_Open()
Dim col, x

col = Worksheets("workload").Range("K3").End(xlToRight).Column
For x = 11 To col
Worksheets("sheet").ComboBox9.AddItem Application.Worksheets("workload").Cells(3, x).Value
Next
End Sub

[/vba]

Good luck,

Dieter

Hi Dieter,

Thanks for the info.

I figured this one out late last night. I had both comboboxes running code when the drop down was activated.

As soon as i moved it to worksheet activate, it was all OK.

Thanks for the help though.