Posts by mscola

    Re: Change Value in Listobject


    Hello Stephen


    I should have explained my problem a bit in more detail, sorry for that.
    But the code you wrote is what I need.
    Brilliant!


    Thanks a lot


    Maria

    Re: Are listviews slow?


    I've just had a look and it works great! I also appreciate it that you've added the following code to speed up the Listview:


    Code
    1. ' Prevents the ListView from updating while items are being added. Stops the
    2. ' flickering effect and speeds up the code as Windows does not redraw the control
    3. ' THIS WILL NOT WORK IN 64 BIT versions of Office. 64 Bit Windows is fine, just Office
    4. Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long


    I've added it here in case someone has problems with slow listviews.


    I do have one more question. As you know, the orders can be filtered by year. The default year is the current year.
    When I change the year from to 2011 - I only get records from 2012 till today. 2011 isn't shown.


    Does this have to do with the listview?

    Re: Are listviews slow?


    After a lot of trying, I was able to make it work. The progress bar now opens in a separate userform.


    However, how can I make it work in the same userform?


    I've changed this

    Code
    1. Private f As frmOrders


    but that doesn't work.


    Would you mind having a look at it?


    I had to put the file on to my Google Drive as attachments here are limited to 200 KB.


    Thanks

    Re: Are listviews slow?


    Hello - I've managed to speed the listview up by a factor of 10 :-)


    Now about the Progressbar ...


    I would appreciate it you could help me with the code. I still can't make the progressbar work properly in the pop-up userform.


    Should it be opened in a separate userform or is it better to just have it on the same userform with listview?
    For example: Once the progress bar reaches 100% it, the progress bar is set to invisible. What do you think?


    This is the part of code which populates the listview with the array data.


    Thanks for your help


    Re: Are listviews slow?


    I have an order table which contains the customerID and the shopID, and a table customers and a table shop.
    I'm putting everything together in a listview. I would, of course, put a filter in to only show this month's orders when the form starts - with the option to show the rest based on the desired time span.
    Or shall I fetch only this month's orders and, when the user changes the timespan, fetch the data again? Wouldn't that take a long time?


    Progress bar:
    Still doesn't work as I wish.


    Userform with listview:

    Code
    1. Private Sub UserForm_Activate()DoEvents
    2. frmProgressBar.Show vbModeless
    3. GetOrderData
    4. End Sub


    Progressbar userform:


    Do you - or does anyone here - know of a code which would allow me to enter a max value for the progress bar.
    So if I know that I have 2000 orders, I could let the progress bar run accordingly? Or shall I ask this in a separate post?
    I shall reply a bit later (night time here).

    I'm loading 25000 records (with an array) into a listview. Is it normal that it takes almost 5 seconds for the userform to load?


    I tried adding a progress bar with the idea: show progress bar in a separate userform while the listview is being populated.
    This doesn't work either because then the progress bar is shown after the listview is ready.


    If the data does take so long to load (I'm happy to show you the code) is there a good way to at least show a progress bar at the same time?


    Thanks for your help


    Massimo

    Hi all


    How do I copy the content of a Table/Listobject column to another Table one without the use of VBA?
    I will be doing more work with this later on .. .


    Example: What formula do I need in order for the content of the left table (tblCities) to appear in the right one (tblCities2).


    [ATTACH=CONFIG]67359[/ATTACH]


    Thanks for your help.


    Massimo

    Images

    • tables.JPG

    Files

    • tbl.xlsx

      (11.56 kB, downloaded 77 times, last: )

    Re: Worksheet Formula: Find value in an array


    I did indeed forget to mention where the result should be. Apologies for that.


    I just want to be able to check whether a customer has (from the many subscriptions)
    at least an active one. The result should be in B14, B15, B16 I hope that it's a bit clearer now.




    ABCDEF
    1CustomerIDSubscription StartLengthSubscription EndActiveRemaining
    2101.01.201236531.12.2012FALSE-937
    3101.06.20159030.08.2015TRUE23
    4201.05.201336501.05.2014FALSE-456
    5201.01.201021030.07.2010FALSE-1807
    6201.01.20151011.01.2015FALSE-206
    7401.08.201136531.07.2012FALSE-1087
    8401.08.201540004.09.2016TRUE387
    9401.06.201290018.11.2014FALSE-259
    10
    11
    12
    13CustomerIDHas active subscriptions
    141TRUE / FALSE
    152TRUE / FALSE
    164TRUE / FALSE

    I've got a question about finding a value in an array. The following Table is called CustomerSubscription.


    I need to be able to tell, based on a CustomerID, whether he/she still has an active subscription. If yes,
    the value should be true, if not then it should obviously be false.


    For example, customer 1 has an active subscription - whereas customer 2 has none.


    What is the best way to solve this problem. I have no problems if each customer only has one subscription,
    but I'm unfamiliar on how to do this with arrays - that's what I have to use, right?


    I've attached the worksheet to this post.
    ozgrid.com/forum/core/index.php?attachment/66523/


    Thanks for your help.


    Massimo




    ABCDEF
    1CustomerIDSubscription StartLengthSubscription EndActiveRemaining
    2101.01.201236531.12.2012FALSE-937
    3101.06.20159030.08.2015TRUE23
    4201.05.201336501.05.2014FALSE-456
    5201.01.201021030.07.2010FALSE-1807
    6201.01.20151011.01.2015FALSE-206
    7401.08.201136531.07.2012FALSE-1087
    8401.08.201540004.09.2016TRUE387
    9401.06.201290018.11.2014FALSE-259

    Files

    • example.xlsx

      (12.59 kB, downloaded 73 times, last: )

    I have a table with orders and I used a pivot table to analyze/sort the orders by shop.
    The chart displays only the total number of orders by shop.


    [Blocked Image: http://i1188.photobucket.com/albums/z415/lufun/th_screengrab_chart.png]



    Is it possible to display the total number of orders by shop as well as the the total number of orders for all shops like this:


    [Blocked Image: http://i1188.photobucket.com/albums/z415/lufun/th_Bild1.png]


    The workbook can be downloaded from my dropbox: https://www.dropbox.com/s/yzbczwvqi8rqqt2/Orders.xlsm


    Thanks for your help


    Massimo