Posts by actuarish

Re: Simpe Autofilter Doesnt Work - To Autofilter Data Based On Cell Value As Criteria

My apologies for taking time to reply. Many THANKS, those two ideas both worked perfectly !!

Thanks once again.

Greetings.

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.

Sub Filter()


Dim k as integer
'k is value on cell B2 and the criteria range. The criteria range should be ">k"
k = Worksheets("Sheet1").Range("B2").Value
'Ive also tried Set k = Worksheets("Sheet1").Range("B2").Value but it also doesnt work
Columns("D:D").Select
Selection.AutoFilter
ActiveSheet.Range("D1:D500").AutoFilter Field:=1, Criteria1:=">k", Operator:=xlAnd
End sub

Please intervene.

Thanks

Greetings.

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.

Re: Completely Redesign the Microsoft Excel/Access interface forAutomated Reporting

Thank you for your response.

I really was curious and thought that interface was designed in Access 2007, I guess I should have read that part in the FAQ. Thanks for the information, Ill just forget about that, since seems alot of work and resources would be necessary.

Later.

Greetings.

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.

http://www.vosesoftware.com/vo…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.

Actuarish.

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

THANKS !! That code worked PERFECTLY.

About the Treeview data, Im sorry I cant share it, since it is VERY sensitive and personal, and had signed a non disclosure confidential agreement. The workbook I provided should just suffice, since it is in a similar format.

THANK YOU.

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

THANKS !! Ill give that a try and get back to you.

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

e.t.c

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.

Regards.

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 !! Ill take a look at that and get back at you.

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.

Moreover,

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.

Actuarish.

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

THANKS on the coding to expand the nodes !

Actuarish.

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
Next

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.

Actuarish

Greetings.

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

e.t.c

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.

Actuarish.

Re: Autofilter data based on listbox selections

Thank you for the links. I basically copy-pasted the code but it did not work the way I hoped. I will just post my issue in the forums and hope for the best. Thank you. Actuarish

Re: Autofilter data based on listbox selections

THANK YOU SIR !!!
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

e.t.c

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.

Actuarish

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...

Private Sub cmd1_Click()


Dim x() 
Dim i As Integer


ReDim x(0)


Application.ScreenUpdating = False


Range("G3:R3").Select


For i = 0 To ListBox4.ListCount - 1
    If Me.ListBox4.Selected(i) Then
        x(UBound(x)) = Me.ListBox4.List(i)
        ReDim Preserve x(UBound(x) + 1)
    End If
Next i


If UBound(x) <> 0 Then
    ReDim Preserve x(UBound(x) - 1)
    Columns("x").Select
    Selection.EntireColumn.Hidden = True    
End If
    
Application.ScreenUpdating = True
    
End Sub

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