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

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
270
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:
VBA Code:
Sub show_userform2()
    UserForm2.Show vbmodless
End Sub

Code in the Form:
Code:
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
270
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,953
.
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Miles

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

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

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
270

ADVERTISEMENT

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

Active Member
Joined
Feb 10, 2016
Messages
270
Ok, now with the full code! Sorry for the rookie mistake :)
VBA Code:
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

Active Member
Joined
Feb 10, 2016
Messages
270

ADVERTISEMENT

.
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,953
I noticed a spelling error in your code :

VBA 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

Active Member
Joined
Feb 10, 2016
Messages
270
I noticed a spelling error in your code :

VBA 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

Well-known Member
Joined
Aug 31, 2016
Messages
3,953
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top