Load an Userform

Manuel Cavero · Mar 30, 2018

Good morning everyone:

I have to develop a piece of code that calls an useform form two different places. The first one piece of code needs to load an UserForm in memory, so what I doing now if load the UserForm calling forma normal module this way:

Code:

UFPAR.PPRUE

Where UFPAR is the UserForm and PPRUE is a Public Sub that loads values in a Dictionary.

From another module I'm calling the Userform with this piece of code:

Code:

Private Sub CBPares_click()
UFPAR.PPRUE
UFPAR.Show vbModeless
End Sub

I've a problem with the first item, sometines works right and sometimes VBA gives me an error, telling that the object (UFPAR or the UserForm) is Null, because the first piece of code doesn't load the UserForm.

Trying to change the way of doing the load of the userform, I tryed to used

Code:

LOAD UFPAR

or

Code:

LOAD (UFPAR)

But VBE doesn't loads the UserForm. Why?

Jerry Sullivan · Mar 30, 2018

Hi Manuel,

Please post the code for your Sub UFPAR.PPRUE.

Manuel Cavero · Mar 30, 2018

Hi Jerry:

This is this the code for the Sub UFPAR.PPRUE:

Code:

Public Sub PPRUE()
'Muesta el formulario de los pares de prueba
Dim Elemento As Variant
Dim Pair As Variant
Dim Pairs As Variant
Dim AuxiliaryPairs As Variant
Dim x As Integer
'Carga las variables
Pairs = Array( _
        "AUDCAD", "AUDCHF", "AUDJPY", "AUDNZD", "AUDUSD", "CADCHF", "CADJPY", _
        "CHFJPY", "EURAUD", "EURCAD", "EURCHF", "EURGBP", "EURJPY", "EURNZD", _
        "EURUSD", "GBPAUD", "GBPCAD", "GBPCHF", "GBPJPY", "GBPNZD", "GBPUSD", _
        "NZDCAD", "NZDCHF", "NZDJPY", "NZDUSD", "USDCAD", "USDCHF", "USDJPY")
AuxiliaryPairs = _
        Array( _
        "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", _
        "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28")
'Calls a Sub to resize the window of the UserForm
Call MCAM.MakeFormResizable
'Populate the ListBox
With Me.ListBoxPares
    .ColumnCount = 2
    .ColumnWidths = "50;10"
    For Pair = 0 To UBound(Pairs, 1)
        .AddItem
        .List(Par, 0) = Pairs(Pair)
        .List(Par, 1) = AuxiliaryPairs (Pair)
    Next Par
    'Preselect the test pairs in the ListBox | [URL]http://sitestory.dk/excel_vba/listboxes.htm[/URL]
    For x = 0 To .ListCount - 1
        If Left(CStr(.List(x)), 6) = "AUDUSD" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "EURAUD" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "EURCAD" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "EURCHF" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "EURGBP" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "EURJPY" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "EURNZD" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "EURUSD" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "GBPUSD" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "NZDUSD" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "USDCHF" Then
            .Selected(x) = True
        ElseIf Left(CStr(.List(x)), 6) = "USDJPY" Then
            .Selected(x) = True
        End If
    Next x
End With
End Sub

Basically (1) loads a variant Array in a ListBox and (2) selects many of them.

So the first piece of code

Code:

[COLOR=#333333]UFPAR.PPRUE[/COLOR]

loads the userform in memory, and 2nd one, shows the userform:

Code:

Private Sub CBPares_click()
UFPAR.PPRUE
UFPAR.Show vbModeless
End Sub

Thanks a lot for your help, Jerry. Have a nice day

Jerry Sullivan · Mar 30, 2018

Please also post the MakeFormResizable sub and any othe nested proceedures.

Manuel Cavero · Mar 31, 2018

Hi Jerry:

MakeFormResizable is a subroutine in a module. The cide was develop by leith Ross, and teh post is here

https://www.mrexcel.com/forum/excel-questions/485489-resize-userform.html

This is the code:

Code:

Private Declare PtrSafe Function SetLastError _
Lib "kernel32.dll" (ByVal dwErrCode As Long) As Long
Public Declare PtrSafe Function GetActiveWindow Lib "user32.dll" () As Long
Private Declare PtrSafe Function GetWindowLong Lib "user32.dll" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function SetWindowLong Lib "user32.dll" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Public Sub MakeFormResizable()
'Rezise the userform
Dim lStyle As Long
Dim hWnd As Long
Dim RetVal
'
Const WS_THICKFRAME = &H40000
Const GWL_STYLE As Long = (-16)
hWnd = GetActiveWindow
'
'Get the basic window style
 lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME
 
'Set the basic window styles
 RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)

