Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    For the future: you can attach zip files. The size of a zip file containing your workbook is just 61 KB.
    You can include clickable hyperlinks in your post: <!t>http://snipurl.com/eo81<!t>"]<!/t>http://snipurl.com/eo81<!t>[/url]<!/t> results in http://snipurl.com/eo81. See <!help=19>Help 19<!/help> for more info.

    The "bullets" in the cells highlighted in blue are radio buttons from the Forms toolbar. You can delete them in several ways:
    - Right-click one of them, press Esc once to cancel the popup menu, then press Delete. Repeat for the others.
    or
    - Display the Drawing toolbar. Click the Select Objects button (the NW arrow). Drag a rectangle around one of the groups of radio buttons to select them (take care not to include other objects), then press Delete. Don't forget to turn off Select Objects when you're done!

    Copying L4 to the other sheets is easy enough, but what exactly do you mean by "hopefully continue this magic all the way down the column"? Which column, and how far, and when?

  2. #2
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro/Formula in a Form (Excel 2002)

    I actually need help with two things in this form.

    1. Clearing the cells highlighted in blue on the Sheet labeled Cash Receipt (as in take out the bullet marks). No matter what I've tried, it simply will not clear the cells. I can't delete the cells themselves because that messes up the other sheets in the Workbook.

    2. If possible, the cell highlighted in red on the sheet labeled Cash Receipt needs to automatically generate a new number (old number +1) AFTER the old number VALUE has been copied into the red cell on the sheet labeled Master AND the sheet labeled May 2. So to make it more concise, say in Cash Receipt in the red cell, I enter 100. I need the 100 to copy to cells I5 on the Master and May 2nd sheets and then come back and refill the original cell where I entered 100 with 101 and hopefully continue this magic all the way down the column. I hope that's clear enough.

    Any and all help will be greatly appreciated. Thanks!
    Pooja

    The file is 455kb so exceeds the file limitations here so I've posted it at Rapidshare at this link -

    http://snipurl.com/eo81

    which is just this link which I've snipped for convenience factor involved -

    http://rapidshare.de/files/1579555/WOPR_Ca...Master.xls.html

    Just click on the link, scroll down and hit the Free button, and click the link that appears after a few seconds. Hope I didn't break any rules by doing it this way and the file is ABSOLUTELY free of viruses/spyware etc on my end!

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    Thank you for letting me know. Most forums I participate in have also banned zip files so I didn't even try that option! A new zipped file is attached to this post.

    Problem 1. You solved it! Thank you very much!

    Problem 2. The clearest explanation for what I need done is - Copy cell L4 from the Cash Receipt sheet to cells I5 in both Master and May 2 sheets. Then in BOTH Master and May 2, cell I6 should contain the next consecutive number of which the VALUE is copied back to the original cell L4 in the Cash Receipt sheet. I filled them in so you can see what I'm talking about. Basically want I want is individual receipt numbers which are copied to the Master log but I don't want to manually have to input them. I'd prefer the Receipt form to automatically generate the next consecutive number, have the number copied to the log along with everything else and then have the next number automatically filled in for the next person to use. If you need me to be clearer, please let me know!

    Thanks much!
    Pooja

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    I am a little confused. When do you want this transfer to take place?

    Should it always go into I5 when transfering or do you have something else in mind. Your request seems to be to put L4 into I5 then replace L4 with the value +1, then put the new L4 in I5, and add one to it, etc... Is this really what you want?

    I imagine you want to do something else (and what about the other values in the "form", what happens to them?

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    I want the transfer to happen instantly and I definitely don't want the first row to be overwritten every time! Sorry for the confusion. What I want is to enter the VALUES from the first receipt of the day into row 5 on the Master and May 2 sheets. The second receipt of the day into row 6, the third receipt into row 7 and so on.... The receipt form itself will have to be printed so after the information is copied into the logs, I don't really need it saved. Does this explanation clear it up the confusion?

    Thanks!
    Pooja

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    Instantly when? what does the user do to trigger that he/she is done with the form and all the info should be transfered? I can't imagine that whenever the user enters something into any cell in that sheet, that only that cell info is transferred and a new reciept is then created. There must be something to tell excel that "user is done, save the info, increment the receipt number"

    Typically you would have some "buttons" on the form to "create new one", "add to database", "delete from database", "edit from database", "copy from database"etc

    What VALUES should be copied into the other sheets and where do you want them.

    What happens with the 15th receipt (may 2 allows only 14) and why does the master have space for more?

    Why do you need it in 2 places? It would seem to be less confusing to just put it into 1 place.

    I would also recommend putting the data into some "database" sheet rather than the sheet with all the other calcs on it. You could use pivot tables, filtering, etc to get the info from the database into another form.

    Depending on your databasing needs, Access might be more of what you need. You could create an actual form to input the data as desired and it would be entered into the database. Access has much more "built-in" routines to do databasing than excel.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    Sorry to say I don't know very much Access so can't create the form there just yet. Soon though, I'm starting training in it next month!

    I suppose something like pressing a Keyboard combination in the Receipt form to generate the NEW Receipt number could be the trigger to copy all the previous information into the Master/May2 sheets (if that's possible of course). Or if it's possible in Excel, maybe pressing an ENTER or similar button which I can insert into the form somewhere?

    As for why I need both the Master and the May 2 sheets, the workbook by the end of the month will contain a new tab for each day of the month. Pretty much I only need the formulas in the Master so that I can copy it over to set up a new tab with all the formulas/formatting etc. I can add more rows into the May 2 one, just haven't done it yet.

    Thanks.
    Pooja

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    You could have a button signal the "I'm done" routine. The routine would copy the items to the database and could then increment the number. You could even add data checks and validation (if desired) to ensure the info is entered correctly.

    If you are saving in MASTER, I still don't see the need to save it for everyday.

    I am not a proponent of having multiple "identically formatted" sheets. It is almost always much much better to create 1 master sheet with all the data and then you can use filtering or pivot tables or other "built-in" techniques to get the summary info.

    If you want help with macro coding, you will have to be very specific about what you need now and in the future. As it stands you can only put in 14 receipts before you have problems. If you want to put in master (always) and also a separate copy on a new sheet every day (not recommended) you would need code to add new sheets as desired and also to put them into the correct sheets.

    As it stands now, we don't have near enough info for doing even the coding basics as it is unclear what you want saved from the form and where you want it saved to exactly.

    Steve

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    Is this "Form" an Excel User Form, or is it a spreadsheet that you are entering data into? I think Steve thinks it is a User Form, but it sounds to me like it is a spreadsheet that is formatted to look like a form.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    It is indeed a spreadsheet formatted to look like a form.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    I know it is a spreadsheet that looks like a form. If you want to transfer the info from it to a regions on another sheet, you must treat it like a form and use buttons to indicate when to transfer the items.

    My impression is that more is desired than just updating the receipt number, the info entered must also be saved.

    Steve

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    I was thinking in terms of using the Worksheet Change event routine. When the user changes the cell in question, transfer the value to the other sheet(s).
    Legare Coleman

  13. #13
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    I'd have to go with Mr. Coleman on this. That's exactly what I'm trying to do. Run some kind of macro to automatically generate a number on the Receipt sheet and as soon as that happens have all the pertinent info I need copied into the log just gets copied and the next row/cell down is selected for the next entry on the receipt form. I just need some guidance on how to make this happen....

    Thank you to you both for trying to help!
    Pooja

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    My question is when do you want this code to run? What does the user do to "trigger this event" to happen? Since the receipt number will be generated by the code, it should not be changed by the user, so it must be different cell...

    Typically this is with a button or based on the user changing a particular cell. If a particular cell which one? Are you also looking for help with the code to
    "have all the pertinent info I need copied into the log"? If so we need info on what you want to copy and where you want it copied to.

    Steve

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro/Formula in a Form (Excel 2002)

    You can do that by putting the following code into the worksheet change event routine for the Cash Receipt worksheet:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("L4")) Is Nothing Then
    Worksheets("Master").Range("I5").Value = Range("L4").Value
    Worksheets("May 2").Range("I5").Value = Range("L4").Value
    End If
    End Sub
    </pre>


    See if the attached workbook does what you want.
    Legare Coleman

Page 1 of 3 123 LastLast

Posting Permissions

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