pablo_the_sonar
New Member
- Joined
- Jun 30, 2015
- Messages
- 2
Hello everybody!
I am relatively a newbie to VBA and completely new on this forum, so I preventively apologise for any odd manipulation or obvious question I might ask here...
So here we go: I am working on a table to manage Human Resources at work, where each colleague has a specific row. In the table, I have two contiguous columns, the first one indicating the exact position title (i.e. Administrator, Head of office, etc), and the second one the department in which the person works (i.e. Management, Administration, Logistics, etc). To be noted that these columns are NOT sorted out alphabetically in the worsheet.
I have created an userform with a Listbox, which I want to populate with the data coming from the two columns mentioned above. I would like this Listbox to be populated avoiding double entries with the same position title (e.g. if two persons have the "administrator" title, this should appear only once in the listbox). To do so, I used a "Range" variable in the userform_initialize subroutine, which works pretty well.
I would like the Range to be sorted out alphabetically BEFORE is inserted in the listbox - which I haven't managed so far. Browsing the web I've found a few threads dealing with this topic, but I'm not sure I've found something really answering my question. Is there a simple way (5-6 lines of code max) to do this?
Here's my code in its actual state:
___________________________________________________________________________________
Private Sub UserForm_initialize() 'create a list of positions existing in the table (without doubles)
'___ONE: set the variables
Dim iRangeDpt As Range
Dim iColumn As Range
Dim n As Integer
'___TWO: set the entire "position" column as reference
Set iColumn = Range(Range("cell_mt_column_05_position").Offset(1, 0), _
Range("cell_mt_column_05_position").Offset(1, 0).End(xlDown))
'___THREE: then add positions individually without doubles to iRangeDpt Variable
n = 1
For Each i In iColumn
If n = 1 Then
Set iRangeDpt = Cells(i.Row, i.Column + 1)
ElseIf Application.CountIf(Range(i, i.Offset(-(n - 1), 0)), i) = 1 Then
Set iRangeDpt = Union(iRangeDpt, Cells(i.Row, i.Column + 1))
End If
n = n + 1
Next i
'___FOUR: order the iRangeDpt Range in alpabetic order
THIS IS WHERE I WOULD LIKE TO INSERT THE SORTING CODE
'___FIVE: define the listbox specifics
list_position_names.ColumnCount = 3
list_position_names.ColumnWidths = "20;80;140"
'___SIX: add list to the userform
n = 0
With list_position_names
For Each i In iRangeDpt
.AddItem
If n < 9 Then
.List(n, 0) = "0" & n + 1
Else
.List(n, 0) = n + 1
End If
.List(n, 1) = Right(i, Len(i) - 5)
.List(n, 2) = i.Offset(0, -1)
n = n + 1
Next i
End With
End Sub
___________________________________________________________________________________
Many thanks in advance for any help and support in this!
Warm regards
Pablo
I am relatively a newbie to VBA and completely new on this forum, so I preventively apologise for any odd manipulation or obvious question I might ask here...
So here we go: I am working on a table to manage Human Resources at work, where each colleague has a specific row. In the table, I have two contiguous columns, the first one indicating the exact position title (i.e. Administrator, Head of office, etc), and the second one the department in which the person works (i.e. Management, Administration, Logistics, etc). To be noted that these columns are NOT sorted out alphabetically in the worsheet.
I have created an userform with a Listbox, which I want to populate with the data coming from the two columns mentioned above. I would like this Listbox to be populated avoiding double entries with the same position title (e.g. if two persons have the "administrator" title, this should appear only once in the listbox). To do so, I used a "Range" variable in the userform_initialize subroutine, which works pretty well.
I would like the Range to be sorted out alphabetically BEFORE is inserted in the listbox - which I haven't managed so far. Browsing the web I've found a few threads dealing with this topic, but I'm not sure I've found something really answering my question. Is there a simple way (5-6 lines of code max) to do this?
Here's my code in its actual state:
___________________________________________________________________________________
Private Sub UserForm_initialize() 'create a list of positions existing in the table (without doubles)
'___ONE: set the variables
Dim iRangeDpt As Range
Dim iColumn As Range
Dim n As Integer
'___TWO: set the entire "position" column as reference
Set iColumn = Range(Range("cell_mt_column_05_position").Offset(1, 0), _
Range("cell_mt_column_05_position").Offset(1, 0).End(xlDown))
'___THREE: then add positions individually without doubles to iRangeDpt Variable
n = 1
For Each i In iColumn
If n = 1 Then
Set iRangeDpt = Cells(i.Row, i.Column + 1)
ElseIf Application.CountIf(Range(i, i.Offset(-(n - 1), 0)), i) = 1 Then
Set iRangeDpt = Union(iRangeDpt, Cells(i.Row, i.Column + 1))
End If
n = n + 1
Next i
'___FOUR: order the iRangeDpt Range in alpabetic order
THIS IS WHERE I WOULD LIKE TO INSERT THE SORTING CODE
'___FIVE: define the listbox specifics
list_position_names.ColumnCount = 3
list_position_names.ColumnWidths = "20;80;140"
'___SIX: add list to the userform
n = 0
With list_position_names
For Each i In iRangeDpt
.AddItem
If n < 9 Then
.List(n, 0) = "0" & n + 1
Else
.List(n, 0) = n + 1
End If
.List(n, 1) = Right(i, Len(i) - 5)
.List(n, 2) = i.Offset(0, -1)
n = n + 1
Next i
End With
End Sub
___________________________________________________________________________________
Many thanks in advance for any help and support in this!
Warm regards
Pablo
Last edited: