Private sub userform initialize ошибка как исправить

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 <formName>_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:

Option Explicit
Private Sub CommandButton1_Click()
Dim iLastRow As Long
    iLastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(iLastRow, 3) = Me.ComboBox3
    Cells(iLastRow, 5) = Me.TextBox1
    Cells(iLastRow, 2) = Me.TextBox2
    Cells(iLastRow, 6) = Me.TextBox3
    Cells(iLastRow, 7) = Me.TextBox4
    Cells(iLastRow, 4) = Me.ComboBox4
    Cells(iLastRow, 9) = Me.TextBox5
    Cells(iLastRow, 10) = Me.TextBox6
    Cells(iLastRow, 8) = Me.ComboBox1
    Cells(iLastRow, 12) = Me.ComboBox2
    Me.TextBox1 = ""
    Me.TextBox2 = ""
    Me.TextBox3 = ""
    Me.TextBox4 = ""
    Me.TextBox5 = ""
    Me.TextBox6 = ""
    MsgBox "Информация добавлена!", vbInformation, "База"
End Sub

[B]Private Sub UserForm_Initialize()
With Worksheets("Прайс-лист")
    Me.ComboBox1.List = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
End With
With Worksheets("Сотрудники T")
    Me.ComboBox3.List = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
End With
With Worksheets("Клиенты")
    Me.ComboBox4.List = .Range("C2:C" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
End With
With Worksheets("masterdata")
    Me.ComboBox2.RowSource = "=masterdata!" & .Range("B2:B" & .Cells(.Rows.Count, 2).End(xlUp).Row).Address
End With
End Sub[/B]

Private Sub ComboBox2_Change()
   If Me.ComboBox2.Value <> "" Then
       Me.ComboBox2.Value = Format(Me.ComboBox2, "dd/mmm/yyyy")
   End If
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox1 = Format(Me.TextBox1, "+7 ### ### ## ##")
End Sub

Private Sub CommandButton2_Click()
    End
End Sub

Sub DisplayForm4Calc1(ByVal control As IRibbonControl)
On Error Resume Next
    frmAA.Show
End Sub

Option Explicit
Public myPrevious As Boolean

Private Sub UserForm_Initialize()
On Error Resume Next
    refNumerator.value = ""
    refDenominator.value = ""
    MultiPage.value = 3
End Sub

Load 7 more related questions

Show fewer related questions