Open UserForm From A Different Workbook

[Minitman]

Greetings,

I have two workbooks,Workbook1 (The customer list) and Workbook2 (a
monthly data history, over a hundred and growing). At this time the
UserForm's and Module1's in each Workbook2 are all identical. The
opening code will load a Workbook1 automatically with each Workbook2
that I open.

Problem: Every time I update the code in any workvook2, I have to
export the RecordForm and/or the Moduke1, go into every workbook2's
code section and delete it's current RecordForm and/or Module1 and
then import the updated RecordForm and/or Module1. This is taking a
lot of time since there are a lot of updates (this is still a work in
progress).

Question: Is there any way to update all of Workbook2's
automatically? If not, is there a way to put the RecordForm (UserForm
and code) and Module1 in Workbook1 (which is always open if any
Workbook2 is open)? And if these are not possible, does anyone have
any suggestion as to where to look?

Any help is greatly appreciated.

-Minitman

 

[Dave Peterson]

Is the recordform just a userform?

Maybe you can separate all the code (userform and module1's) into a 3rd
workbook. Make this new code only workbook an addin.

Tell the users that they have to load the addin to make any substantial changes.

Heck, maybe the customer list could be included with the addin--I'm not sure how
you use it. So you'd only need 2 workbooks--the macro workbook and the data
workbook.

But after you've separated the code from the workbook, you'll have to give the
users a way to run your macros (get to the userform for instance).

Saved from a previous post:

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

 

[Minitman]

Hey Dave,

Thanks for the reply. I like the direction it is going, I'm just not
too sure how to get there.

I am still using Office 2003 on an XP box.

Yes. A "RecordForm" is what vba project calls a UserForm.

I will experiment with this idea this weekend and write back on Monday
how it went. This will be an interesting weekend. (LORD, save us
from interesting times) :^)

 

[Minitman]

Hey Dave,

Slight correction - "RecordForm" is the name of the UserForm. (Don't
you just hate growing old!!!)

I tried to put the Module1 & RecordForm into WkBk2 and then access
them from WkBk1 but no luck! :^(

Any chance you could post a few samples of possible ways it could be
done?

Any help is appreciated.

-Minitman

 

[Dave Peterson]

Those links are what I use to start any new addin.

If I want a toolbar, then I use the one on Debra Dalgleish's site.

 

[Minitman]

Hey Dave,

I created a Personal.XLS and imported my UserForm and Module1 into it.

How do I get my sheet code to look at Personal.XLS Module1 and/or run
UserForm?

Sample:
'**********************************************************************************
'In sheet code section
Private Sub Worksheet_Change(ByVal Target As Range)
WChange Target.Column
End Sub
'**********************************************************************************
'In Personal.XLS Module1 - Used to be in same workbook as sheet
'Module1 which was working.

Sub WChange(TCol As Integer)
Select Case TCol
Case 4
CheckForBadReferences
Case 129 ' No change if DD3 is already "YES"_
Sheets("Input").Range("DD3").Value = "YES"
End Select
End Sub
'**********************************************************************************

Sub WChange is a very minor subroutine, but good enough to demo the
problem.

Any ideas on how to my workbook can see Module1 and/or open UserForm?

Any help is appreciated.

-Minitman

 

[Dave Peterson]

First, I use my personal.xla (an addin) as a container for generic macros that I
want available when I open any workbook.

If I wanted a routine for a specific workbook/worksheet, I wouldn't put it in
personal.xl*.

I'd create a dedicated workbook/addin that contains that routine. Give it a
nice name, too. That way, it'll be much easier to share with others.

Lots of people will have their own personal.xl* file and if you share your
version with others, they'll have to choose which file they should use. (You
can only have one version of personal.xls open at a time.)

===========
You have at least a couple of choices.

Option 1:

One is to rename the (personal.xls in your case) workbook's project from
VBAProject to something unique (and save that workbook). Then select the
project with the calling routine.

Tools|References and check the project for your personal.xls workbook.

Now all your functions/subroutines will look like they're part of your original
workbook--in fact, each time you open the calling routine, excel will see the
reference and automatically open that other workbook.

Then you can use:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
WChange Target.Column
End Sub

or (since I like the Call statement)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Call WChange(Target.Column)
End Sub

===========
Option 2:

You'll have to make sure that both workbooks are open. (there won't be a
reference in this option.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherWkbk As Workbook
'remember the other workbook has to be open!
Set OtherWkbk = Workbooks("Please don't use personal.xls")
Application.Run "'" & OtherWkbk.Name & "'!Wchange", target.column
End sub

Or if you wanted to use a function in that other workbook and wanted to return a
value:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherWkbk As Workbook
Dim Result As Long
'remember the other workbook has to be open!
Set OtherWkbk = Workbooks("Please don't use personal.xls")
Result = Application.Run("'" & OtherWkbk.Name & "'!Wchange2", Target.Column)
MsgBox "Column * 2 = " & Result
End Sub

And wchange2 looked like:
Option Explicit
Function WChange2(myNum As Long) As Long
WChange2 = myNum * 2
End Function

==========
And if you want to use the reference...

Open the calling workbook
Open the VBE
hit ctrl-r to see the project explorer
select your workbook's project (the top level)
hit F4 to see its properties
Change the (Name) property from VBAProject to something unique.

 

[Minitman]

Hey Dave,

With this info I am able to access the Module1 in workbook2 from
workbook1. Thank you very much!!!

However, accessing the UserForm on workbook2 from workbook1 is
problematic. :^(

The problem comes from the fact that the UserForm needs the name of
Workbook1 while it is sitting in workbook2. I was using ThisWorkbook
to capture the name of Workbook 1.

Is there a way to pass the name of workbook 1 to the UserForm in
workbook 2???

-Minitman

 

[Dave Peterson]

The userform is in book2.xls and you're calling a macro that shows that
userform.

I would have guessed that in most cases, you'd be working against the
activesheet (and activeworkbook), but if you're doing this by code, you could
add a routine in book2.xls:

I have this code in book1.xls (the workbook without the userform).

Option Explicit
Sub testme02()

Dim OtherWkbk As Workbook
'remember the other workbook has to be open!
Set OtherWkbk = Workbooks("book2.xls")
Application.Run "'" & OtherWkbk.Name & "'!ShowTheForm", ThisWorkbook

End Sub

I have this code in book2.xls (the one with the userform) in a general module:

Option Explicit
Public WhichWkbkCalledMe As Workbook
Sub ShowTheForm(WhichWkbk As Workbook)
Set WhichWkbkCalledMe = WhichWkbk
UserForm1.Show
End Sub

I have a public variable that can be seen by any procedure--including the
userform_initialize procedure behind the userform:

Option Explicit
Private Sub UserForm_Initialize()
Me.Label1.Caption = WhichWkbkCalledMe.FullName
End Sub

 

[Minitman]

"I have a public variable that can be seen by any procedure--including
the userform_initialize procedure behind the userform"

I can't seem to get public variable to work in the UserForm_Initialize
procedure. I traced the workbook1 name up to the "RecordForm.Show"
in Module1of workbook2. It didn't show up in the UserForm_Initialize
procedure.

This is what I have in the sheet code section of workbook1:
************************************************************************************************
Option Explicit
Dim OtherWkbk As Workbook
_____________________________________________
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Set OtherWkbk = Workbooks("MCL6.xls")
Application.Run "'" & OtherWkbk.Name & "'!DoubleClickAction", _
Target.Column, _
Target.Row, _
Target.Value, _
Cancel, _
ThisWorkbook
End Sub
*************************************************************************************************

This is the code in Module1 of workbook2:
*************************************************************************************************
Option Explicit
Public wb1 As Workbook
Public ws1_1 As Worksheet
Public ws1_2 As Worksheet
Public wb2 As Workbook
Public ws2_1 As Worksheet
Public ws2_2 As Worksheet
____________________________________________________
Public Sub DoubleClickAction(TCol As Integer, _
TRow As Integer, _
TVal As Variant, _
Cancel As Boolean, _
WhichWkbk As Workbook)

Set wb1 = WhichWkbk
Set ws1_1 = wb1.Worksheets("Enter")
Set ws1_2 = wb1.Worksheets("Input")
Set wb2 = Workbooks("MCL6.xls")
Set ws2_1 = wb2.Worksheets("CustList")
Calculate
Select Case TCol
Case 1
Select Case TRow
Case 1, 2
Case Else
RecordForm.Show
Cancel = True
End Select
End Select
End Sub
***********************************************************************************************
And here is the code from the UserForm code section:
***********************************************************************************************
Option Explicit
__________________________________________
Private Sub UserForm_Initialize()
MsgBox "Workbook 1 Name is " & wb1.Name 'can't see
Set ws1_1 = wb1.Sheets("Enter")
Set ws1_2 = wb1.Sheets("Input")
Set wb2 = Workbooks("MCL6.xls")
Set ws2_1 = wb2.Sheets("CustList")
Set ws2_2 = wb2.Sheets("Print_Form")
....
End Sub
*************************************************************************************************
Public variables and I do not get along well! :^(

Any idea as to what I did wrong?

-Minitman

 

[Dave Peterson]

Why did you move this statement out of the procedure:
Dim OtherWkbk As Workbook
Just curious.

===
But the real question is why didn't you use the public variable in the
userform_initialize procedure?

Instead of:
MsgBox "Workbook 1 Name is " & wb1.Name 'can't see

You want to use:
MsgBox "Workbook 1 Name is " & whichwkbk.Name

And since you've made those public variables, you should use them:

Private Sub UserForm_Initialize()
MsgBox "Workbook 1 Name is " & whichwkbk.Name
msgbox ws1_1.name
'and so forth

End Sub

 

[Dave Peterson]

ps. It may make your post look very pretty, but don't include those underscore
lines...

Those underscores can be impossible to see when they occur on the same line as
that VBE separator line (It took forever to notice them <vbg>!)

 

[Minitman]

Hey Dave,

I was attempting to use your code with my names. I must have missed
something on the first attempt because when I went back over to logic
I could not see any problem. In the process of reentering your code
with my names the code fixed itself. :^)

Again, thanks for the help.

-Minitman

 

[Minitman]

I'll be more careful in the future

ps. It may make your post look very pretty, but don't include those underscore
lines...

Those underscores can be impossible to see when they occur on the same line as
that VBE separator line (It took forever to notice them <vbg>!)

 

[Dave Peterson]

Well, after I posted, I realized that I got confused with YOUR variable names.
My latest response was completely off-base.

You were using wb1 (the public variable). I was just too confused to see it
<bg>.

But you didn't answer my question about dimming the otherwkbk outside the
procedure.

 

[Minitman]

Sorry about the confusion. When I tried to run the code with my names
(which should have worked but did not), I thought that I must have
done something wrong! It turned out that just redoing the conversion
from your names to mine corrected the problem.

As for dimming otherwkbk at the top of the code sheet - the finale
code does indeed have this variable dimmed at the top of the code
section. I was still working on functionality when I posted and
hadn't noticed the variable duplication until shortly after posting,
at which time I made the necessary adjustment.

Again, thanks for the help.

-Minitman

 

[Dave Peterson]

Glad you got it working!

Sorry about the confusion. When I tried to run the code with my names
(which should have worked but did not), I thought that I must have
done something wrong! It turned out that just redoing the conversion
from your names to mine corrected the problem.

As for dimming otherwkbk at the top of the code sheet - the finale
code does indeed have this variable dimmed at the top of the code
section. I was still working on functionality when I posted and
hadn't noticed the variable duplication until shortly after posting,
at which time I made the necessary adjustment.

Again, thanks for the help.

-Minitman

 

[Dave Peterson]

One more thing, if you're doing this routine for lots of different workbooks,
you may want to look into using an application event.

Instead of putting code in each worksheet module that needs this behavior, you
can add something unique to each of those sheets (a hidden sheet name???).

Then you can have your workbook (addin??) that contains the userform actually
monitor events. The code would look for that top secret flag and decide if it
should continue.

This would be the shell that you'd need in that addin (in the ThisWorkbook
module):

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim TestName As Name
Dim TestNameStr As String

TestNameStr = "TopSecretUniqueToThisProcedure"

Set TestName = Nothing
On Error Resume Next
Set TestName = Sh.Names(TestNameStr)
On Error GoTo 0

If TestName Is Nothing Then
Exit Sub
End If

UserForm1.Show

End Sub

You can read more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx