Tally Total

[Danian]

Just starting using VBA with MS Excel. I need to make a basic tally chart for items sold. I need a button in VBA that when you click it just adds another 1 to the total in the textbox, so on my form I click a button 5 times and in the textbox it says 5. In Access you would put "txtbox1 = txtbox1 +1" but this is not the case for Excel. Maybe i need to make the textbox Interger only. How do you do this.

Excel 2002 (XP)

Thanks in Advance

[Ger Plante]

Hi, welcome aboard!

Its not much different to what you described above.

TextBox1.Value = TextBox1.Value + 1 

See attached XLS for a rocking demo!!! :-)

Press ALT+F11 to see the code behind it the scenese.

Let me know if you need anymore help.

Ger

[JF]

Just starting using VBA with MS Excel. I need to make a basic tally chart for items sold. I need a button in VBA that when you click it just adds another 1 to the total in the textbox, so on my form I click a button 5 times and in the textbox it says 5. In Access you would put "txtbox1 = txtbox1 +1" but this is not the case for Excel. Maybe i need to make the textbox Interger only. How do you do this.

Excel 2002 (XP)

Thanks in Advance

1 Question, any particular reason you are using a text box and button? Why not use a spinner (unless you only want to increase and not be able to decrease the number)?

2. If only increase, then why use a text box instead of just increment the value of a cell when clicking the button

Private Sub CommandButton1_Click() 
    Cells(1, 1).Value = (Cells(1, 1).Value) + 1 
End Sub 

This would increment A1 by 1 each time the button was clicked.

[Danian]

Whats a spinner????

[Danian]

Still will not work. I think its because i have not specified that it should be just for numericals. How do you do this.

How do you make your form auto popup when you run the spreadsheet.

PLease see attached:

[Ger Plante]

Sorry, whats not working?? What else are you trying to do.

To show the User form when excel opens put the following code into the Workbook_open event... as follows:

 
Private Sub Workbook_Open() 
     
     
    frmDailyInput.Show 
     
End Sub 

I picked up on a small bug in your code... if you immediately click the 'add' button for any specific text boxes, it will crash because the text box has not been initalised to zero in the code, or changed to a specifc number by the user. Its like you are trying to tell excel to add one to nothingness... Its good practice to initalise these to a certain number (usually zero, but can be anything) in the workbook_open event as above. Even though it is a text box, excel will evaluate the text box to a number when you try and do something like adding a number to its a value.

Before you show the form above, Initalise all required textboxes with

frmDailyInput.textbox3.value = 0 
 'initialise the rest of the text boxes that will contain numbers also.

On the "complete" command button where you have...

Me.txt3mins.Value = "" 
 'and all the rest of them

this should also be changed to

frmDailyInput.textbox3.value = 0 
 'and all the rest of them

There is no big difference between using "Me." and "frmDailyInput." I just come from the old school and I like to properly reference all objects etc. "Me." just seems like a short cut and can make debugging kind of tricky if you have 10 User Forms and 10 text boxes on each user form. ("What form does ME. refer to now????"). Just a matter of taste really. Dont get hung up on it.

Spin buttons used to be called up/down controls about 100 years ago when I started programming ;-)

Its basically a control that has two arrows (one pointing up and the other pointing down). The control is linked to a text box. When a user presses the up arrow on the control, it increases the value in the text box. When the user presses the down arrow on the control it decreases the value in the text box (a small amount of code required behind the scenes). The spin button control can be found on the Control Toobox (where you found your Text Box). You probably have seen plenty of spin buttons in many windows based applications like graphics applications, for controlling zoom and angles in 3D drawings.

The advantage of using a spinner over what you are doing is that you can only ADD numbers to the text box using a command button. Using the spinner you can ADD and SUBTRACT numbers from the text box automatically. It also provides a mechanism for you to specify that the maximum number in the text box should be say 60 and the minimum zero... The system then provides for this auto error checking... for example. Its definitely worth playing with them a little bit to see their power.

Hope that makes sense.

Cheers,
Ger

[Danian]

Thanks for that. I now have it working. I just need a couple of other things sorted if you can help:

1.) How do I condition the data that is put into a text box? We I enter the data into the Date textbox (txtdate) I would like it to automatically enter the / / for me.

(i.e) I enter 01082005 and it automatically makes it 01/08/2005. In acces this is called an input mask

--------

2.) Currently when I click complete on the form, it inserts the data entered in to the next available row on the worksheet called "DATA". What i would also like it to do is insert this information onto another worksheet called "SOLD", but with a twist.

I need it to look at the date i have enter into a textbox at the top of the form (txtdate) and then look at the row on worksheet "SOLD" that the dates match and then enter the data into that Column. Please see Doc at http://www.goodtimezinc.co.uk/test_database_update.xls as the file is big to upload

(i.e) on fomDailyInput I enter date 05/08/2005 and enter 5 in 3 minutes textbox and 20 in 12 minutes textbox.

I would like it to enter the data into (WS) "DATA" as it is now and also enter the data into (WS) "SOLD" in column I Rows 5 & 8.

But obviously the date, column and row will change with every entry.

Thanks in advance.

[Ger Plante]

Hi Danian,

All I can do is offer advice here Danian as I am quite busy myself here at work. I remember using Input mask before, but I dont know if it is available in VBA. You can insert slashes into any string at any position using the Mid, left and right functions once the string has been entered in the text box.... Due to the nature of users they could enter dates in any shape or form and you are not limiting them in any way so to speak. I wouldnt recommend this approach.

Entering dates and keeping them consistent with dates already entered by previous users in a freeform textbox is nigh on impossible I would say. The traditional approach is to use 3 seperate text boxes (one for Day, one for Month and one for year), with Slashes fixed between the three text boxes (Slashes in a text label).

As mentioned above you can put spin buttons on these text boxes to increment and decrement the values of day/month/year independantly and automatically check to make sure they dont go over a certain value (e.g. 31 for day).

You can then validate any string (with or without slashes or hyphens) using the "ISDATE" VBA function to make sure that what the user has entered is valid.

However, to make absolutely sure that your users enter dates in a correct and CONSISTENT format and will always be valid dates, then I would strongly urge you to review this article

http://www.ozgrid.com/VBA/excel-calendar-dates.htm

Its a calendar control that allows the user to select a date. This control placed on a user form can be launched from your current userform when the user clicks on the text box (the "Enter" event for the text box).

Part 2 below is quite Programmable... You first need to use the .find method on sheet 2 to find the valid date you entered on the userform. And since each category is in the same row everytime, you can safely use the .offset command from the address of the search result of the find you just did (offset zero columns to the right and x rows down)

Sorry I cant give you code per se, but hopefully this will put you on the right track.

Ger

[JF]

Whats a spinner????

A spinner is a control from either the form or controls toolbar. It allows the user to click on the up/down arrow and increment the number in the attached cell by the amount you set.

in my example, I set the forms spinner to increment by 10 and the controls spinner to increment by 5.