UserForm initially displayed and now doesn't - what did I do wrong?

[miless2111s]

I am attempting to have a UserForm which contains a set of buttons to allow the users to display different sheets and hide the tabs for neatness. The UserForm should display at the top left of each sheet (i.e. where A1 is).
I have used the code from Pearson Software Consulting with a simple UserForm containing only a text box, and set the UserForm properties to "StartUpPosition" = 0-manual, "Top"=20 and "Left" =20 and initially is all worked brilliantly.
Then I got fancy and applied the same code to a different workbook which had a UserForm with command buttons (to give the required "click to display X" function).

This didn't work - the UserForm didn't display.

Then when I went back to the original prototype and added a command button to see what I might have done wrong the original UserForm failed to display! Removing the Command Button didn't make any difference.

I wondered if the box was displaying way off screen so added a check that the UserForm was visible and it wasn't. So it appears that the code is running but the Form isn't displaying (even though the initialize is running when I step through using F8.

Code to display the UserForm:

Sub show_userform2()
    UserForm2.Show vbmodless
End Sub

Code in the Form:

Private Sub UserForm_Initialize()
'from http://www.cpearson.com/excel/SetParent.aspx
Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"
Dim AppHWnd As Long
Dim DeskHWnd As Long
Dim WindowHWnd As Long
Dim MeHWnd As Long
Dim Res As Long

' Get the window handle of the main Excel application window.
' Note, in Excel 2002 and later, you can use "Application.HWnd"
' rather than "FindWindow("XLMAIN", Application.Caption)" to get the
' handle of the main application window. In Excel 2002 and later,
' uncomment the line
' AppHWnd = Application.HWnd
' and remove the call to
' FindWindow("XLMAIN", Application.Caption)
'
'<<<
' Excel 2002 and later only
'AppHWnd = Application.HWnd
'<<<

' The following line of code is not necessary if you are in Excel 2002 or later
' and you are using "AppHWnd = Application.HWnd" to get the window handle to
' the Excel Application window.
AppHWnd = FindWindow("XLMAIN", Application.Caption)

If AppHWnd > 0 Then
    ' get the window handle of the Excel desktop
    DeskHWnd = FindWindowEx(AppHWnd, 0&, "XLDESK", vbNullString)
    If DeskHWnd > 0 Then
        ' get the window handle of the ActiveWindow
        WindowHWnd = FindWindowEx(DeskHWnd, 0&, "EXCEL7", ActiveWindow.Caption)
        If WindowHWnd > 0 Then
            ' ok
        Else
            MsgBox "Unable to get the window handle of the ActiveWindow."
        End If
    Else
        MsgBox "Unable to get the window handle of the Excel Desktop."
    End If
Else
    MsgBox "Unable to get the window handle of the Excel Application."
End If

' get the window handle of the userform
MeHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.Caption)

If (MeHWnd > 0) And (WindowHWnd > 0) Then
     ' make the userform a child window of the ActiveWindow
     Res = SetParent(MeHWnd, WindowHWnd)
     If Res = 0 Then
         ''''''''''''''''''''
         ' an error occurred.
         ''''''''''''''''''''
         MsgBox "The call to SetParent failed."
     End If
End If
    If UserForm2.Visible = True Then MsgBox "all is well" Else MsgBox "form not visible"

End Sub

I am very new to using a UserForm so be gentle if I have been a massive twit

Many thanks in advance

Miles

 

[miless2111s]

Wow, that's a first; no replies!
Have I posted to the ring section of the site are is this just such a dumb question everyone is rolling on the floor in laughter ?

 

[Logit]

.
If you go here : www.cpearson.com/excel/SetParent.aspx and re-read the entire discussion by Chip Pearson .. also review the posted code .. you should realize
there is more code that does not exist in your workbook .. based on the amount of code you have posted above.

I tested Chip's code here, along with the example workbook he provides, and everything runs well here.

 

[Norie]

Miles

You don't appear to have posted all the code.

If I copy what you have posted then it won't run.

 

[miless2111s]

Rats, I see what I did wrong; I failed to copy the declarations at the top, however they're present in the code, I just failed to copy that bit into the forum.
Regards
Miss

 

[miless2111s]

Ok, now with the full code! Sorry for the rookie mistake

