I'm relatively new to excel VBA and have my entire program written
except for one small function and I can't find the answer anywhere!

I am using a userform in file1 with command buttons that send the user
to another excel file (file2). When you click on the command button,
the userform hides and you can see the file2. I have created a button
in file2 to close file2. What I need to happen at this point is for
the userform in file1 to show again.

I have tried not hiding it to begin with, but I can't get it to lose
focus. I have tried modeless, and you can access file2, but the
userform remains on top and I can't get it to move to the back. I have
tried to make it when file1 activates userform.show, but that doesn't
seem to work and I don't have a grasp of the activate / deactivate
concept.

I feel like this is something easy that I'm missing! Can anyone help
me???

Thanks in advance!

Have you thought about instantiating application level events in File1
http://www.cpearson.com/excel/appevent.htm

and use the WindowActivate and WindowDeactivate events.

--
Regards,
Tom Ogilvy


"brookly" wrote in message
oups.com...
I'm relatively new to excel VBA and have my entire program written
except for one small function and I can't find the answer anywhere!

I am using a userform in file1 with command buttons that send the user
to another excel file (file2). When you click on the command button,
the userform hides and you can see the file2. I have created a button
in file2 to close file2. What I need to happen at this point is for
the userform in file1 to show again.

I have tried not hiding it to begin with, but I can't get it to lose
focus. I have tried modeless, and you can access file2, but the
userform remains on top and I can't get it to move to the back. I have
tried to make it when file1 activates userform.show, but that doesn't
seem to work and I don't have a grasp of the activate / deactivate
concept.

I feel like this is something easy that I'm missing! Can anyone help
me???

Thanks in advance!

Okay, this is what I did. This is all in my "thisworkbook". Do I just
need to change my workbook_activate to window_activate? or do I need
to get rid of that all together and put a window deactivate on my
userform?


Option Explicit

Private Sub workbook_open()
' Start runs a macro that eliminates toolbars, sets a picture as
background, pulls up userform, etc.
Start
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub

Private Sub workbook_activate()
UserForm1.Show
End Sub

Dim AppClass As EventClass

this worked for me.

in the THISWORKBOOK Module:

(note that "start" is not called until after the application level events
are instantiated")

Public WithEvents App As Application


Private Sub App_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As
Excel.Window)
Dim bk As Workbook
'MsgBox Wb.Name, , "App_WindowActivate"
If Wb.Name = ThisWorkbook.Name Then
On Error Resume Next
Set bk = Workbooks("File2.xls")
On Error GoTo 0
If bk Is Nothing Then
' to avoid showing the form anytime the file1.xls is activated
' check if there is a loaded userform - thus, when you are
' finally done with the userform, unload it rather than hide it
' see code in userform module
If VBA.UserForms.Count 0 Then
UserForm1.Show
End If
End If
End If
End Sub

Private Sub workbook_open()
Set App = Application
'MsgBox App.Name & ": " & ThisWorkbook.Name, , "Workbook_Open"
Start
End Sub

In a general module:

Sub Start()
' yours does more of course
UserForm1.Show
End Sub

In the Userform Module:

Private Sub cmdClose_Click()
' done with the userform - close it
' unload when done so the userform won't show just
' by selecting another workbook
Unload Me
End Sub

Private Sub cmdOpenFile2_Click()
' hides (don't unload) the userform
Me.Hide
' opens the file
Workbooks.Open "C:\Data\file2.xls"
End Sub

-----------------------

--
Regards,
Tom Ogilvy

"brookly" wrote in message
oups.com...
Okay, this is what I did. This is all in my "thisworkbook". Do I just
need to change my workbook_activate to window_activate? or do I need
to get rid of that all together and put a window deactivate on my
userform?


Option Explicit

Private Sub workbook_open()
' Start runs a macro that eliminates toolbars, sets a picture as
background, pulls up userform, etc.
Start
Set AppClass = New EventClass
Set AppClass.App = Application
End Sub

Private Sub workbook_activate()
UserForm1.Show
End Sub

Dim AppClass As EventClass

Yeah! So close!!

This works great when I close file2 with the small "x". The problem
with that is that I have a macros running to eliminate toolbars when
the userform shows (just for presentation sake). When the user clicks
the command button in file1userform that sends them to file2, I reset
the toolbars. I have added a command button directly onto file2
spreadsheet that I want to "clear all" toolbars and close file2 without
saving changes. When I use that command button to close file2 instead
of the "x", my userform does not show again in file1 which is still
open.

Here is the code on the command button in file2.

Private Sub CommandButton1_Click()
'ClearAll runs a macros to eliminate toolbars and unsightly excel
background
ClearAll
ProdWorkbook.Close savechanges:=False
End Sub

What am I missing?

P.S. You're saving my life!

I would next try putting a procedure in File1 that shows the userform.

Then in the button code of File2, have it do an Application.Ontime to run
that procedure just before it closes

Application.OnTime Now+timevalue("00:00:01"),"File2.xls!ShowMyForm"
ProdWorkbook.Close savechanges:=False



--
Regards,
Tom Ogilvy
..
"brookly" wrote in message
oups.com...
Yeah! So close!!

This works great when I close file2 with the small "x". The problem
with that is that I have a macros running to eliminate toolbars when
the userform shows (just for presentation sake). When the user clicks
the command button in file1userform that sends them to file2, I reset
the toolbars. I have added a command button directly onto file2
spreadsheet that I want to "clear all" toolbars and close file2 without
saving changes. When I use that command button to close file2 instead
of the "x", my userform does not show again in file1 which is still
open.

Here is the code on the command button in file2.

Private Sub CommandButton1_Click()
'ClearAll runs a macros to eliminate toolbars and unsightly excel
background
ClearAll
ProdWorkbook.Close savechanges:=False
End Sub

What am I missing?

P.S. You're saving my life!

I don't know what that means, but it worked!!!! Thanks so much!