'Clear any previous API error codes
 SetLastError 0

'Did the style change?
 If RetVal = 0 Then MsgBox "Unable to make UserForm Resizable."
End Sub

And there in no more nested functions or Subs

Manuel Cavero · Mar 31, 2018

Hi Jerry:

MakeFormResizable is a subroutine in a module. The code was develop by Leith Ross, and the post is here

https://www.mrexcel.com/forum/excel-questions/485489-resize-userform.html

This is the code:

Code:

[COLOR=#222222][FONT=Verdana]Private Declare PtrSafe Function SetLastError [/FONT][/COLOR][COLOR=#222222][FONT=Verdana]Lib "kernel32.dll" (ByVal dwErrCode As Long) As Long[/FONT][/COLOR]
P[COLOR=#222222][FONT=Verdana]rivate Declare PtrSafe Function GetActiveWindow Lib "user32.dll" () As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Private Declare PtrSafe Function GetWindowLong Lib "user32.dll" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Private Declare PtrSafe Function SetWindowLong Lib "user32.dll" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long[/FONT][/COLOR]

Code:

[COLOR=#222222][FONT=Verdana]Public Sub MakeFormResizable()[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'Rezise the userform[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim lStyle As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim hWnd As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim RetVal[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Const WS_THICKFRAME = &H40000[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Const GWL_STYLE As Long = (-16)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]hWnd = GetActiveWindow[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'Get the basic window style[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana] lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]'Set the basic window styles[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana] RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]'Clear any previous API error codes[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana] SetLastError 0[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]'Did the style change?[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana] If RetVal = 0 Then MsgBox "Unable to make UserForm Resizable."[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

And there is no more nested functions or Subs. It is important to say that I'm using a 64-bit version of Excel 2016

gibra · Mar 31, 2018

Can you post a file sample that reproduce the problem?

Manuel Cavero · Mar 31, 2018

Hi gibra:

Morning from Spain

The problem is that sometimes the first piece of code (UFPAR.PPRUE) loads the UserForm, and sometimes doesn't. When the system doesn't loads the Userform, I've got an error because the object is null....

Remeber that the UserForm is being called form two different subs in two different places of the project. Both places are in the code of a worksheet.

The first one runs a sub on a click event of a buttom of the sheet, and this sub is where the error is produced on loading the Userform

Code:

UFPAR.PPRUE

The second piece of code is in another module of the same sheet amd simply shows the userForm. In this case runs this code:

Code:

Private Sub CBPares_click()
UFPAR.PPRUE
UFPAR.Show vbModeless
End Sub

I tried to use Load but doesn't works

I'm begining to think that the problem is produced when the UserForm is in memory

Manuel Cavero · Mar 31, 2018

hello again gibra

Captura%20de%20pantalla%202018-03-31%2011.13.49.png

This is the error:

https://www.dropbox.com/s/28ch89kfp8i22cs/Captura de pantalla 2018-03-31 11.13.49.png?dl=0

gibra · Mar 31, 2018

Manuel Cavero said:
hello again gibra

The image does not help much, because it does not understand anything.

Only by executing the DEBUG of the code can we hope to find the cause of the error.

Load an Userform

Manuel Cavero

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

Manuel Cavero

New Member

Jerry Sullivan

MrExcel MVP

Manuel Cavero

New Member

Manuel Cavero

New Member

gibra

New Member

Manuel Cavero

New Member

Manuel Cavero

New Member

gibra

New Member

Similar threads

Forum statistics

Share this page

Load an Userform

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock