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:
