Calculation error

E

ellyna

Banned - Rules violations
Joined
Nov 29, 2020
Messages
89
Office Version
  1. 2013
Platform
  1. Windows
Hi, when i submit the user form 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 www.dropbox.com

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


2)The output that on multipage summary is below , but it does not calculate the total percentage . As i just do the first one and the result do not showed calculate of percentage.
VBA Code: 
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
1608615762037.png
 
Sort by date Sort by votes
maabadi said:
Try this:
VBA Code: 
Private Sub TextBox5_Change()
If TextBox3 <> "" And TextBox3 <> 0 Then
On Error Resume Next
    TextBox4.Value = Format((TextBox5.Value) / (TextBox3.Value), "0.00%")
    TextBox6.Value = Format((TextBox5.Value) / (TextBox2.Value), "0.00%")
End If
End Sub

Private Sub TextBox7_Change()
If TextBox5 <> "" And TextBox5 <> 0 Then
On Error Resume Next
    TextBox8.Value = Format((TextBox7.Value) / (TextBox5.Value), "0.00%")
End If
End Sub

Private Sub TextBox13_Change()
If TextBox11 <> "" And TextBox11 <> 0 Then
On Error Resume Next
    TextBox12.Value = Format((TextBox13.Value) / (TextBox11.Value), "0.00%")
    TextBox14.Value = Format((TextBox13.Value) / (TextBox10.Value), "0.00%")
End If
End Sub

Private Sub TextBox15_Change()
If TextBox13 <> "" And TextBox13 <> 0 Then
On Error Resume Next
    TextBox16.Value = Format((TextBox15.Value) / (TextBox13.Value), "0.00%")
End If
End Sub
Click to expand...
Is it you just add this script in between the script ?
VBA Code: 
If TextBox5 <> "" And TextBox5 <> 0 Then

May i know what does it means? Sorry i'm new with excel vba
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Because it divides at TextBox5, if it is empty, don't return value And if it is Zero it show Error, Divided by Zero.
 
Upvote 0
maabadi said:
Because it divides at TextBox5, if it is empty, don't return value And if it is Zero it show Error, Divided by Zero.
Click to expand...
Ohh i get what you mean. Before i insert the script as you suggested i faced and error as "type mismatch".
After i insert the line it work perfectly and do not contain error.

Can you help me on:
1) The data of summary user form insert to excel sheet i wanted it to be recorded on column 3 instead of column 2 ?
2) On the Packing excel sheet i still can't solve as value not show on production output and total rejection. But in user form it does show the value.

1608625486403.png


I attach my excel workbook below. Thank you .
 
Upvote 0
ellyna said:
Ohh i get what you mean. Before i insert the script as you suggested i faced and error as "type mismatch".
After i insert the line it work perfectly and do not contain error.

Can you help me on:
1) The data of summary user form insert to excel sheet i wanted it to be recorded on column 3 instead of column 2 ?
2) On the Packing excel sheet i still can't solve as value not show on production output and total rejection. But in user form it does show the value.

View attachment 28398

I attach my excel workbook below. Thank you .
Click to expand...
i forgot to attach my workbook. Here is it

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
 
Upvote 0
I see your code.
1. the simple way is you hide your column2, I try to do it, but I don't know why time input at column2 always it zero. But if you only want date not time and delete column3 change AddRecord_code to this:
VBA Code: 
Option Explicit
Sub AddRecord(ByVal Form As Object)
    Dim myWorksheet As Worksheet
    Dim NewRecord   As Range
    Dim ctrl        As Control
    Dim c           As Integer, PageNo As Integer, i As Integer
    Dim NextNo      As Long
    Dim SheetName   As String
    Dim Response    As VbMsgBoxResult
    Dim ActivePage  As Object
    
    On Error GoTo myerror
    
    With Form.MultiPage1
        'get worksheet name from page tab
        SheetName = .Pages(.Value).Caption
        'get page no
        PageNo = .Value
    End With
    'inform user
    Response = MsgBox("Do you want To save the data To " & SheetName & " ?", 36, "Confirmation")
    If Response = vbNo Then Exit Sub
    'set object variable to worksheet
    Set myWorksheet = ThisWorkbook.Worksheets(SheetName)
    'set object variable to Active Page
    Set ActivePage = Form.MultiPage1.Pages(PageNo)
    
    With myWorksheet
        'get next number
        NextNo = Val(Application.Max(.Columns(1)) + 1)
        'get next blank cell in range
        Set NewRecord = .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, 1)
    End With
    
    'add new record
    c = 1
    With NewRecord
        .Value = NextNo
        '.Offset(, 1).Value = Now()
        '.Offset(, 1).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
    End With
    
    'index each control on selected page
    For i = 1 To ActivePage.Controls.Count
        'loop through each textbox or combobox control on page
        For Each ctrl In ActivePage.Controls
            'to ensure each control posts data in correct order,
            'we check if control tabindex matches index value
            If ctrl.TabIndex = i - 1 Then
                If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
                    'post record to range
                    With NewRecord.Offset(, IIf(c > 1, c, c))
                        'check for date format & value
                        If ctrl.Value Like "##/##/####" And IsDate(ctrl.Value) Then
                            .Value = DateValue(ctrl.Value)
                        Else
                            .Value = ctrl.Value
                        End If
                    End With
                    'clear control
                    ctrl.Value = ""
                    c = c + 1
                End If
            End If
        Next ctrl
    Next i
    
    'inform user
    MsgBox "Record Added To Sheet: " & SheetName, 64, "Record Added"
    
myerror:
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub
 
Upvote 0
maabadi said:
I see your code.
1. the simple way is you hide your column2, I try to do it, but I don't know why time input at column2 always it zero. But if you only want date not time and delete column3 change AddRecord_code to this:
VBA Code: 
Option Explicit
Sub AddRecord(ByVal Form As Object)
    Dim myWorksheet As Worksheet
    Dim NewRecord   As Range
    Dim ctrl        As Control
    Dim c           As Integer, PageNo As Integer, i As Integer
    Dim NextNo      As Long
    Dim SheetName   As String
    Dim Response    As VbMsgBoxResult
    Dim ActivePage  As Object
   
    On Error GoTo myerror
   
    With Form.MultiPage1
        'get worksheet name from page tab
        SheetName = .Pages(.Value).Caption
        'get page no
        PageNo = .Value
    End With
    'inform user
    Response = MsgBox("Do you want To save the data To " & SheetName & " ?", 36, "Confirmation")
    If Response = vbNo Then Exit Sub
    'set object variable to worksheet
    Set myWorksheet = ThisWorkbook.Worksheets(SheetName)
    'set object variable to Active Page
    Set ActivePage = Form.MultiPage1.Pages(PageNo)
   
    With myWorksheet
        'get next number
        NextNo = Val(Application.Max(.Columns(1)) + 1)
        'get next blank cell in range
        Set NewRecord = .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, 1)
    End With
   
    'add new record
    c = 1
    With NewRecord
        .Value = NextNo
        '.Offset(, 1).Value = Now()
        '.Offset(, 1).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
    End With
   
    'index each control on selected page
    For i = 1 To ActivePage.Controls.Count
        'loop through each textbox or combobox control on page
        For Each ctrl In ActivePage.Controls
            'to ensure each control posts data in correct order,
            'we check if control tabindex matches index value
            If ctrl.TabIndex = i - 1 Then
                If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
                    'post record to range
                    With NewRecord.Offset(, IIf(c > 1, c, c))
                        'check for date format & value
                        If ctrl.Value Like "##/##/####" And IsDate(ctrl.Value) Then
                            .Value = DateValue(ctrl.Value)
                        Else
                            .Value = ctrl.Value
                        End If
                    End With
                    'clear control
                    ctrl.Value = ""
                    c = c + 1
                End If
            End If
        Next ctrl
    Next i
   
    'inform user
    MsgBox "Record Added To Sheet: " & SheetName, 64, "Record Added"
   
myerror:
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub
Click to expand...
My friend advice me to do it like this : (But i have no idea on how t do it)
To place data in correct order on worksheet you need to set the Tab Order of each control as required :VBA Userforms - Tab Order
 
Upvote 0
2.For ProductionOutput & Total Rejection at Packing tab you define at other textbox change event equal to ....
you should change it at all to textbox number at summary sheet with If condition Based Machine name.

And Are your Total Material(A) at Summary sheet equal to ProductionOutput at Packing sheet. if Not are input that manual.
 
Last edited:
Upvote 0
What is your problem with your tab order now? I don't know.
 
Upvote 0
For Example your Material in textbox code should change to this
You Also should change all of txtPOut.Value to your textbox name based machines at summary sheet:
VBA Code: 
Private Sub txtMIn_Change()
Select Case comboMachine.Value
    Case "Hot Stamp A"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox7.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Hot Stamp B"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox15.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Hot Stamp C"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox23.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Label"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox31.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Hot Air"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox39.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Capping D"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox47.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Capping D + Foil"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox55.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Cutting"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox63.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Ultrasonic"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox71.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
        
End Sub
 
Upvote 0
maabadi said:
For Example your Material in textbox code should change to this
You Also should change all of txtPOut.Value to your textbox name based machines at summary sheet:
VBA Code: 
Private Sub txtMIn_Change()
Select Case comboMachine.Value
    Case "Hot Stamp A"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox7.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Hot Stamp B"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox15.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Hot Stamp C"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox23.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Label"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox31.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Hot Air"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox39.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Capping D"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox47.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Capping D + Foil"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox55.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Cutting"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox63.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
    Case "Ultrasonic"
    txtPOut.Value = IIf(Val(txtMIn) > 0, Val(txtMIn.Value) - Val(txtReject.Value), 0)
    TextBox71.Value = IIf(Val(txtMIn) > 0, Val(txtStartUp.Value) + Val(txtReject.Value), 0)
       
End Sub
Click to expand...
As in the summary user form . I would prefer to key in manually. As I see in the record it is not according to machine as some is not key in into the summary.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Locked
  • Question
mismatch this code for add items to listbox in userfrom
Replies
1
Views
224
Fluff
Fluff
M
  • Question
mismatch this code for add items to listbox in userfrom
2 3 4
Replies
31
Views
1K
MohamedAmin
M
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
336
Agnarr
Agnarr
A
  • Question
possible calculation in listbox for debit , credit when search based on textbox ?
Replies
0
Views
281
abdelfattah
A
T
  • Solved
UserForm calculator error
Replies
5
Views
404
TessieBear99
T

Forum statistics

Threads
1,214,583
Messages
6,120,383
Members
448,956
Latest member
JPav

Share this page

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
Back
Top