Listbox and multiple workbooks
 

I need help!
I have a database workbook, a xla file (soon to be) that contains a listbox,
and a data input workbook. Using the listbox from the xla file, I need to
populate the input file from the database file. I'm close, but cannot get to
the last step. I am using a 'right click' menu item to fire the code below.
It works if I am on the database file, but not on the input file.

What am I missing?

The database workbook will be updated frequently but must it be opened to be
used?

Thanks in advance!

Sub See_Vendor()
wbname = "Current Vendors"
Call WorkbookIsOpen(wbname)
If Err < 0 Then
Workbooks.Open Filename:="s:\finance\acct-gl\current vendors.xls"
Range("a2").Select
Top = ActiveCell.Address
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
bottom = ActiveCell.Address
Worksheets("Sheet1").Range(Top, bottom).Name = "Vendor"
End If

Vendor_Lookup.Show

End Sub

Function WorkbookIsOpen(wbname) As Boolean
'Used from John Walkenbach's VBA Power Programming
'Returns True if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function

Private Sub UserForm_Initialize()
ListBox1.RowSource = "Vendor"
End Sub

Listbox and multiple workbooks
 

I am not sure this will completely solve the problem, but it does qualify
your range references to the exact workbook and sheet. Give it a try.

Sub See_Vendor()
Dim wb As WorkBook, sh As Worksheet, lr As Long
wb = Workbooks("current vendors.xls")
sh = wb.Worksheets("Sheet1")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
wbname = "current vendors"
Call WorkbookIsOpen(wbname)
If Err < 0 Then
Workbooks.Open Filename:="s:\finance\acct-gl\current vendors.xls"
Top = sh.Range("A2").Address
Bottom = sh.Range("A" & lr).Offset(,3).Address
sh.Range(Top,Bottom).Name = "Vendor"
End If

Vendor_Lookup.Show

End Sub


"Dan" wrote:

I need help!
I have a database workbook, a xla file (soon to be) that contains a listbox,
and a data input workbook. Using the listbox from the xla file, I need to
populate the input file from the database file. I'm close, but cannot get to
the last step. I am using a 'right click' menu item to fire the code below.
It works if I am on the database file, but not on the input file.

What am I missing?

The database workbook will be updated frequently but must it be opened to be
used?

Thanks in advance!

Sub See_Vendor()
wbname = "Current Vendors"
Call WorkbookIsOpen(wbname)
If Err < 0 Then
Workbooks.Open Filename:="s:\finance\acct-gl\current vendors.xls"
Range("a2").Select
Top = ActiveCell.Address
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
bottom = ActiveCell.Address
Worksheets("Sheet1").Range(Top, bottom).Name = "Vendor"
End If

Vendor_Lookup.Show

End Sub

Function WorkbookIsOpen(wbname) As Boolean
'Used from John Walkenbach's VBA Power Programming
'Returns True if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function

Private Sub UserForm_Initialize()
ListBox1.RowSource = "Vendor"
End Sub