Confused With VBA, Userform, and sorting data in excel

[davew0880]

Hello everyone! I am new to the forum. I found it searching google for answers to my issues, since then I have searched this forum but am just as confused as before.

I will have excel spreadsheets that will be similar to the one found at the following dropbox link.

https://www.dropbox.com/sh/c5zghws832np4k7/AADFK8PpwLG8sBIbuL-0i93aa?dl=0

I will be making various spreadsheets but they will all follow the same format, the same columns, just different names and or numbers in them and could range up to around 1,000 rows of number plus name but mostly around a few hundred.

What I am trying to do is have a very simple userform with one text box that when a number or name is typed into the form and then the enter key or a submit button is pressed that it will add one number to the total next to the associated row for the number or name entered.

For example: If I would enter the number 2 (which can be seen in the spreadsheet as row 3) and hit the enter key or the name Ken (also row 3 in teh spreadsheet) and hit the enter key, it will then change the cell at column D row 3 to reflect 1, if I then enter 2 or Ken again and hit enter it will then increase the number in cell D 3 by one.

I have been trying to figure this out but have not had much luck, I can create a spreadsheet no problem, I can halfway make a userform, but I have no clue as to the VBA coding and every source I find I do not understand or they want you to buy a service or book.

So, first, can that be done? Second, how can I find out how to code that?

Thank you so much,

Dave

 

[JLGWhiz]

Something like this in the UserForm code module might work for you

Private Sub CommandButton1_Click()
Dim sh As Worksheet, fn As Range
Set sh = Sheets("Sheet1")
If IsNumeric(Me.TextBox1.Text) = True Then
    Set fn = sh.Range("B2", sh.Cells(Rows.Count, 2).End(xlUp)).Find(TextBox1.Text, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.Offset(, 2) = fn.Offset(, 2).Value + 1
        End If
Else
    Set fn = sh.Range("C2", sh.Cells(Rows.Count, 3).End(xlUp)).Find(TextBox1.Text, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.Offset(, 1) = fn.Offset(, 1).Value + 1
        End If
End If
End Sub

You would need to add a commandbutton1 to your userform. the code tested without error.

 

[davew0880]

Hello JLGWhiz,

Thank you so much for your answer. The whole process is much more complicated than I thought it would be but this forum is awesome for help.

I used the code and it works great, I even tried to play with it a little and then got lost LOL. I was trying to figure out what code I need to alter in order to account for new columns. Say I added a column between A and B, what would need to be changed in the code to let know the new column is there so it can account for the shift to place the totals in the right cells now?

I also tried to figure out a shorter way to submit data. I tried to make it so once text is typed in the textbox and then the enter key is pressed that it would submit the data and then clear the box so the next data could be entered, and so forth and so forth, but all it did was highlight the command button and wait for a click. I googled and searched for some information on clearing a box after data submission and just using the enter key but what I found made no sense to me. Is that even possible in this situation?

 

[JLGWhiz]

Say I added a column between A and B, what would need to be changed in the code to let know the new column is there so it can account for the shift to place the totals in the right cells now?

The offset value would increment or decrement by the number of rows you add or delete between the target column and the column you want to hold the running total.

To clear the text after the running total has been incremented, add the line of code below.

Me.Textbox1.Text = ""

Make it the last line before 'End Sub'.