Userform works despite Run-Time Error 91. What's going on?

Question

I am new to Userforms. I have created the following Userform which is called from a subroutine. The Userform picks up a range from a sheet and creates a corresponding number of textboxes and then checkboxes so as to allocate an original name with a new name.

The userform is created with the following:

Public Sub UserForm_Initialize()

    'Declare variables
    Dim txtBox As MSForms.TextBox
    Dim comBox As MSForms.ComboBox
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer
    Dim dist As Integer
    Dim dstArr As Variant
    Dim rng As Range
    
    'Assign variables
    Set rng = Range("Missing_MAERSK")
    n = rng.Rows.Count
    dist = 5
    dstArr = Range("LU_Destination_Ports").Value
    
    'Loop to add textboxes
    For i = 1 To n
        Set txtBox = UserForm1.Controls.Add("Forms.TextBox.1", Visible:=True)
        With txtBox
            .name = "txtBox" & i
            .Value = rng(i)
            .Height = 20
            .Width = 150
            .Left = 81
            .Top = 30 + dist
            .Font.Size = 10
        End With
        dist = dist + 20
    Next i
    
    'Loop to add list boxes
    dist = 5
    For j = 1 To n
        Set comBox = UserForm1.Controls.Add("Forms.ComboBox.1", Visible:=True)
        With comBox
            .name = "comBox" & j
            .List = dstArr
            .Height = 20
            .Width = 150
            .Left = 315
            .Top = 30 + dist
            .Font.Size = 10
        End With
        dist = dist + 20
    Next j
    
    'Show userform
    UserForm1.Show
    
End Sub

And then when the Replace Names button is clicked the following is ran:

Public Sub CommandButton1_Click()

    'Close userform
    Unload UserForm1
    
    'This is the one
    Dim cmb As MSForms.ComboBox
'   Dim txt As MSForms.TextBox
    Dim oldVal As String
    Dim newVal As String
    Dim rng As Range
    Dim rng2 As Range
    Dim n As Integer
    Set rng = Range("MAERSK_Destin")
    Set rng2 = Range("Missing_MAERSK")
    n = rng2.Rows.Count
    
    'Loop
    For i = 1 To n
        Set txt = Me.Controls("txtBox" & i)
        Set cmb = Me.Controls("comBox" & i)
            If cmb.Value <> "" Then
                oldVal = txt.Value
                newVal = cmb.Value
                rng.Replace what:=oldVal, Replacement:=newVal
            End If
    Next i
    
End Sub

Let's say I populate Bangkok to Bangkok BMT, I get the following:

I think the issue might be with the way I call the values in the Command_Button1_Click sub.

Any advice would be appreciated.

Cheers

Don't you want to let the code finish before you unload the userform? — Tim Williams
@Tim Williams my thinking was to do it with the form closed, since you can't see anything happening anyway. Could the unloaded userform be giving me the runtime error, since the userform is not visible? — e_conomics
Seems like you could just Hide the userform and then unload it when you're done? Should be easy enough to try that. FYI inside the userform it's safer to use Me to refer to the form instead of (eg) UserForm1 - if you change the name you won't need to update the code. — Tim Williams
I replaced Unload UserForm1 with UserForm1.Hide and then once the loop runs I ran Unload UserForm1 however had the same error message. Noted with using Me — e_conomics

e_conomics e_conomics · Accepted Answer · 2021-01-29T04:38:16

Figured out the problem.

According to this post: Call UserForm_Initialize from Module

a UserForm should not be initialized from outside the userform.

I was calling UserForm_Initialize() from my sub, so to rectify this I replaced it with UserForm1.Show

Userform works despite Run-Time Error 91. What's going on?

1 Answers