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

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Can't anyone help with this?
If i am going about it the wrong way, could someone advise me please

Thanks
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Active Member
Joined
Aug 22, 2006
Messages
259
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
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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

Active Member
Joined
Mar 23, 2005
Messages
448
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

Active Member
Joined
Aug 22, 2006
Messages
259
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
 

Forum statistics

Threads
1,144,370
Messages
5,723,964
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top