Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Tally Total

  1. #1
    Join Date
    4th August 2005
    Posts
    8

    Tally Total

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,486

    Re: Tally Total

    Hi, welcome aboard!

    Its not much different to what you described above.

    VB:
    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th March 2004
    Posts
    561

    Re: Tally Total

    Quote Originally Posted by 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
    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

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    4th August 2005
    Posts
    8

    Re: Tally Total

    Whats a spinner????

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th August 2005
    Posts
    8

    Re: Tally Total

    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:
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,486

    Re: Tally Total

    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:

    VB:
     
    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

    VB:
    frmDailyInput.textbox3.value = 0 
     'initialise the rest of the text boxes that will contain numbers also.
    
    
    On the "complete" command button where you have...
    VB:
    Me.txt3mins.Value = "" 
     'and all the rest of them
    
    
    this should also be changed to
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    4th August 2005
    Posts
    8

    Re: Tally Total

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,486

    Re: Tally Total

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    12th March 2004
    Posts
    561

    Re: Tally Total

    Quote Originally Posted by Danian
    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Keep Tally Of Team Scores
    By matrix281 in forum EXCEL HELP
    Replies: 3
    Last Post: January 19th, 2008, 22:49
  2. Tally Report Of x
    By sriya in forum EXCEL HELP
    Replies: 4
    Last Post: August 28th, 2007, 15:41
  3. Tally rows
    By ceepee2 in forum EXCEL HELP
    Replies: 7
    Last Post: March 30th, 2005, 19:29
  4. Counting and a running tally
    By uncw99 in forum EXCEL HELP
    Replies: 5
    Last Post: March 11th, 2005, 04:10
  5. macros to tally information
    By seabat in forum EXCEL HELP
    Replies: 1
    Last Post: January 20th, 2004, 17:17

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno