Posts by mscola

    Re: $180 check/fix errors and clean up code

    The customers are the ones who are giving me problems (in the worksheet!) :)
    When a user adds a new record (such as a new order, or a new subscription) - and the customer is not in the table - he/she should be able to add a new customer without having to close the data entry form.

    Re: $180 check/fix errors and clean up code

    I'm sorry about the version .. I should have mentioned that I'm using Office 2010. The language is German.
    Shall I give you the instructions here or by email? I'm going on holiday tomorrow evening and that's why I cannot add an extension
    But I can tell you what it is causing me the biggest headaches...
    Go to "Kunden" (=customers). That's the second button in the custom toolbar. Then click on "New customer" .. keep adding several customers... until you get an error message. (I get an error message after having added three customers)
    So my question is: why do I get such an error message?

    Re: $180 check/fix errors and clean up code

    Hello everyone

    Thanks for your replies. One problem has to be with the listobject.add and listobject.delete method.

    For example with customers:

    open the userform which displays all customers. Try adding new customers from there.
    I got an error after having added three customers. I get a run-time error -2147417848
    Excel freezes and it looks like this:


    Did I write the correct code to add the customer to the table.

    For example with shops:

    Doubleclick on a row in the sheet "ShoppingTaxi". A userform will display the selected data which should allow the user to make changes. That includes adding new shops and customers.
    I get the same error message as above

    How would you rewrite this?

    The ocx file can be downloaded here:

    I use Windows 8 at home and windows 7 at work and get the same errors

    Thank you for helping me.


    I’ve been working on this project for a few weeks and I’m almost finished. But there are still a few bugs and I appreciate it if someone can help me finishing this project.

    What it’s used for
    I work for a local charity in Lucerne and we employ long-term unemployed people or people with slight mental handicap. We offer a home delivery (called ShoppingTaxi) as well as recycling service (RecyclingTaxi) for private households.
    A courier picks up groceries that had already been bought by a customer from a shop and delivers everything to their home. Everything is paid with badges (“vignetten”). Badges are sold to shops and directly to customers.
    The recycling service works by subscription only. Our customers receive a bag which can be filled with recycling material such as bottles, cans etc. and we pick up their bags once, twice of four times a month. Pickup day is based on the street/nr.

    What I do with the spreadsheet:
    I add all orders, new subscriptions, pickups for the recycling service as well as all badges sold.


    Data is added with userforms only. Existing records can be changed/deleted by double-clicking on the row. A custom tab Logistik contains all buttons which will open the userform. The custom tab has 3 groups: 1) an overview (Übersicht) which will launch userforms with detailed sorted by customer, shop, courier or subscription. The second one is for the shopping taxi service and the third is for the recycling service.

    A lot of the calculation is done in the background, in the hidden sheet called “Berechnungen”. I did not translate the worksheets. Almost all controls are in English and some labels too.
    There are several listviews in the userforms. When a user double-clicks in a listview, another userform will open which allows the user to change or delete the data.

    The problems
    The biggest problem is that if I add new customers/shops and/or delete them, I get error messages and then Excel is frozen – it’s like Excel is still trying to calculate. I think the problems are the listobjects and maybe the way the userforms are loaded: Because I allow (for example) a user to add a new customer when recording an order, there is more than one userform open. I use global variables what userform was opened.
    I got some help at the beginning but I’m the only one working on this project and I am happy to pay for someone to check/fix the errors and clean up the code. I am sure there are other ways, for example, to check for open windows/userforms and to make sure that the new data has been added to the listobject.

    This is a working copy. Please do not delete any records as I am going to use this again.
    I am happy to pay you USD 180 from my pocket money for help.
    Can someone help me within 48 hours?

    As the file is quite big (1.5MB), it can be downloaded from my Skydrive storage:…&authkey=!AAQG6aimVBBV3bs

    Thanks a lot

    Re: Get Lowest/Highest Date from Table in Excel 2010 without Sumproduct?

    Thanks a lot guys.

    As I work with userforms, I use hidden sheets for calculations. Do you recommend using Pivot Tables or should I use tables with formulae instead?

    Is it possible to, in order to display the data in a userform, search (with e.g. Index) and calculate (e.g. SUM) in a Pivot Table the same way I would do in standard Tables?


    I have a table which contains the customer ID and the date of purchase. I would like to calculate how many orders were placed in a given year, and the date of the first and last order of that year. The year's date is in Cell H1.

    I tried using COUNTIFS but that one fails because it cannot calculate with YEAR(). Is there a way of doing this without the use of SUMPRODUCT, because I've read on several websites that this formula is volatile formula. Volatile formulae should be avoided?
    If there is no other way than using SUMPRODUCT, I'd appreciate it if you could help me with this.

    Thanks, Massimo

    Re: Search in Listview

    Hello cytop

    I've tried your suggestion on all worksheets and made some amendments so that it works with my project and it works very well!

    Thank you so much for the time taken to help me.


    Re: Search in Listview

    Hello and sorry for my late reply. First of all, thank you for the time you took to answer my question.
    I've implemented the listview on several workbooks and it works fine but I came across to issues:

    How do I set the search function "jump" to the next item, if there are several items with the same name in the listview?

    You wrote that I could use a LIKE operator. Do I have to replace = with LIKE?

    I've attached the workbook and added more data to it and resized the listview.

    Re: Search in Listview

    I gave it a try and added the following code:

    I get an error message every time I click on the command button.
    The subitem is the "Column", isn't it?