Error populating a list box
 

Got the error!

Another sub was had an unload instruction and it is causing the error.

Any suggestion on how I can fix this? The idea was when "OK" is pressed this form unload and another one to input password is open. if you user press cancel in the second one the first is open again and so on...

to pass value between forms I use a trick of putting the selection value in a spreadsheet (ActiveWorkbook.Sheets("Menu").Cells(2, 9).Value = Selection) not the most elegant option but kind of works. I would appreciate more efficient solutions...

Thanks a million,
PA



Private Sub CommandButton3_Click()
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End Sub

Private Sub listbox1_Click()
Dim Selection As String
Selection = ListBox1.List(ListBox1.ListIndex, 0)
ActiveWorkbook.Sheets("Menu").Cells(2, 9).Value = Selection
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim lbtarget As MSForms.ListBox
Dim ws As Worksheet
Dim rngSource As Range
Dim count As Integer
Set ws = ActiveWorkbook.Sheets("Utilizadores")
count = Application.count(Range(ws.Cells(2, 1), ws.Cells(200, 1)))
Set rngSource = Range(ws.Cells(2, 1), ws.Cells(count + 1, 2))
Set lbtarget = Me.ListBox1
With Me.ListBox1
.Clear
.ColumnCount = 2
.ColumnWidths = "20;280"
.List = rngSource.Cells.Value
'.ListIndex = 3
End With
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End If
End Sub

Error populating a list box
 

PA presented the following explanation :
Got the error!

Another sub was had an unload instruction and it is causing the error.

Any suggestion on how I can fix this? The idea was when "OK" is pressed this
form unload and another one to input password is open. if you user press
cancel in the second one the first is open again and so on...

Why not just 'Hide' the 1st form, then 'Show' it again if the user
presses cancel on the 2nd form.

to pass value between forms I use a trick of putting the selection value in a
spreadsheet (ActiveWorkbook.Sheets("Menu").Cells(2, 9).Value = Selection) not
the most elegant option but kind of works. I would appreciate more efficient
solutions...

Use a public variable (declared in a standard module) for this. It's
always much more efficient than writing/reading a spreadsheet. If you
hide the 1st form then all its values are available to you, but still
better to load the selected value into a variable that can be used by
other code.

You could also declare the variable in the form module so it behaves
like a property of the form. Then just access it when needed like...

FormName.VariableName


Thanks a million,
PA



Private Sub CommandButton3_Click()
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End Sub

Private Sub listbox1_Click()
Dim Selection As String
Selection = ListBox1.List(ListBox1.ListIndex, 0)
ActiveWorkbook.Sheets("Menu").Cells(2, 9).Value = Selection
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim lbtarget As MSForms.ListBox
Dim ws As Worksheet
Dim rngSource As Range
Dim count As Integer
Set ws = ActiveWorkbook.Sheets("Utilizadores")
count = Application.count(Range(ws.Cells(2, 1), ws.Cells(200, 1)))
Set rngSource = Range(ws.Cells(2, 1), ws.Cells(count + 1, 2))
Set lbtarget = Me.ListBox1
With Me.ListBox1
.Clear
.ColumnCount = 2
.ColumnWidths = "20;280"

Why not...
.RowSource = rngSource
instead of...
.List = rngSource.Cells.Value


'.ListIndex = 3
End With
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc