Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I move multiple tabs in multiple workbooks to a master? | Excel Worksheet Functions | |||
Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
macro: copy multiple workbooks to multiple tabs in single book | Excel Programming | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) |