excel - ListBox multiple Select

[sjvenz]

I have been trying to display multiple values in a LB on a form.

I have two LB's
LB1 displays employee details - row AH17:ah124
LB2 displays Courses required - rowAJ17:AJ124

I can get LB2 to display the courses require when an employee is
selected in LB1., The problem I'm having is that I am trying to get the
Courses that have a value of "no" (row ak17:ak124) to be highlighted.
Has anyone any ideas, have been trying for awhile to get this to work.
Any help would be greatly appreciated.

I also get an error - Object variable or With block variable not set.

I'm also trying to then paste these values when selected onto another
workbook...is this possible


The following is the code I'm using:

Private Sub UserForm_initalize()
Dim myrange As Range
Dim Cell As Range
Dim a As Integer


Set myrange = Sheets("view daily").Range("aj17:aj124")
For Each Cell In myrange
ListBox1.AddItem Cell.Value
ListBox2.AddItem Cell.Value
If Cell.Offset(0, 1).Value = "no" Then
ListBox2.Selected(a) = True
Else
ListBox2.Selected(a) = False
End If
a = a + 1
Next
End Sub

Private Sub ListBox1_Change()
Dim AllCells As Range
Dim myrange As Range
Dim Cell As Range
Dim Index As Integer
Dim RowSelected As Integer
Set AllCells = Sheets("view daily").Range("ah17:ah124")
ListBox2.Clear

RowSelected = 0
For Index = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Index) Then
RowSelected = Index
End If
Next

For Each Cell In AllCells
If Cell.Value = CLng(ListBox1.List(RowSelected)) Then
ListBox2.AddItem Cell.Offset(0, 2).Text
End If
' Note: the 2nd argument (key) for the Add method must be a
string
' End If
Next Cell

End Sub

Private Sub UserForm_Initialize()

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in ah17:ak124
Set AllCells = Range("ah17:ah124")

On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a
string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
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

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
frm_DAILYSignINsheet.ListBox1.AddItem Item
Next Item

' Show the UserForm
frm_DAILYSignINsheet.Show
End Sub

 

[Patrick Molloy]

first question: you have TWO initialze events for the
form....I'm surprised that it runs. Which is correct?
Second question: if the lists are poplated by the same
two columns, then why re-populate list2? The data doesn't
seem to change.
question three - at what point do you get the error ?
usually VBA will hightlight the problem statement.

to plavce the selected values into a new book...

DIM WB as Mowrkbook, WS as worksheet
SET WB = Workbooks.Add
SET WS = WB.ActiveSheet
dim NewRow as Long
With Listbox2
For Index = 0 to .ListCount-1
if .Selected(Index) Then
NewRow = NewRow + 1
WS.Cells(NewRow,1).Value = .List(Index)
end if
Next
End with


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----

I have been trying to display multiple values in a LB on a form.

I have two LB's
LB1 displays employee details - row AH17:ah124
LB2 displays Courses required - rowAJ17:AJ124

I can get LB2 to display the courses require when an employee is
selected in LB1., The problem I'm having is that I am trying to get the
Courses that have a value of "no" (row ak17:ak124) to be highlighted.
Has anyone any ideas, have been trying for awhile to get this to work.
Any help would be greatly appreciated.

I also get an error - Object variable or With block variable not set.

I'm also trying to then paste these values when selected onto another
workbook...is this possible


The following is the code I'm using:

Private Sub UserForm_initalize()
Dim myrange As Range
Dim Cell As Range
Dim a As Integer


Set myrange = Sheets("view daily").Range("aj17:aj124")
For Each Cell In myrange
ListBox1.AddItem Cell.Value
ListBox2.AddItem Cell.Value
If Cell.Offset(0, 1).Value = "no" Then
ListBox2.Selected(a) = True
Else
ListBox2.Selected(a) = False
End If
a = a + 1
Next
End Sub

Private Sub ListBox1_Change()
Dim AllCells As Range
Dim myrange As Range
Dim Cell As Range
Dim Index As Integer
Dim RowSelected As Integer
Set AllCells = Sheets("view daily").Range("ah17:ah124")
ListBox2.Clear

RowSelected = 0
For Index = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Index) Then
RowSelected = Index
End If
Next

For Each Cell In AllCells
If Cell.Value = CLng(ListBox1.List(RowSelected)) Then
ListBox2.AddItem Cell.Offset(0, 2).Text
End If
' Note: the 2nd argument (key) for the Add method must be a
string
' End If
Next Cell

End Sub

Private Sub UserForm_Initialize()

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in ah17:ak124
Set AllCells = Range("ah17:ah124")

On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a
string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
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

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
frm_DAILYSignINsheet.ListBox1.AddItem Item
Next Item

' Show the UserForm
frm_DAILYSignINsheet.Show
End Sub



------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by

step guide to creating financial statements

 

[Bill Manville]

I can get LB2 to display the courses require when an employee is
selected in LB1., The problem I'm having is that I am trying to get the
Courses that have a value of "no" (row ak17:ak124) to be highlighted.

Is the listbox LB2 set to be multi-select?
Similar code works OK for me.

I also get an error - Object variable or With block variable not set.

On which statement?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[sjvenz]

Ok,
I get the error when I close down the form, but nothing is
highlighted.

What I'm trying to do is when you select and EMPLOYEE in LB1 the values
would then change in LB2 with the courses (that are a "NO") required in
LB2.

At this stage all it is doing is showing the Courses but nothing is
highlighted/selected with what they require to do.

 

[Bill Manville]

I get the error when I close down the form, but nothing is
highlighted.

Does the error not come with a Debug button to take you to the
offending line of code?

I don't fully understand the code you posted; it doesn't seem to
correspond to your description of what you are trying to do.
There are 2 UserForm_Initialize routines for a start.
In the first one you seem to be adding the same information to ListBox1
and ListBox2.

ListBox1 is, I assume, single select.
So the first loop in ListBox1_Change is not needed. You can just use
ListBox1.ListIndex in place of RowSelected.

I suggest you use the debugging facilities to step through your code
and see where it is not doing what you want.



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup