Creating a user interface for an excel file.

Hello hnkkseanH,

It would be best to submit your workbook for review. Seeing the data and the layout are key to developing an user interface. Be sure to remove any confidential material before posting your workbook. The maximum allowable upload file size is 100 KB.

Sincerely,
Leith Ross

Well I hate to say it but the file is 30 mb, without any of the data. I am not sure why this is and I would imagine that it should be much smaller. But either way there is no way for me to upload it for you to look at it.

The basic layout consists of:

6 charts (scatterplots) made up of data from the two sheets
2 Sheets. one with the good data and one with the bad (differentiated on the charts)

the two charts have the same like 40 columns and headings. Two of which need filter controls. These being Valve Model and Test Running.

I believe that I need help in understanding VBA but I don't know where to input code nor do I no anything about coding in it.

The UI just needs to control the filters for the two columns on each of the two sheets.

sorry double post

I have just been introduced to the UserForm command and this seems to be a relativly good first step. It seems possible to create a good UI from this. Now the clincher will come from the ease or difficulty of using it to control the filters.

I am sorry that I am so in the dark about this. I just don't really want to go around spending money on the training manuals when all I need help on is this one thing.

If anyone has a problem in Aerospace Engineering then maybe I can repay with a little knowledge

Well, after doing some digging around I have found this tutorial type excel file. Now I have figured out how to implement the VBA code into something i can use (and making it cover two sheets) What I can't seem to do is figure out how to create the button on my worksheet (I can only work with this sample here, can't apply it to my wksht).

please take a look and let me know what you think.

Try the View menu, then Toolbars, tick Controls Toolbox.
on the controls toolbox select command button.

Now hows about linking that command button to the code written out for the filter control?

Hi

Here's a non forms approach that may give you some ideas. As I've got no idea about your structure, this is just a guess. I've only put a graph on sheet2, but you should get the idea.

Make the selection changes on the first sheet and see how it goes.

rylo

Alright this is the 10th time I have tried to post a reply I hope it works....

So my setup is like this:

6 pages upfront are charts that use data from both of the following sheets.

The first sheet has about 50 or so variable columns and around 300 or so rows (this sheet is the good data)

The other sheet is the same as the first except for the data (which is bad in this case)

A simple autofilter is good enough to filter the data by valve model and test running but the company wants something that easier to use. Like a UI that can control the filters on both sheets. If I could get something like the example I posted to work then I would be in serious buisness. However, no matter what I do I can't seem to get the data to update.

Hi

Try putting up a representative example file to one of the free online storage sites, and put the link and any necessary passwords etc onto a post. If we can see your structure, it may help to resolve your problems.


rylo

http://www.box.net/

login:

[email protected]
PW:

hnkksean

Its the only file up there

Hi

What are the 10 valve types that you have? If as you say there are only 10 types, then it will be easier to fix the combobox values rather than trying to extract from the data.

Also, can you update your file to:
1) remove all the data from columns BV:BW. All these formulas will be adding to the size.
2) Put in some sample data. Nothing confidential, but enough that will allow a test of both the filter to work, and the graphs to update.

rylo

Well I put up the file with some data so the graphs should work out and it will let you know the 10 valve types.

As for the first question, no none of the data can be removed since thats the way it comes from the online database and apparently it can't be changed (its a Japanese system that I know nothing about) but in the long run I don't think it will matter much. Lemme know what you think.

Hi

Must be missing something here. There are 54 unique values in column W (Valve Type). What are the 10 types you are referring to? Which column has the data?

Test Running (Column BS) seems to have only 2 options Start-up or NORMAL.

rylo

Rylo,

Valve model is the important one, not valve type.

Also yes, there are only two options for test running. Unless of course you count the ones with blank cells.

Hi

Added a file to the storage site. See if that gives you some ideas.


rylo

that is probably one of the most beautiful things I have ever seen in my life.

Seriously that is basically exactly the userform I have been trying to create for days now with no luck. Thank you!!!

one question though. how can I bring the UF back up after it has been closed. Besides closing the file and opening it again?

BTW you are amazing.

Sorry, but I seem to be having some trouble in posting the rest of my data into the file. I think that I can work that trouble out though.

However, I was wondering if there was a way to make the userform still work if you only fill in one of the fields. Say I want all of the valve models but only the start-up condition. The UI doesn't seem to like that very much.

I owe you big time for this

Hi

You can run the workbook_open code to reraise the form. I don't know where your users will be, but you could either put a button onto the 2 data sheets to run the code, or perhaps add a button to an existing, or a new toolbar.

I made the form modal, so you can go to the sheets / charts while the form is still open.

rylo

Hi

Try changing the CommandButton1_Click code to:

Please Login or Register  to view this content.

rylo

rylo,

Adding that script to the command button worked really well.

now the only problem I have left is bringing back the UF after being closed. Is there any way that I could add a persistant toolbar to the workbook that would have the button for the UF in it?

Sean

Sean

Added another file with a specific menu to open the form if it has been closed.


rylo

Rylo,

Thanks a lot for all your help. I have everything working now. I just wanted to know how you created the macro called openform. That was the only thing tripping me up now.

Thanks again,
Sean

Sean

In the VBE, Insert, Module will create a general module.

In that module, I typed in the code as given.

Is that what you are trying to determine?


rylo

I suppose it is.

Thanks

Sean

Put a copy of the updated workbook on the storage site and I'll have a look.

rylo

the newest incarnation is uploaded

Sean

Sean

Simple thing. There are no headings in columns BT and BU. So when the autofilter is applied, it stops at column BS. This means it doesn't have a field number 76. If you put something like xx in BT1, and yy in BU1 on both sheets, then run the filter, it will work.


rylo

of course!

Thanks

Will adding a " " to each of the comboboxes create a blank that can be selected to make it easier to not use a filter. So instead of deleting whats in the combobox you can just select the blank? I'm going to give it a shot.

Nevermind. that worked.