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.
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 Sub
Private Sub UserForm_Initialize() ' Clear textboxes tbItemNum.Value = "" tbCount.Value = "" tbTagNum.Value = "" ' Return cursor to item number tbItemNum.SetFocus End Sub
I'm sure I'm missing something obvious, or not using the right search string in Google. Any help would be MOST appreciated!