Show userform from another workbook

[ranswrt]

I have a database of items in another workbook. I need to select certain
items from the database to use in the current workbook using a listbox. Does
it make a difference which workbook I put the userform in? What is the proper
code to load and show a userform from another workbook or if the userform is
in the currentworkbook how do I set the rowsource for the listbox when the
rowsource is in another workboo?

 

[Dave Peterson]

You can put the userform in the workbook where it belongs.

And if the userform belongs in a different workbook (say book1.xls), you can add
a subroutine into book1.xls's project that shows the userform:

(In a general module)
Option Explicit
Sub ShowMyForm()
UserForm1.Show
End Sub

You can use this kind of code to populate the listbox from the activesheet--no
matter what workbook is active.

(this goes behind the userform in book1.xls)
Option Explicit
Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = ActiveSheet.Range("A1:A10").Address(external:=True)
End Sub


And to show that userform in book1.xls using code in a different workbook, you
could use code like:

Option Explicit
Sub testme()
Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("book1.xls")
Application.Run "'" & OtherWkbk.Name & "'!showmyform"
End Sub

=======
Personally, I would think about keeping the data (in the database) in a separate
workbook. Then put the userform code into an addin, create a toolbar for that
addin that shows the userform and never put the code in those other "source"
workbooks.

 

[ranswrt]

Thank you I'll work on that.

You can put the userform in the workbook where it belongs.

And if the userform belongs in a different workbook (say book1.xls), you can add
a subroutine into book1.xls's project that shows the userform:

(In a general module)
Option Explicit
Sub ShowMyForm()
UserForm1.Show
End Sub

You can use this kind of code to populate the listbox from the activesheet--no
matter what workbook is active.

(this goes behind the userform in book1.xls)
Option Explicit
Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = ActiveSheet.Range("A1:A10").Address(external:=True)
End Sub


And to show that userform in book1.xls using code in a different workbook, you
could use code like:

Option Explicit
Sub testme()
Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("book1.xls")
Application.Run "'" & OtherWkbk.Name & "'!showmyform"
End Sub

=======
Personally, I would think about keeping the data (in the database) in a separate
workbook. Then put the userform code into an addin, create a toolbar for that
addin that shows the userform and never put the code in those other "source"
workbooks.