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]

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.

OK, I will give that a go. I assume the act of having the same code in two workbooks which were open at the same time wouldn't have caused this issue? I didn't copy the text in the first place (rather I copied from the site) so I will re-do that.
There was no other code (other than macros which call or close the userform) as I am prototyping it before adding this function to the much more complex project it is destined for.
Thanks for the suggestion.
Miles

 

[Logit]

Although there may be ... I do not see anything in the code that would cause a second open workbook to create a problem. Someone else might have a better understanding about this than I.

 

[miless2111s]

Sorry for the delay. I have now created a new sheet and form with two simple command buttons and copied the code from the website and it still doesn't display a UserForm. Wondering if the command buttons in some way caused the issue I have created a second userform with only a text label and that still doesn't display
I have made sure that the initial location property is set to manual and the form is modeless (both in the calling macro and the properties)
Complete code:

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()

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 UserForm1.Visible = True Then MsgBox "success" Else MsgBox "Form not visible"

End Sub
Private Sub CommandButton1_Click()
    Worksheets("Input").Activate
End Sub

Private Sub UserForm_Click()
    Worksheets("Display").Activate
End Sub

I am using Office 365 Pro 32 bit (thought I want this code to be compatible with as many versions of Excel as possible). I know I wasn't dreaming when I saw this work at one point as even I'm not that sad but it doesn't appear to be working now

Many thanks for any suggestions or fixes that you can provide!

Miles

 

[RoryA]

Are you seeing any message boxes?

 

[miless2111s]

Are you seeing any message boxes?

Only the one that I have programmed in which tells me that the form isn't visible. No other dialogues or messages are appearing.

 

[RoryA]

The Initialize event fires before the form is shown, so that doesn't necessarily surprise me. Have you tried putting the code in the Activate event instead?

Also, since I'm pretty sure Chip's code predates the switch to SDI in Excel 2013, you should probably have a look at Jan Karel's article here: Excel 2013 and up; Keeping Userforms on top of SDI windows

 

[My Aswer Is This]

If you want a Userform to display all the sheet names in the active workbook in a Listbox on the Userform

And then if you click on a sheet name in the listbox you will activate that sheet.

I have a script that is very simple I can send you.

And you could put this Userform in your Personal Workbook
Then it would be available for any workbook you open.
You can activate the script using a Shortcut key.

 

[miless2111s]

If you want a Userform to display all the sheet names in the active workbook in a Listbox on the Userform

And then if you click on a sheet name in the listbox you will activate that sheet.

I have a script that is very simple I can send you.

And you could put this Userform in your Personal Workbook
Then it would be available for any workbook you open.
You can activate the script using a Shortcut key.

Thank you for the kind offer - for my continued education I would be interested in seeing it. For the current application I need to show only a few of the sheet and put some explanation on the buttons to simplify things
Regards
Miles

 

[miless2111s]

The Initialize event fires before the form is shown, so that doesn't necessarily surprise me. Have you tried putting the code in the Activate event instead?

Also, since I'm pretty sure Chip's code predates the switch to SDI in Excel 2013, you should probably have a look at Jan Karel's article here: Excel 2013 and up; Keeping Userforms on top of SDI windows

Not being great at reading code I can't tell is where this will place the form - i assume in the centre of the window?