Multipage user form and how to insert each user form into different sheets ?

[ellyna]

I'm using multipage userform and have different sheet in excel. I have no idea how to use the vba to insert the second userform into 2nd sheet of excel.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[dmt32]

You have merged cells in rows 1 & 2 in columns A, B & C on summary sheet - merged cells are just trouble - unmerge them.

I am very short of time at moment - post what it is you want to calculate & will see if can update for you

Dave

 

[ellyna]

You have merged cells in rows 1 & 2 in columns A, B & C on summary sheet - merged cells are just trouble - unmerge them.

I am very short of time at moment - post what it is you want to calculate & will see if can update for you

Dave

 

[ellyna]

1) Packing user form i manage to calculate on the "Material In, Start Up and Reject". As (Material In - Reject = Production Output) and (Start up + Reject=Total Rejection). The problem I faced is when i change the number at "Start Up" the figure do not change automatically on "Total Rejection".

2)Cartooning user form "Shift Time" combo box do not have the dropdown item ?

3)At summary user form i would like to calculate on the ((C/B), (C/A), and (D/C)) in percentage of each of the machine.

I will attach a photo on the summary user form calculation. And i will attach my other excel extrusion for your reference.
Thank you so much for your help.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Reference for you on my Extrusion Department that I have completed.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Calculation for User form "summary"

 

[dmt32]

Hi,
see if these updates for the packing & cartooning issues help.
I do not have time to work through the summary requirement at this time as with so many textboxes, probably would be easier to create a common code maybe via a class to perform the calculations.
In short term, you could just apply calculation in the textbox change events as needed.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dave

 

[ellyna]

Hi,
see if these updates for the packing & cartooning issues help.
I do not have time to work through the summary requirement at this time as with so many textboxes, probably would be easier to create a common code maybe via a class to perform the calculations.
In short term, you could just apply calculation in the textbox change events as needed.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dave

Thank you for your help dave. I will try on the summary and review your script back.

 

[ellyna]

Hi,
see if these updates for the packing & cartooning issues help.
I do not have time to work through the summary requirement at this time as with so many textboxes, probably would be easier to create a common code maybe via a class to perform the calculations.
In short term, you could just apply calculation in the textbox change events as needed.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dave

Hi Dave i have tried the script that you provided. I noticed that in packing the user form calculate perfectly but when i submit the record into excel sheet, the column on "Production Output and Total Rejection" do not show the value .

Dropbox - File Deleted - Simplify your life

www.dropbox.com

1)"Production Output and Total Rejection" do not show the value .

Dave, I tried using your script on the summary user form . And the output that i get is below , but it does not calculate the total percentage . Maybe you can see my script and correct me if i'm wrong and for the rest i will do it myself.

Private Sub TextBox3_Change()
      TextBox4.Value = IIf(Val(txtTextBox5) > 0, Val(TextBox5.Value) / Val(TextBox3.Value), "0.00%")
End Sub


Private Sub TextBox2_Change()
    TextBox6.Value = IIf(Val(txtTextBox2) > 0, Val(TextBox5.Value) / Val(TextBox2.Value), "0.00%")
End Sub

Private Sub TextBox5_Change()
    TextBox8.Value = IIf(Val(txtTextBox5) > 0, Val(TextBox7.Value) / Val(TextBox5.Value), "0.00%")
    
End Sub

2) Does not calculate the total percentage . Maybe you can see my script above and correct me if i'm wrong and for the rest i will do it myself.

 

[dmt32]

Hi,
For the common AddRecord script to function correctly, the Tab Order for each textbox must be set correctly. I did this for the two smaller forms but ran out of time to work on the summary tab.

I have figured an approach that should negate the need for all those change events or creating a class.
If I manage to find time to complete this side of xmas will post link for you to try otherwise will have to be next week

Dave

 

[ellyna]

Hi,
For the common AddRecord script to function correctly, the Tab Order for each textbox must be set correctly. I did this for the two smaller forms but ran out of time to work on the summary tab.

I have figured an approach that should negate the need for all those change events or creating a class.
If I manage to find time to complete this side of xmas will post link for you to try otherwise will have to be next week

Dave

Oh i see. It's quite late for next week.
I will tried and play around first with the script see if i can find solution on it.

 

[ellyna]

Hi,
For the common AddRecord script to function correctly, the Tab Order for each textbox must be set correctly. I did this for the two smaller forms but ran out of time to work on the summary tab.

I have figured an approach that should negate the need for all those change events or creating a class.
If I manage to find time to complete this side of xmas will post link for you to try otherwise will have to be next week

Dave

Hi Dave, I have done for all the calculation on "Summary" user form and it calculate according to what i want. Can you check on my script if i did it wrong.

Can you help me on
1) The data insert to excel sheet as the column are at column 3 instead of 2 ?
2) On the Packing excel sheet i still can't solve on the value show on production output and total rejection

Packing_shift_productivity v5.xlsm

Thank you very much

 

[dmt32]

Hi Dave, I have done for all the calculation on "Summary" user form and it calculate according to what i want. Can you check on my script if i did it wrong.

Can you help me on
1) The data insert to excel sheet as the column are at column 3 instead of 2 ?
2) On the Packing excel sheet i still can't solve on the value show on production output and total rejection


Thank you very much

To place data in correct order on worksheet you need to set the Tab Order of each control as required :VBA Userforms - Tab Order

I have updated summary tab with solution I think does what you want that negates need for all those change events - Solution uses a hidden sheet that is linked to all the textboxes in summary tab & it is this sheet that performs the calculations - I have only lightly tested & you will need to determine if this is producing correct result.

Really sorry, I do not have the time to look at the other part of your project this side of xmas but if you are using the common code I provided, then be mindful of what I stated above about setting the tab order of controls.


we seem to be out of step with version numbers but this is my latest version

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dave