Show and Hide a UserForm using a String as the UserForm Name

[metalwork10]

Hi All

I am keeping track of which UserForm is active by placing its name in a worksheet cell.
If someone switches workbooks then I need to Hide my UserForm.

Using the Workbook_Deactivate Event I have the following code which works when Showing the Form (change the last word from Hide to Show) but doesn’t work when trying to Hide the Form?


Public MyUserForm As String
MyUserForm = Sheet4.Range("Current_UserForm").Value
VBA.UserForms.Add(MyUserForm).Hide

Many thanks for any ideas

[Kenneth Hobs]

Userform1.Show
Userform1.Hide

[metalwork10]

Hi Kenneth

Sorry but that is not the answer I was after. The problem is that I can keep tract of the Active UserForm by placing it's name into a cell but I don't know before hand what the UserForm name is. This prevents me from hard coding the UserForm Name Into my code.

[snb]

avoid using several userforms....



application.run "close_" & Sheet4.Range("Current_UserForm").Value


Sub Close_Userform1 ()
userform1.Hide
End Sub

[Kenneth Hobs]

I included more than you needed to show you what does not work. You can delete the parts not needed.

Create Userform1.

Add a Case for each Userform. My suggestion is that when using this sort of method, rename your Userform controls to something unique for that workbook.
If you want to try the VBComponents method, you will need to add that object. I left it there for your information.

In a Module:

Declare Function GetForegroundWindow Lib "user32" () As Long
Declare Function Setfocus Lib "user32" Alias "SetFocus" (ByVal hwnd As Long) As Long

Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Public Const SW_SHOWNORMAL = 1


Sub ShowUserform1()
UserForm1.Show vbModeless
ThisWorkbook.Worksheets("Sheet4").Range("Current_UserForm").Value2 = "UserForm1"
End Sub

In ThisWorkbook:

Private Sub Workbook_Activate()
Dim MyUserForm As String

MyUserForm = ThisWorkbook.Worksheets("Sheet4").Range("Current_UserForm").Value2
On Error Resume Next
' http://support.microsoft.com/kb/157609
'VBA.UserForms.Add(MyUserForm).Show vbModeless
' http://www.cpearson.com/Excel/showanyform.htm
' ShowAnyForm "UserForm1", vbModeless
' ShowUserForm MyUserForm, vbModeless
'Setfocus FindWindow("ThunderDFrame", "Userform1")
ShowWindow FindWindow("ThunderDFrame", MyUserForm), SW_SHOWNORMAL
End Sub

Private Sub Workbook_Deactivate()
Dim MyUserForm As String
MyUserForm = ThisWorkbook.Worksheets("Sheet4").Range("Current_UserForm").Value2
On Error Resume Next
Select Case MyUserForm
Case "UserForm1"
UserForm1.Hide
Case Else
End Select
' or
'ShowWindow FindWindow("ThunderDFrame", MyUserForm), vbHide 'Works fine
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Worksheets("Sheet4").Range("Current_UserForm").Value2 = ""
End Sub

Sub ShowUserForm(userformName As String, state As Integer)
Dim VBComp As Object
For Each VBComp In Application.VBE.ActiveVBProject.VBComponents
If VBComp.Type = 3 And VBComp.Name = userformName Then '3 = vbext_ct_MSForm
VBA.UserForms.Add(VBComp.Name).Show state
End If
Next
End Sub

[metalwork10]

Hi Kenneth

Many thanks for your time with this problem. I never realized how involved this problem would be. I may think of approaching this with a different method. Maybe use a Multipage instead of several UserForms.

[Kenneth Hobs]

It is not that complicated. You only need the two API commands FindWindow and ShowWindow. Most the other stuff is just commented code or unused code that can be deleted.

Multipage has its own issues but it is your call obviously.

[metalwork10]

Hi Kenneth

Yes the length of code had me fearful. I still have a lot to learn but I have managed to find a small piece of code which seems to work with my program.


Dim i As Long, Str As String
For i = VBA.UserForms.Count - 1 To 0 Step -1
Str = Str & VBA.UserForms(i).Name
VBA.UserForms(i).Hide
Next I

What do you think?

[mikerickson]

UserForms("UserForm1").Add.Show

[snb]

sub M_snb()
Select Case Sheet4.Range("Current_UserForm").Value
Case "UserForm1"
UserForm1.Show
Case "UserForm2"
UserForm2.Show
Case "UserForm3"
UserForm3.Show
End Select
End Sub

[Aflatoon]

You could use a simple loop:

Sub HideForm(strName As String)
Dim uf As Object
For Each uf In UserForms
If LCase$(uf.Name) = LCase$(strName) Then uf.Hide
Next uf
End Sub

[snb]

@Aflatoon

That code doesn't detect any of the 3 userforms in the same workbook (Excel 2010)

[Aflatoon]

Interesting - it works fine for me in 2010. (I'm sure you notice that it is intended to hide loaded userforms, not to show userforms as your code does)

[snb]

I wouldn't be so sure if I were you.
I didn't realise the userforms had to be loaded for the code to work....
Thanks for pointing out.

[Aflatoon]

Why would you try to hide an unloaded form? ;)

[snb]

To prevent 'Prism' to read it ?

[metalwork10]

@ snb
Hi there, yes I guess I did overlook your suggestion. Many thanks for your input. I have now done as you suggested. As I know the name of the Active UserForm, which is stored on the worksheet. Your Select Case routine is what I have used.

Many thanks