Userform_Initialize not working; Error 424 or error 91

Question

I am working with Excel 2016, and I am new to VBA. The userform initialize functionality was working, and then stopped. I am trying to figure out why.

I want to push a button on a worksheet, have a form pop up that accepts some input (text and a selection from a drop down list), and another button on the form to create another popup to accept more input (barcode scan or text entry) until it eventually exits based on a determined condition (run out of slots to populate with the scanned barcode) or the user exits.

I have the button on the worksheet. I have the userform. However, at some point, I had to rename userform_Initialize to _Initialize because I was getting errors about objects missing, and error 424. After doing so, the "compiler" is happy, but the initialize function is never called, so nothing is working on the userform.

I think what I am seeing is very similar to this other question, but I'm not sure, nor am I sure where to stick my code if I try to do that.

Am I taking the wrong approach?

Private Sub UserForm_Initialize() gives error 424 at runtime when I click on the commandButton to bring up the userform. Switching to Private Sub warehouseCheckinForm_Initialize(), the initialize function is never called when the userform populates, leaving the functionality on the form broken and causing other problems..

This is the code on the userform:

Public initialsInput As String
'*Initials of user scanning in stuff

Public modelSelected As String
'*Model selected for scanning

Public numItemsModel As Integer
'*Keep track of how many of the selected model are available to check in to the warehouse

Public searchBOMRange As Range
'*Range for search operations


Private Sub ModelComboBox_Change()
   modelSelected = ModelComboBox.Value
   numItemsModel = Application.WorksheetFunction.CountIf(searchBOMRange, modelSelected)
   numItemsModelLabel.Caption = numItemsModel

End Sub

Private Sub setInitialsButton_Click()

    If Len(InitialsTextBox.Value) = 2 Then
        initialsInput = InitialsTextBox.Value
    ElseIf Len(InitialsTextBox.Value) < 2 Then
        MsgBox "Enter in 2 letters for your initials"
    Else
        MsgBox "You entered in too much data!"
    End If

End Sub

Private Sub UserForm_Initialize()

    '*Start with empty inputs
    numItemsModel = 0
    searchBOMRange = Sheets("BOM").Range("C11:C2000")
    modelSelected = ""
    initialsInput = ""

    InitialsTextBox.Value = ""
    ModelComboBox.Clear
    numItemsModelLabel.Caption = numItemsModel

    '*Fill the Combo Boxes

    Dim oDictionary As Object
    Dim cellContentModel As String
    Dim rngComboValues As Range
    Dim rngCell As Range

    Set rngComboValues = Sheets("BOM").Range("C11:C2000")
        '*The first ~2000 items because there probably won't be BOMs bigger than that.  Test case was <1000
        '*Doing C:C took 5+ seconds to load the window
    Set oDictionary = CreateObject("Scripting.Dictionary")

    For Each rngCell In rngComboValues
        cellContentModel = rngCell.Value
        If Not oDictionary.exists(cellContentModel) Then
            oDictionary.Add cellContentModel, 0
        End If
    Next rngCell

    For Each itm In oDictionary.keys
        Me.ModelComboBox.AddItem itm
    Next itm

    Set oDictionary = Nothing




        'For Each cell In Sheets("BOM").Range("B:B")
    '    If cell.Value <> "" Then
    '        MakeComboBox.AddItem cell.Value
    '    End If
    'Next cell

End Sub



Private Sub warehouseScanButton_Click()

    For Each modelSelected In searchBOMRange

        If Len(initialsInput) < 2 Then
            Beep
            MsgBox "Enter your initials first!"
            End
        ElseIf Len(modelSelected) < 1 Then
            Beep
            MsgBox "Select a model first!"
            End
        ElseIf Len(initialsInput) >= 2 And Len(modelSelected) >= 1 Then
            scannedInput = InputBox("Scan a serial number, or type it in and mash the ENTER key")
            If scannedInput = "NA" Or scannedInput = "N/A" Then
                Beep
                MsgBox "You can't search for 'not applicable', it doesn't apply!"
                End
            End If
        End If

        '//Searches for empty serial number cell
        '// Model is in C, serial is in O (letter)
        '//offset is row, column; down is positive, right is positive

        Set matchedCell = modelSelected.Offset(0, 12)

        If matchedCell Is Nothing Then
            '//do stuff
            scannedInput = InputBox("Scan a serial number, or type it in and mash the ENTER key")

            matchedCell.Offset(0, 2).Value = initialsInput
            matchedCell.Offset(0, 3).Value = Now '// Checked in to Warehouse
            matchedCell.Offset(0, -2).Value = Now '// "Recv'd date"
            matchedCell.Offset(0, 1).Value = "W"

            numItemsModel = numItemsModel - 1


            'If Len(matchedCell.Offset(0, 4).Value) >= 2 And scannedInput <> "" Then
            '    Beep
            '    MsgBox "Serial Number " & scannedInput & " is already checked in to The Lab!"
            'ElseIf Len(matchedCell.Offset(0, 4).Value) < 2 Then
            '    matchedCell.Offset(0, 4).Value = initialsInput
            '    matchedCell.Offset(0, 5).Value = Now
            '    matchedCell.Offset(0, 1).Value = "L"
        End If

        If Not matchedCell Is Nothing Then '//If the cell has something in it
                '//Beep
                '//MsgBox "Error! This is unpossible!"
                '//End
            End If
        End If

    Next modelSelected

End Sub

This is the logic on the command button on the worksheet:

Private Sub WarehouseCheckinCommandButton_Click()
    '*Brings up the form
    WareHouseCheckinForm.Show

End Sub

I think somehow a keyword is involved, or something else. When I change the name of the function, I see some stuff at the top of the window changing. It goes from "Userform" to "General". I think that is important.

Edit 2

(Edit 1 was rolled in on the sly) Ok, so it sounds like I need to leave the initialize function as Userform_Initialize. This is what I get when I click on the command button run time error 91 object variable or With block variable not set and I have the option to debug. If I debug, I get this:

YetAnotherRandomUser · Accepted Answer

It turned out that I was setting the range with just = and not using set. I didn't think anything about how to set an object versus a primitive.

I changed:

searchBOMRange = Sheets("BOM").Range("C11:C2000")

to:

Set searchBOMRange = Sheets("BOM").Range("C11:C2000")

And now things work like I want them to.

Userform_Initialize not working; Error 424 or error 91

Answers (2)

Related Questions