VBA cell counter

José C

New Member
Joined
Jan 5, 2016
Messages
2
Hi all! I've been visiting this site for quite a while now for questions about Excel and VBA, but this is the first time I have had to ask a specific question!

I'm new to VBA, but I'm learning. I recently learned how to work with user forms and had an idea for something that might be handy at the place I work.
I was wondering if I could make some sort of cell counter (organic biological cells, not excel cells) using a form with text boxes. It would look something like this:
VBA_CELL_COUNTER.png

The user will click on the text box as instructed, then look in a microscope, and then with each keystroke using the legend below, it would distribute the sum of each class to its corresponding box.
What I want is that if I, for example, press J in the text box, the text box will auto clear itself but it would record the keystroke somewhere and count it towards the total, putting the sum in real time on each of the other boxes for each group.

I can figure out how to do the sums and everything, what I'm more interested is in knowing if there is a way of auto clearing a text box after a key is pressed and keeping that value somewhere else for the final sum.

Hope this is clear enough! If not, let me know and I'll try to explain more!

Thanks so much in advance and keep up the great job!;)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Well it seems I found a way to do it. Code might not be pretty but it is working so far, so that's a good start!:biggrin:
What I did was assign conditions so that as soon as the text box detects one of the desired keystrokes, it would add 1 to a specified cell and immediately change the text box value to "".
Code:
If TextBox1.Value = "J" Or TextBox1.Value = "j" Then
    Worksheets("Sheet2").Range("A2").Value = Range("A2") + 1
    TextBox1.Value = ""
ElseIf TextBox1.Value = "H" Or TextBox1.Value = "h" Then
    Worksheets("Sheet2").Range("B2").Value = Range("B2") + 1
    TextBox1.Value = ""
ElseIf TextBox1.Value = "G" Or TextBox1.Value = "g" Then
    Worksheets("Sheet2").Range("C2").Value = Range("C2") + 1
    TextBox1.Value = ""
ElseIf TextBox1.Value = "F" Or TextBox1.Value = "f" Then
    Worksheets("Sheet2").Range("D2").Value = Range("D2") + 1
    TextBox1.Value = ""
ElseIf TextBox1.Value = "D" Or TextBox1.Value = "d" Then
    Worksheets("Sheet2").Range("E2").Value = Range("E2") + 1
    TextBox1.Value = ""
ElseIf TextBox1.Value = "S" Or TextBox1.Value = "s" Then
    Worksheets("Sheet2").Range("F2").Value = Range("F2") + 1
    TextBox1.Value = ""
End If
If Worksheets("Sheet2").Range("G2") = 100 Then
    MsgBox "Total Count Reached"
    lblTotalCount2.BackColor = vbRed

I added a limit of 100 cells with a message box that warns when the limit has been reached, but it pops up twice, I don't know why. Nothing major, just an annoyance. Also made the total cell count box to turn red upon this condition.

Added a Clear button to reset the counter.

Will keep working on it, this is fun! If anybody have any suggestion, I am more than open to them, and I thank you in advance!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top