Adding UserForm content to sheet WITHOUT closing after

  • 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 UserForm_Initialize()
        ' Clear textboxes
        tbItemNum.Value = ""
        tbCount.Value = ""
        tbTagNum.Value = ""
        ' Return cursor to item number
    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!

  • Hi,

    Try the following...

    Firstly get rid of all the Unload Me in sub cmdNext_Click as they will close the UserForm. Also remove ufInventoryEntry.Show.

    Secondly get rid of the Call UserForm_Initialize which is called when the form first loads and move the code there to a sub called ResetUserForm and call that to reset the text boxes and setfocus to tbItemNum.


    Tom Rowe.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!