Selecting a ListBox range

[JoeH]

Hi there,

I am a novice at using VB and need some help.
I have created a list box that pulls it's info from another shee
labled "database". I need to be able to select a row from that list bo
and have certian values from that row pasted on another sheet. I hav
been able to paste the info but only from the last row that appears
not the one that is selected. I've though something along the lines o
Listbox_click event but don't know how to use it Here is the cod
that i have so far:

Dim BSapp_v2 As Workbook
Dim shtbsmain As Worksheet
Dim shtdatabase As Worksheet
Dim cl As Integer


Private Sub UserForm_Activate()
With Sheets("database")
For cl = 2 To 5000
If .Cells(cl, 4) = "" Then Exit For
Next
End With
cl = cl - 1
progListBox.RowSource = "database!$A$2:$V$" & Mid(Str(cl), 2)
End Sub

Private Sub btnOpen_Click()
If IsNull(progListBox) Then
Beep
MsgBox "No Reference Selected!", vbExclamation, ""
Exit Sub
End If

With Sheets("DataBase")

Load:
Range("Issr_local") = .Cells(cl, 2)
Range("Principal") = .Cells(cl, 4)

End With
Hide
End Sub

Private Sub btnCancel_Click()
Hide
End Sub

Any help would be greatly appreciated. Thank you for your time.

Jo

 

[Tom Ogilvy]

isnull doesn't work in that situation

Private Sub btnOpen_Click()
If progListBox.ListIndex = -1 Then
Beep
MsgBox "No Reference Selected!", vbExclamation, ""
Exit Sub
End If
set rng = Range(progListbox.RowSource).Columns(1).Cells
cl = rng.Offset(prog.ListBox.ListIndex,0).Row
With Sheets("DataBase")

Load:
Range("Issr_local") = .Cells(cl, 2)
Range("Principal") = .Cells(cl, 4)

End With
Hide
End Sub

 

[JoeH]

Tom,

Thanks for the quick reply. I replaced the section and now i a
getting a -- Runtime Error 424 Object Required. The debugger shows Thi
line as the 'offender'

cl = rng.Offset(prog.ListBox.ListIndex, 0).Row

I tried declaring RNG as an integer and got the same error (don't kno
if needed or not but thought it was worth a try)

Any more hints ??

Again thanks
Jo

 

[JoeH]

TOM !!!!

Nevermind.... I just needed to clear the sleep from my eyes and remov
a period !!!!!!

It's deffinatly time to get some rest. It's been a long weekend !

Thanks again for the quick and perfectly working response !!!

Jo

 

[Tom Ogilvy]

Yes, my typo. Sorry for that.