1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA/Macro

Discussion in 'Business Applications' started by SlowHnds, Nov 23, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I am trying to design an automated shopping list for Housebound elderly and disabled persons.

    In Excel I have a shopping list that is multiple worksheets with one column and then with different categories for each worksheet. Ie. Produce, Dairy, Meat, Baking, Staples, Canned, Frozen, Cereals and so on

    To use it I want to use VBA or a Macro to Turn the Filter on, let the user select the items wanted from the list. Copy the filtered list to the WorkSheet "ShoppingList" then go to the next work sheet and again turn on the filter and select the items the client wants on the shopping list then copy the filtered results to Column B of WorkSheet "ShoppingList". I want to do this for all the worksheets. Produce, Dairy, Meat, Baking, Staples, Canned Goods, Frozen Foods, Cereals, and so on.

    When I try to make a macro it records the items selected when the filter is turned on. I need the macro to pause at that point while the individual items on the list are selected and then continue once ok is pressed.

    Any one know how to edit the macro to make it work correctly?
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, I'd like to take a look at what you have now. If you could post a working sample with your macro I'll see if I can come up with something
    I'll see which version you're using once you attach your file, but it's good practice to metion the Excel version you use.
     
  3. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    version is 2007 - some people are running 2010. These are donated software to the seniors society and the Society gives the software to seniors that have need of the software.

    Attached is in 2007 as zip.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I'll check it out and let you know, 2007/2010 is not much different, if there are also people using 2003 then my advise is save it as an xls file which can be used by all versions.
    I'll update as soon as I have something, it's bed-time here (midnight)
     
  5. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    The copies donated are either in 2007 or as of this April in 2010. Small town 2000 residents and we've had 31 copies of Office donated to the Seniors Society. Which for the size of the Town we're doing well. Hopefully we'll get another 20 or so this year.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Why are you using an autofilter instead of data validation? Also could you explain how the macro is going to be triggered? Will you be firing the macro manually or using an event such as a button click?

    Rollin
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, like Rollin_Again asks but more important, what do you want the macro to do.
    You've got a shteet named SHOP will this be the sheet where the made choices are to be collected?
    If that is so then this will be reset to blank so that a new shoppinglist can be created?
    Who is going to maintain the other sheets? The product lists?
    If the idea is that a person or persons can fill in his / her own shopping list, then I imagine that you want them to be able to select either Meat, Drinks, what ever en create a shopping list?
    I don't think you need filters for that and like Rollin correctly says it's data validation what you require.
    I'll put some code which I think (with the information I imagine) you want and post it.
    Won't be 'til later this evening since I am currently at the office but used the lunch break to see what you needed.
    Any aditional info you have will help understand it better.
    Will the file be on individual PC's?
    Will they print?, mail? the shopping list?

    You see, as you go on more questions arise
     
  8. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    The idea is this, we help housebound seniors to type out a fixed shopping list of all the items that they use. It's a static list.

    Once a week they run the macro, CTRL T (or a button doesn't matter) They can then select only those items from the static list that they need. This list will be emailed to the Seniors Society - Shopper, using a separate macro, The "Shopper" does their shopping for them and delivers their items.

    We have tried having them do this manually, but it doesn't work well. We are dealing with the elderly (older than I am and I'm in my late 50's) and anything we can do to help them and automate the process they appreciate and so does the shopper for the week as it cuts down on errors in shopping considerably

    I don't understand how data validation would help in this.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Okay that's clear, but the choice of products? Can they at least do some selctions? Quantities?
    I'll see if I can put something together and keeping in mind that they're mostly elderly people I'll see if I can make it as simple and straightforward as possible.

    I imagine you have the mailing macro ready.
     
  10. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    If I can get the selection filter to work in the macro I can add a column for QTY and include that as part of the copy. They'd have to fill the QTY before running the mail macro.

    The start of the macro will erase/delete the previous shopping list generated at the start of the macro. Or a new Shopping List can be created as a new work sheet for each week. Keeping previous shopping lists would be a good idea as it may help to solve "disagreements" about what the client had requested. (doesn't happen often but it can be acrimonious)

    The mailling macro was the easy part.

    Sub Mail()
    '
    ' Mail Macro
    ' Mails the shopping list
    '
    ' Keyboard Shortcut: Ctrl+Shift+M
    '
    Sheets("Shop").Select
    Sheets("Shop").Copy
    Application.Dialogs(xlDialogSendMail).Show
    ActiveWindow.Close
    End Sub


    Thanks to everyone for looking at this and for helping out it is appreciated.
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I haven't gotten to the actual shopping list yet but I started some Admin basics such as range names etc

    What the macro does is is set all the basic values to allow for pick lists
    If you press Ctrl+s to open / show the userform

    I have not added any further code but this is just to see if it's a little like what you had in mind.

    I'm off to bed here now. Will read you findings tomorrow
     

    Attached Files:

  12. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    That is too much of good thing. I need something truly basic where the clients can see all their choices in one section.

    The filter, shows the list of items which is great for the client. Manually it works well. I just do not know the code to automate it.

    I added a column for amount of an item. I've used name ranges on each worksheet, and the macro's email are in place. The only blocks remaining
    1. Automating Filtering Selection
    2. Copying to next empty column right 4 times and then next empty row down in A, B, C, D and then down again until all categories have been copied.
    3. Filling in the quantities/amounts on the list.

    I've attached the next iteration if that helps at all.

    Thanks for your work so far it is greatly appreciated. I'm going to be picking that apart for awhile figuring out how it works and how I can use it in the future. I just don't think it's what I need in this situation I need something less elegant and more simplified.
     

    Attached Files:

  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Sorry but I still don't understand why autofilter is being used. What criteria is being used for the filter?

    Rollin
     
  14. jones_joli

    jones_joli

    Joined:
    Nov 25, 2011
    Messages:
    4
    The concept is, we help housebound senior citizens to type out a set grocery list of all of the products they use. It is a static list.

    Once per week they run the macro, Control T (or perhaps a button does not matter) They are able to then choose only individuals products in the static list they need. This list is going to be e-mailed towards the Senior citizens Society - Shopper, utilizing a separate macro, The "Shopper" does their searching for them and provides their products.

    We now have attempted getting them do that by hand, however it does not work nicely. We coping the seniors (over the age of I'm and I am during my late 50's) and anything we are able to do to assist them to and automate the procedure they appreciate and thus does the shopper for that week because it decreases on errors in shopping substantially

    I do not know how data validation would assist in this.
    __________________
    [​IMG]
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    The autofilter is much more complicated than what it looks like at first sight, but...
    Talking of ages, it's not the age, (I'm 62) but it's the simple computer litteracy which is what plays a rol with elderly, my father could and would not accept the fact that a computer did the 'thinking'

    If the idea of the user form to enter tha list applies I'll go on with that because then you can just show what the can do and not offer other unnecessary infromation.

    Looks complicated since there is nothing finished in my code but I think I could come up with a workable result, just say Go and I'd like to put some time into it,
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1028206

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice