We do quarterly inventory at my work, and part of my inventory tasks is data entry - 500+ individual tags entered into a giant spreadsheet. My old way was Find by item number, then the count and tag numbers in the requisite cells. This inventory, I've made a UserForm to do most of it for me; it takes the data I give it (item, count, tag number), finds the item number, and enters the count and tag number in the next empty cells. This works flawlessly, but my issue is this:
The UserForm closes every time I use it. I would like to keep the UserForm open, dump the data into the spreadsheet, clear it, and keep the userform open. I can't seem to keep it open no matter what I do. Unload Me and then ufInventoryEntry.Show does not work.
UserForm Code
Private Sub cmdNext_Click()
Dim sItem As String, dCount As Double, sTag As String, sErrClass As String, vResult As Variant
Dim rg As Range, rItemRange As Range
' Pull variables from textboxes
sItem = tbItemNum.Value
dCount = tbCount.Value
sTag = tbTagNum.Value
Set rItemRange = ActiveSheet.Range("A2:A6000")
sErrClass = ""
' Insert values into sheet
For Each rg In rItemRange
If rg.Value = sItem Then ' Find item number in Column A
rg.Activate ' Activate appropriate cell
' Find next empty count cell and populate
Do Until IsEmpty(ActiveCell) = True And Not ActiveCell.EntireColumn.Hidden
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = dCount
' Find next empty tag cell and populate
Do Until IsEmpty(ActiveCell) = True And Not ActiveCell.EntireColumn.Hidden
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = sTag
End If
' Handle case of item number not found
If rg = Range("A6000") And rg.Value <> sItem Then
GoTo ErrHandler
End If
Next rg
Call UserForm_Initialize
ErrHandler:
Select Case sErrClass
Case "NumNotFound"
vResult = MsgBox("Item number not found. Retry?", vbRetryCancel, "Error")
Select Case vResult
Case vbRetry
Unload Me
ufInventoryEntry.Show
Case vbCancel
GoTo EndSub
End Select
End Select
EndSub:
Unload Me
End SubUserform_Initialize
Private Sub UserForm_Initialize()
' Clear textboxes
tbItemNum.Value = ""
tbCount.Value = ""
tbTagNum.Value = ""
' Return cursor to item number
tbItemNum.SetFocus
End SubI'm sure I'm missing something obvious, or not using the right search string in Google. Any help would be MOST appreciated!