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

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Logit

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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,093
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you seeing any message boxes?
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
255

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,093
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

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