GET YOUR TEETH INTO THIS (Sounds easy BUT..................)

[Grizlore]

I need to calculate a total TareWeight and display it to the User and this must be done via UserForms.

Sounds easy, but I am having trouble.

Help wil the VBA code would be very much appreciated!!!

Basically, all I need is the total Tareweight, but obtaining it is the problem. I need to filter lots of worksheets to obtain the right weight.

TareWeight = InsertTare + GlueTare + BagTare + CartonTare

Doesn’t this sound simple?

I have four worksheets…. right, the easier ones first

“Insert Weights”
This contains two columns “Insert Type” & “Weight”.
Via List boxes, the User should select the “Insert Type” and the code should pick up it’s adjacent “weight”

“Glue Weight”
This contains three columns “Plant”, “Line” & “Weight”
Via List boxes, the User should select the “Plant” & “Line” and the code should pick up it’s adjacent “weight”. It would have to filter the “Line” after the “Plant” is selected.
“Bag Weights”
This contains four columns “Plant”, “Line”, “Size” & “Weight”
Via List boxes, the User should select the “Plant”, “Line” & “Size” and the code should pick up it’s adjacent “weight”. It would have to filter the “Size” & “Line” after the “Plant” is selected.
“Carton Weight”
This contains five columns “WO Number”, “Size”, “Name”, “Date” & “Weight”
Via List boxes, the User should select the “WO Number” & “Date” and the code should pick up it’s adjacent “weight”. It would have to filter as “WO Number” can have more than one date.

Can anyone help please???

I need to know the code to filter each criteria, so the list box is populated with the filtered info, until the required weight can be found. This need to be done for the four elements and then... add them up.

Any help would be much appreciated!

 

[Grizlore]

Can't anyone help with this?
If i am going about it the wrong way, could someone advise me please

Thanks

 

[GlennUK]

You are asking a lot ... that you be taught how to create userforms, write VBA, or have VBA written for you, and have it all perform the tasks you require. But you haven't explained why ....

this must be done via UserForms

... you've said this? Why can't it be done via formulae exactly?

 

[Grizlore]

You are asking a lot ... that you be taught how to create userforms, write VBA, or have VBA written for you, and have it all perform the tasks you require. But you haven't explained why ....

this must be done via UserForms

... you've said this? Why can't it be done via formulae exactly?

I can create Users forms, I know a little about VBA

I need two things really.

1. How to filter the columns, so i can obtain the "weight" i need. (By filtering I imagine i would need to know how to populate the next next listbox to enable the next filter.)
2. How I can pick up this cell value after the filtering.

Sorry if this is a big ask. I'm just looking for pointers and hopefully some example code!


Help appreciated

 

[GlennUK]

Look at this, which is done using formula in defined names and data validation, and see what you think ( still not user forms or vba )

http://www.contextures.com/xlDataVal02.html

 

[Grizlore]

Look at this, which is done using formula in defined names and data validation, and see what you think ( still not user forms or vba )

http://www.contextures.com/xlDataVal02.html

Thanks, but the individual spreadsheets will keep having new items added, and therefore wouldnt this method need to have things redefined.

Also the data and spreadsheets needs to be hidden from the Users and therefore I was thinking that a Userform is needed.

 

[gingerafro]

You could set up a dynamic range. There is a bit in Debra's site (contextures) to help or search the board.

I think I am right in saying that if a user wanted to 'get around' the userform and see the raw data, they could, so you may as well use Glenn's method.

 

[Grizlore]

You could set up a dynamic range. There is a bit in Debra's site (contextures) to help or search the board.

I think I am right in saying that if a user wanted to 'get around' the userform and see the raw data, they could, so you may as well use Glenn's method.

I did have a cunning plan to stop users viewing the worksheets, with or without macro protection