Option Explicit
Private Declare Function SetParent Lib "user32" ( _
    ByVal hWndChild As Long, _
    ByVal hWndNewParent As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long

Private Declare Function GetAncestor Lib "user32.dll" ( _
    ByVal hwnd As Long, _
    ByVal gaFlags As Long) As Long
Private Sub UserForm_Initialize()
'from http://www.cpearson.com/excel/SetParent.aspx
Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"
Dim AppHWnd As Long
Dim DeskHWnd As Long
Dim WindowHWnd As Long
Dim MeHWnd As Long
Dim Res As Long

' Get the window handle of the main Excel application window.
' Note, in Excel 2002 and later, you can use "Application.HWnd"
' rather than "FindWindow("XLMAIN", Application.Caption)" to get the
' handle of the main application window. In Excel 2002 and later,
' uncomment the line
' AppHWnd = Application.HWnd
' and remove the call to
' FindWindow("XLMAIN", Application.Caption)
'
'<<<
' Excel 2002 and later only
'AppHWnd = Application.HWnd
'<<<

' The following line of code is not necessary if you are in Excel 2002 or later
' and you are using "AppHWnd = Application.HWnd" to get the window handle to
' the Excel Application window.
AppHWnd = FindWindow("XLMAIN", Application.Caption)

If AppHWnd > 0 Then
    ' get the window handle of the Excel desktop
    DeskHWnd = FindWindowEx(AppHWnd, 0&, "XLDESK", vbNullString)
    If DeskHWnd > 0 Then
        ' get the window handle of the ActiveWindow
        WindowHWnd = FindWindowEx(DeskHWnd, 0&, "EXCEL7", ActiveWindow.Caption)
        If WindowHWnd > 0 Then
            ' ok
        Else
            MsgBox "Unable to get the window handle of the ActiveWindow."
        End If
    Else
        MsgBox "Unable to get the window handle of the Excel Desktop."
    End If
Else
    MsgBox "Unable to get the window handle of the Excel Application."
End If

' get the window handle of the userform
MeHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.Caption)

If (MeHWnd > 0) And (WindowHWnd > 0) Then
     ' make the userform a child window of the ActiveWindow
     Res = SetParent(MeHWnd, WindowHWnd)
     If Res = 0 Then
         ''''''''''''''''''''
         ' an error occurred.
         ''''''''''''''''''''
         MsgBox "The call to SetParent failed."
     End If
End If
    If UserForm2.Visible = True Then MsgBox "all is well" Else MsgBox "form not visible"

End Sub

This isn't the complete set of code from Pearson Software Consulting as the second section provides a different behavior and I was happy with the way that the UserForm initially displayed however I am confused as to why is stopped working and how to get it working again
Thanks in advance
Miles

 

[miless2111s]

.
If you go here : www.cpearson.com/excel/SetParent.aspx and re-read the entire discussion by Chip Pearson .. also review the posted code .. you should realize
there is more code that does not exist in your workbook .. based on the amount of code you have posted above.

I tested Chip's code here, along with the example workbook he provides, and everything runs well here.

Hi Logit, I to had it working and then it stopped and "all" I had done was add a command button (later removed) and also tried to use the same code on a different workbook which was open at the same time. I don't know if one of these actions made it stop working or how to get it working again
Regards
Miles

 

[Logit]

I noticed a spelling error in your code :

Sub show_userform2()
    UserForm2.Show vbmodless
End Sub

vbmodless ... should be spelled : vbmodeless

Aside from that ... what error notification/s are you receiving ?

 

[miless2111s]

I noticed a spelling error in your code :

Sub show_userform2()
    UserForm2.Show vbmodless
End Sub

vbmodless ... should be spelled : vbmodeless

Aside from that ... what error notification/s are you receiving ?

Logit
No error at all, it is just not displaying, which is why I put the msgbox in as I wondered if the UserForm was displaying out of sight however it seems that it wasn't displaying (judging by the UserForm2.Visible test).
Stepping though using F8 suggested that it goes all the way through from the .show step through the initiate and then back to the .show so I am quite confused at the moment!
TIA
Miles

 

[Logit]

I don't see any settings for the Userform that might make it act this way.

Any other code in the workbook that might affect the Userform ?

It might just be as simple as your workbook has become corrupted somehow. This would require you to re-write your code into a new workbook.
If you copy and paste the existing code you might be copying whatever is causing the error.