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

miless2111s

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

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
255
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
2,909
.
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
75,288
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
255
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
255
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
255
.
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
2,909
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
255
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
2,909
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.
 

Forum statistics

Threads
1,078,435
Messages
5,340,253
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top