Posts by actuarish

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.


    Im trying to autofilter a data range with the value of a specific cell as the criteria range, i.e filter a range based on the value of a cell. Here is my code, but it doest work.

    Please intervene.



    I have a range of values in column A, I wish to add all numbers by all other numbers, i.e for a specific value in the range, I want to add it to all the other values in the range, and output the values in a new range.

    For example for values 1,2,3,4 in range A2:A5, I want to output a new range the values;

    1+2 =3
    1+3 =4
    1+4 =5
    2+1 =3
    2+3 =5
    2+4 =6

    e.t.c, beginning in range B2. No number can be added to itself.

    Thank you in anticipation.


    I am a VBA novice, providing data analysis and reporting automation to a few clients. I also have an elementary understanding in VBA in Access. I stumbled upon the picture below and it tickled my curiosity.…sviewer/screenshots/1.png

    This is a screenshot from a report generated by a software called ModelRisk. I do not wish to learn how the specific analyses were performed or interpreting the chart, but I'd like to know how the interface was designed. I believe it is designed in Access 2007, and since I know the charting features in Access 2007 are terrible, I also wish to understand how to design similar charts. I also have a fair knowledge of ribbon customization in XML. My questions are:

    1.How does one place a picture/icon on the ribbon circle ?
    2.How does one design similar form tabs on the interface, with custom icons i.e labelled CummulA,Scatter,Trend,Spider e.t.c in the picture?
    3.How does one automate creation of such charts ?

    Would I require to learn a programming language, or are there utilities for such designs ? I also understand one can use C++ in Excel, what added unique capabilities would C++ provide in Excel that are not in VBA ?

    I wish to design standalone reporting and data analysis applications with a rather similar interface. I may also want to, for instance, for data in an excel worksheet, import it into the application, run complex queries, design excel pivot tables and charts then place the reports in a similar tabbed interface on separate forms, in a few seconds.


    Re: Populating a treeview control based on a data range on two columns

    OOPPSS !!

    Hi again, my apologies, I have a quick idea of instead using a listbox control instead of a treeview..just to complete and submit THIS project..

    Please help me populate a listbox control with the same data, with items such as:

    Beverage- Tea
    Beverage - Coffee
    Beverage - Soda
    Fruit - Mango
    Fruit - Banana


    The data is, or course in columns A for Category and columns B for the corresponding Item.

    I hope to also auto-filter the same data at the click of a button, exactly the same way I expected the treeview to work.Please also allow multiple selections.

    Thank you in anticipation.

    Re: Populating a treeview control based on a data range on two columns

    I have once again tried the best I can, without any success. I have even tried designing a new form interface with new controls with the same properties, reusing the same code, but keep getting error messages.

    It seems to me that the treeview object is more complicated than I thought. I think at this point, since im pressed for time and set to miss my project deadline, ill call it a wrap, though am very grateful of your assistance. I have learnt alot about the control, and I hope to try and make it work sometime later. I have understood populating the control, I believe I can make the filters work.

    Once again, THANK YOU for all your assistance.


    Re: Populating a treeview control based on a data range on two columns

    Hi, Ive tried to figure it out but without success.

    I'm actually using Excel 2010 and have tried it in Excel 2007 and the error persists. Ive also tried re-designing the userform and inserting a new treeview and renaming it appropriately, but the end result is the same. I have the correct object references in the VBA environment, and can comfortably use other treeview controls. Seems to me that the error is in the coding.

    Please intervene.

    Re: Populating a treeview control based on a data range on two columns

    Thanks alot for your assistance.

    I tried running the procedure by clicking the treeview control, and it results to an error message. Seems a certain feature is not available on my machine.

    An error states "Could not load object because it is unavailable in your machine" then another error

    "Compile Error Cannot find object or library " it also highlights the procedure "Private Sub tv_NodeCheck(ByVal Node As MSComctlLib.Node)"

    The same happened when I tried on another computer.

    Please advice.

    Re: Populating a treeview control based on a data range on two columns

    THANKS ! I have not yet fully figured it out, but I understand what is going on, and believe will figure it out. THANK YOU.

    However, I've been a bit confused along the way, and since I'm VERY pressed for time, I seek your indulgence to help me clear it out.

    I have designed a custom auto-filter form for a very large data range. Its rather complicated for me, but every other part is complete except for the treeview part.

    Given the initial data range (in the workbook), if user selects a specific parent node, then range (A1:B10) in sheet1 is auto-filtered by the parent selections in the treeview, with the autofilter criteria range being the selection(s). For instance, if user selected "Beverage", then only rows where the column A value is "Beverage" are visible.

    Further, If the user selects "Soda" and "Coffee" in the child nodes of the "Beverage" parent, then the autofilter should:

    1. Autofilter column A of the range, with the Parent "Beverage" as the criteria range (even if "Beverage" parent was not selected).

    2. Autofilter column B of the range, with the selections passed as the autofilter criteria range, i.e "Soda" and "Coffee".

    Such that ONLY columns where the value in column A is "Beverage" AND columns where the values in column B are "Soda" and "coffee" are visible.


    If user selects " Beverage" and "Fruits" but does not specify the items, then ALL beverages and fruits items are visible.

    It should also be flexible enough to allow multiple selections of parent and child nodes. I hope im not vague. Please ignore the root node for now.

    I understand that passing the multiple selections as auto-filter criteria might be problematic, that is why I hoped the selections would form a range or an array. Then I hoped to proceed from there.

    I really hope to complete this very soon. Thank you in anticipation.

    Re: Populating a treeview control based on a data range on two columns

    Thank you for all your assistance thus far. I have been confused by the code above, especially where you mentioned tags.

    I wish to run separate procedures on the parent selections and on the child selections. That is why I hoped the selections would be converted into a range or an array. I am hoping for something like:

    For i = 1 To {all parent selections}      
    Run procedure  
    Next i

    For j = 1 To {all children selections}  
        Run procedure  
    Next j

    Please guide me further. Thank you in anticipation.


    Re: Populating a treeview control based on a data range on two columns

    THAAAAANKSSSSSS !!! That worked absolutely PERFECTLY. Thank you, and thank you for the quick response.

    I have a few issues though,

    1. How can I have all the children visible on loading the form containing the control? The parent nodes are all collapsed.I need the children visible, since the end users do not understand the control. I do not know much about the control, but im thinking something like:

    Dim i
    For Each i In tv.nodes.count
     tv.Nodes(i).Expanded = True

    This doesnt work, please guide me further.

    2. I have added checkboxes on the nodes, so as to enhance multiple selections. I wish to run a procedure based on the selections.How can i specify the selections ? For instance,

    For i = 1 to {all parent selections}
            Run procedure
    Next i
    For j = 1 to {all children selections}
           Run procedure
    Next j

    PS: I would be more than grateful if the selections can be converted into a new range or an array.

    Thank you in anticipation.



    I would like to populate a treeview control with data on two colums on a range. I have never used it so im unfamiliar with its events and properties.

    I have two columns of data in columns A and B, titled "Category" and "Item".I would like to populate treeview control with UNIQUE values in the "Category" column as the parent nodes, with their corresponding values in "Item" as the child nodes. I want the code to fetch the values of each parent and its corresponding children. The data range is as:

    Category Item
    Beverage Coffee
    Fruit Apple
    Snack Bisquit
    Beverage Lemonade
    Fruit Mango

    e.t.c, such that the data on the treeview control is as:

    Node(s) Child(ren)
    Beverage - Coffee
    - Lemonade
    Fruit - Apple
    - Mango


    The data range ( "A1:B500") is not formatted in any way.I want the code to populate the control on loading a form containing the treeview control.Please comment on any code so that I understand what is going on.

    PS: I would also later want to run a procedure based on the selections.

    For the sake of clarity, Please find attatched a workbook.Thank you in anticipation.


    Re: Autofilter data based on listbox selections

    That was very helpful and I can call it a wrap.I have also learnt a lot and keen on improving further.

    Moreover...I hope you do not mind....:oops:

    I had another tougher issue, related to the previous one. It pertains to a similar project and am completely stuck. This time, due to the data format, It would require a Treeview control.

    The data format is as:

    Beverages - Tea
    - Coffee
    - Lemonade
    - Soda

    Fruits - Mango
    - Apple
    - Orange


    the categories are in column A while the items are in column B. I am not familiar with Treeview properties and events as Ive never used it. I want code to populate the data to the treeview from the two columns, with Column A values as the Parent and its corresponding values in Column B as the children. I believe it is possible using Arrays, or any other way, and thereafter apply the treeview selections as filters to the data range at the click of a button. I hope I'm clear.

    I look forward to your guidance. Thank you for your valuable help thus far.


    Re: Autofilter data based on listbox selections

    THANKS ! That worked like MAGIC !!!!

    That was more than I could have asked for.It blew me away, considering you even came up with an illustration.THANK YOU SIR !!

    OH! and by the way...there's a final listbox...yes there is.... where the data is in Months (Jan to Dec) over row range (G3:R3). I want the code to be able to hide the month column for unselected items, i.e hide all other columns if the user selected Jan to April.

    As always, I have given it a try, (based on the filter code for the other listboxes) but It doesn't do anything. Here's what I have...

    I hope to add this as the final piece to the previous code. Please direct me further. Many THANKS.