f2e4
05-15-2008, 04:45 AM
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:
'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
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
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:
'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
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