VBA Code Works in Visual Basic Coder, but not with Command Button in Excel

rheintz · Jul 23, 2024

Hello, I am having an issue with my VBA code. It works in the coder when I launch it, but when I use the command button I placed on the first page of the Excel sheet, it doesn't transfer values from one form to the next, and the next form uses input from the first form to populate information. The code is very extensive and has a lot of subs, so I don't know where to begin. The code attached is from the second form. 1st form code can be sent if you need it.

Ahead of time I appreciate all the help.

VBA Code:

Option Explicit

Private Sub cmdCalculate_Click()
    Call Calculate4DimensionsInput
End Sub

Private Sub cmdExit_Click()
    If MsgBox("Do you want to Exit?", vbYesNo, "Reset Data") = vbYes Then
        Unload Me
    End If
End Sub

Private Sub cmdReset_Click()
    If MsgBox("Do you want to Reset thie data?:", vbYesNo, "Reset Data") = vbYes Then
        'Remove data from textboxes
        Call EmptyTextboxes4Dimensional
    End If
End Sub

Public Sub EmptyTextboxes4Dimensional()
    'Resets all input boxes back to blank when reset button is pressed.
        form4DimensionalInput.textboxC1S1D1.Value = ""
        form4DimensionalInput.textboxC1S2D1.Value = ""
        form4DimensionalInput.textboxC1S3D1.Value = ""
        form4DimensionalInput.textboxC1S4D1.Value = ""
        form4DimensionalInput.textboxC2S1D1.Value = ""
        form4DimensionalInput.textboxC2S2D1.Value = ""
        form4DimensionalInput.textboxC2S3D1.Value = ""
        form4DimensionalInput.textboxC2S4D1.Value = ""
        form4DimensionalInput.textboxC3S1D1.Value = ""
        form4DimensionalInput.textboxC3S2D1.Value = ""
        form4DimensionalInput.textboxC3S3D1.Value = ""
        form4DimensionalInput.textboxC3S4D1.Value = ""
        form4DimensionalInput.textboxC4S1D1.Value = ""
        form4DimensionalInput.textboxC4S2D1.Value = ""
        form4DimensionalInput.textboxC4S3D1.Value = ""
        form4DimensionalInput.textboxC4S4D1.Value = ""
        form4DimensionalInput.textboxC1S1D2.Value = ""
        form4DimensionalInput.textboxC1S2D2.Value = ""
        form4DimensionalInput.textboxC1S3D2.Value = ""
        form4DimensionalInput.textboxC1S4D2.Value = ""
        form4DimensionalInput.textboxC2S1D2.Value = ""
        form4DimensionalInput.textboxC2S2D2.Value = ""
        form4DimensionalInput.textboxC2S3D2.Value = ""
        form4DimensionalInput.textboxC2S4D2.Value = ""
        form4DimensionalInput.textboxC3S1D2.Value = ""
        form4DimensionalInput.textboxC3S2D2.Value = ""
        form4DimensionalInput.textboxC3S3D2.Value = ""
        form4DimensionalInput.textboxC3S4D2.Value = ""
        form4DimensionalInput.textboxC4S1D2.Value = ""
        form4DimensionalInput.textboxC4S2D2.Value = ""
        form4DimensionalInput.textboxC4S3D2.Value = ""
        form4DimensionalInput.textboxC4S4D2.Value = ""
        form4DimensionalInput.textboxC1S1D3.Value = ""
        form4DimensionalInput.textboxC1S2D3.Value = ""
        form4DimensionalInput.textboxC1S3D3.Value = ""
        form4DimensionalInput.textboxC1S4D3.Value = ""
        form4DimensionalInput.textboxC2S1D3.Value = ""
        form4DimensionalInput.textboxC2S2D3.Value = ""
        form4DimensionalInput.textboxC2S3D3.Value = ""
        form4DimensionalInput.textboxC2S4D3.Value = ""
        form4DimensionalInput.textboxC3S1D3.Value = ""
        form4DimensionalInput.textboxC3S2D3.Value = ""
        form4DimensionalInput.textboxC3S3D3.Value = ""
        form4DimensionalInput.textboxC3S4D3.Value = ""
        form4DimensionalInput.textboxC4S1D3.Value = ""
        form4DimensionalInput.textboxC4S2D3.Value = ""
        form4DimensionalInput.textboxC4S3D3.Value = ""
        form4DimensionalInput.textboxC4S4D3.Value = ""
        form4DimensionalInput.textboxC1S1D4.Value = ""
        form4DimensionalInput.textboxC1S2D4.Value = ""
        form4DimensionalInput.textboxC1S3D4.Value = ""
        form4DimensionalInput.textboxC1S4D4.Value = ""
        form4DimensionalInput.textboxC2S1D4.Value = ""
        form4DimensionalInput.textboxC2S2D4.Value = ""
        form4DimensionalInput.textboxC2S3D4.Value = ""
        form4DimensionalInput.textboxC2S4D4.Value = ""
        form4DimensionalInput.textboxC3S1D4.Value = ""
        form4DimensionalInput.textboxC3S2D4.Value = ""
        form4DimensionalInput.textboxC3S3D4.Value = ""
        form4DimensionalInput.textboxC3S4D4.Value = ""
        form4DimensionalInput.textboxC4S1D4.Value = ""
        form4DimensionalInput.textboxC4S2D4.Value = ""
        form4DimensionalInput.textboxC4S3D4.Value = ""
        form4DimensionalInput.textboxC4S4D4.Value = ""
        form4DimensionalInput.textboxC1S1D5.Value = ""
        form4DimensionalInput.textboxC1S2D5.Value = ""
        form4DimensionalInput.textboxC1S3D5.Value = ""
        form4DimensionalInput.textboxC1S4D5.Value = ""
        form4DimensionalInput.textboxC2S1D5.Value = ""
        form4DimensionalInput.textboxC2S2D5.Value = ""
        form4DimensionalInput.textboxC2S3D5.Value = ""
        form4DimensionalInput.textboxC2S4D5.Value = ""
        form4DimensionalInput.textboxC3S1D5.Value = ""
        form4DimensionalInput.textboxC3S2D5.Value = ""
        form4DimensionalInput.textboxC3S3D5.Value = ""
        form4DimensionalInput.textboxC3S4D5.Value = ""
        form4DimensionalInput.textboxC4S1D5.Value = ""
        form4DimensionalInput.textboxC4S2D5.Value = ""
        form4DimensionalInput.textboxC4S3D5.Value = ""
        form4DimensionalInput.textboxC4S4D5.Value = ""
End Sub

Private Sub ComboBoxPartNumber_Change()
    Call InitializeControlsOffsets
End Sub

Private Sub UserForm_Initialize()
    Call InitializeControlsOffsets
End Sub

Private Sub InitializeControlsOffsets()
    'Fills the Dim Textboxs on the form based off the part number selected on the other form.
    'If the part only has 4 dimensions is blues out the Dim 5 boxes to make them not an input.
    
    Dim Count As Long
    Dim iRow As Long
          
    With shDimValues
        iRow = shDimValues.Cells(shDimValues.Rows.Count, 1).End(xlUp).Row + 1
        
        Me.TextBoxPartNum.Value = formOffsetCalculator.ComboBoxPartNumber.Value
        Me.TextBoxNumberofParts.Value = formOffsetCalculator.TextBoxNumberofParts.Value
        
        For Count = 2 To iRow
            If formOffsetCalculator.ComboBoxPartNumber.Value = shDimValues.Cells(Count, 1).Value Then
                Me.textboxDim1.Value = shDimValues.Cells(Count, 7).Value
                Me.textboxDim2.Value = shDimValues.Cells(Count, 8).Value
                Me.textboxDim3.Value = shDimValues.Cells(Count, 9).Value
                Me.textboxDim4.Value = shDimValues.Cells(Count, 10).Value
                
                If shDimValues.Cells(Count, 11).Value = "" Or shDimValues.Cells(Count, 11).Value = 0 Then
                    Me.textboxDim5.BackColor = vbBlue
                    Me.textboxC1S1D5.BackColor = vbBlue
                    Me.textboxC1S2D5.BackColor = vbBlue
                    Me.textboxC1S3D5.BackColor = vbBlue
                    Me.textboxC1S4D5.BackColor = vbBlue
                    Me.textboxC2S1D5.BackColor = vbBlue
                    Me.textboxC2S2D5.BackColor = vbBlue
                    Me.textboxC2S3D5.BackColor = vbBlue
                    Me.textboxC2S4D5.BackColor = vbBlue
                    Me.textboxC3S1D5.BackColor = vbBlue
                    Me.textboxC3S2D5.BackColor = vbBlue
                    Me.textboxC3S3D5.BackColor = vbBlue
                    Me.textboxC3S4D5.BackColor = vbBlue
                    Me.textboxC4S1D5.BackColor = vbBlue
                    Me.textboxC4S2D5.BackColor = vbBlue
                    Me.textboxC4S3D5.BackColor = vbBlue
                    Me.textboxC4S4D5.BackColor = vbBlue
                    
                    Me.textboxDim5.Value = 0
                    Me.textboxC1S1D5.Value = 0
                    Me.textboxC1S2D5.Value = 0
                    Me.textboxC1S3D5.Value = 0
                    Me.textboxC1S4D5.Value = 0
                    Me.textboxC2S1D5.Value = 0
                    Me.textboxC2S2D5.Value = 0
                    Me.textboxC2S3D5.Value = 0
                    Me.textboxC2S4D5.Value = 0
                    Me.textboxC3S1D5.Value = 0
                    Me.textboxC3S2D5.Value = 0
                    Me.textboxC3S3D5.Value = 0
                    Me.textboxC3S4D5.Value = 0
                    Me.textboxC4S1D5.Value = 0
                    Me.textboxC4S2D5.Value = 0
                    Me.textboxC4S3D5.Value = 0
                    Me.textboxC4S4D5.Value = 0
                Else
                    Me.textboxDim5.Value = shDimValues.Cells(Count, 11).Value
                End If
    
            End If
        Next
    End With
End Sub

Private Sub Calculate4DimensionsInput()
    Dim AvgCh1Dim1 As Double, AvgCh1Dim2 As Double, AvgCh1Dim3 As Double, AvgCh1Dim4 As Double, AvgCh1Dim5 As Double
    Dim AvgCh2Dim1 As Double, AvgCh2Dim2 As Double, AvgCh2Dim3 As Double, AvgCh2Dim4 As Double, AvgCh2Dim5 As Double
    Dim AvgCh3Dim1 As Double, AvgCh3Dim2 As Double, AvgCh3Dim3 As Double, AvgCh3Dim4 As Double, AvgCh3Dim5 As Double
    Dim AvgCh4Dim1 As Double, AvgCh4Dim2 As Double, AvgCh4Dim3 As Double, AvgCh4Dim4 As Double, AvgCh4Dim5 As Double
    Dim SampleSize As Long, Dim1Val As Double, Dim2Val As Double, Dim3Val As Double, Dim4Val As Double, Dim5Val As Double
    
    Dim Ch1Sam1Dim1 As Double, Ch1Sam2Dim1 As Double, Ch1Sam3Dim1 As Double, Ch1Sam4Dim1 As Double
    Dim Ch1Sam1Dim2 As Double, Ch1Sam2Dim2 As Double, Ch1Sam3Dim2 As Double, Ch1Sam4Dim2 As Double
    Dim Ch1Sam1Dim3 As Double, Ch1Sam2Dim3 As Double, Ch1Sam3Dim3 As Double, Ch1Sam4Dim3 As Double
    Dim Ch1Sam1Dim4 As Double, Ch1Sam2Dim4 As Double, Ch1Sam3Dim4 As Double, Ch1Sam4Dim4 As Double
    Dim Ch1Sam1Dim5 As Double, Ch1Sam2Dim5 As Double, Ch1Sam3Dim5 As Double, Ch1Sam4Dim5 As Double
    
    Dim Ch2Sam1Dim1 As Double, Ch2Sam2Dim1 As Double, Ch2Sam3Dim1 As Double, Ch2Sam4Dim1 As Double
    Dim Ch2Sam1Dim2 As Double, Ch2Sam2Dim2 As Double, Ch2Sam3Dim2 As Double, Ch2Sam4Dim2 As Double
    Dim Ch2Sam1Dim3 As Double, Ch2Sam2Dim3 As Double, Ch2Sam3Dim3 As Double, Ch2Sam4Dim3 As Double
    Dim Ch2Sam1Dim4 As Double, Ch2Sam2Dim4 As Double, Ch2Sam3Dim4 As Double, Ch2Sam4Dim4 As Double
    Dim Ch2Sam1Dim5 As Double, Ch2Sam2Dim5 As Double, Ch2Sam3Dim5 As Double, Ch2Sam4Dim5 As Double
    
    Dim Ch3Sam1Dim1 As Double, Ch3Sam2Dim1 As Double, Ch3Sam3Dim1 As Double, Ch3Sam4Dim1 As Double
    Dim Ch3Sam1Dim2 As Double, Ch3Sam2Dim2 As Double, Ch3Sam3Dim2 As Double, Ch3Sam4Dim2 As Double
    Dim Ch3Sam1Dim3 As Double, Ch3Sam2Dim3 As Double, Ch3Sam3Dim3 As Double, Ch3Sam4Dim3 As Double
    Dim Ch3Sam1Dim4 As Double, Ch3Sam2Dim4 As Double, Ch3Sam3Dim4 As Double, Ch3Sam4Dim4 As Double
    Dim Ch3Sam1Dim5 As Double, Ch3Sam2Dim5 As Double, Ch3Sam3Dim5 As Double, Ch3Sam4Dim5 As Double
    
    Dim Ch4Sam1Dim1 As Double, Ch4Sam2Dim1 As Double, Ch4Sam3Dim1 As Double, Ch4Sam4Dim1 As Double
    Dim Ch4Sam1Dim2 As Double, Ch4Sam2Dim2 As Double, Ch4Sam3Dim2 As Double, Ch4Sam4Dim2 As Double
    Dim Ch4Sam1Dim3 As Double, Ch4Sam2Dim3 As Double, Ch4Sam3Dim3 As Double, Ch4Sam4Dim3 As Double
    Dim Ch4Sam1Dim4 As Double, Ch4Sam2Dim4 As Double, Ch4Sam3Dim4 As Double, Ch4Sam4Dim4 As Double
    Dim Ch4Sam1Dim5 As Double, Ch4Sam2Dim5 As Double, Ch4Sam3Dim5 As Double, Ch4Sam4Dim5 As Double
    
    
    'Setting the sample size from the form
    SampleSize = Me.TextBoxNumberofParts.Value
    
    'Chuck 1 Samples Designated to Variables and Calculates Average of Dimensions
    Ch1Sam1Dim1 = Me.textboxC1S1D1.Value
    Ch1Sam2Dim1 = Me.textboxC1S2D1.Value
    Ch1Sam3Dim1 = Me.textboxC1S3D1.Value
    Ch1Sam4Dim1 = Me.textboxC1S4D1.Value
    AvgCh1Dim1 = (Ch1Sam1Dim1 + Ch1Sam2Dim1 + Ch1Sam3Dim1 + Ch1Sam4Dim1) / SampleSize
    
    Ch1Sam1Dim2 = Me.textboxC1S1D2.Value
    Ch1Sam2Dim2 = Me.textboxC1S2D2.Value
    Ch1Sam3Dim2 = Me.textboxC1S3D2.Value
    Ch1Sam4Dim2 = Me.textboxC1S4D2.Value
    AvgCh1Dim2 = (Ch1Sam1Dim2 + Ch1Sam2Dim2 + Ch1Sam3Dim2 + Ch1Sam4Dim2) / SampleSize
    
    Ch1Sam1Dim3 = Me.textboxC1S1D3.Value
    Ch1Sam2Dim3 = Me.textboxC1S2D3.Value
    Ch1Sam3Dim3 = Me.textboxC1S3D3.Value
    Ch1Sam4Dim3 = Me.textboxC1S4D3.Value
    AvgCh1Dim3 = (Ch1Sam1Dim3 + Ch1Sam2Dim3 + Ch1Sam3Dim3 + Ch1Sam4Dim3) / SampleSize
    
    Ch1Sam1Dim4 = Me.textboxC1S1D4.Value
    Ch1Sam2Dim4 = Me.textboxC1S2D4.Value
    Ch1Sam3Dim4 = Me.textboxC1S3D4.Value
    Ch1Sam4Dim4 = Me.textboxC1S4D4.Value
    AvgCh1Dim4 = (Ch1Sam1Dim4 + Ch1Sam2Dim4 + Ch1Sam3Dim4 + Ch1Sam4Dim4) / SampleSize
    
    Ch1Sam1Dim5 = Me.textboxC1S1D5.Value
    Ch1Sam2Dim5 = Me.textboxC1S2D5.Value
    Ch1Sam3Dim5 = Me.textboxC1S3D5.Value
    Ch1Sam4Dim5 = Me.textboxC1S4D5.Value
    AvgCh1Dim5 = (Ch1Sam1Dim5 + Ch1Sam2Dim5 + Ch1Sam3Dim5 + Ch1Sam4Dim5) / SampleSize
    
    'Chuck 2 Samples Designated to Variables and Calculates Average of Dimensions
    Ch2Sam1Dim1 = Me.textboxC2S1D1.Value
    Ch2Sam2Dim1 = Me.textboxC2S2D1.Value
    Ch2Sam3Dim1 = Me.textboxC2S3D1.Value
    Ch2Sam4Dim1 = Me.textboxC2S4D1.Value
    AvgCh2Dim1 = (Ch2Sam1Dim1 + Ch2Sam2Dim1 + Ch2Sam3Dim1 + Ch2Sam4Dim1) / SampleSize
    
    Ch2Sam1Dim2 = Me.textboxC2S1D2.Value
    Ch2Sam2Dim2 = Me.textboxC2S2D2.Value
    Ch2Sam3Dim2 = Me.textboxC2S3D2.Value
    Ch2Sam4Dim2 = Me.textboxC2S4D2.Value
    AvgCh2Dim2 = (Ch2Sam1Dim2 + Ch2Sam2Dim2 + Ch2Sam3Dim2 + Ch2Sam4Dim2) / SampleSize
    
    Ch2Sam1Dim3 = Me.textboxC2S1D3.Value
    Ch2Sam2Dim3 = Me.textboxC2S2D3.Value
    Ch2Sam3Dim3 = Me.textboxC2S3D3.Value
    Ch2Sam4Dim3 = Me.textboxC2S4D3.Value
    AvgCh2Dim3 = (Ch2Sam1Dim3 + Ch2Sam2Dim3 + Ch2Sam3Dim3 + Ch2Sam4Dim3) / SampleSize

    Ch2Sam1Dim4 = Me.textboxC2S1D4.Value
    Ch2Sam2Dim4 = Me.textboxC2S2D4.Value
    Ch2Sam3Dim4 = Me.textboxC2S3D4.Value
    Ch2Sam4Dim4 = Me.textboxC2S4D4.Value
    AvgCh2Dim4 = (Ch2Sam1Dim4 + Ch2Sam2Dim4 + Ch2Sam3Dim4 + Ch2Sam4Dim4) / SampleSize
    
    Ch2Sam1Dim5 = Me.textboxC2S1D5.Value
    Ch2Sam2Dim5 = Me.textboxC2S2D5.Value
    Ch2Sam3Dim5 = Me.textboxC2S3D5.Value
    Ch2Sam4Dim5 = Me.textboxC2S4D5.Value
    AvgCh2Dim5 = (Ch2Sam1Dim5 + Ch2Sam2Dim5 + Ch2Sam3Dim5 + Ch2Sam4Dim5) / SampleSize
    
    'Chuck 3 Samples Designated to Variables and Calculates Average of Dimensions
    Ch3Sam1Dim1 = Me.textboxC3S1D1.Value
    Ch3Sam2Dim1 = Me.textboxC3S2D1.Value
    Ch3Sam3Dim1 = Me.textboxC3S3D1.Value
    Ch3Sam4Dim1 = Me.textboxC3S4D1.Value
    AvgCh3Dim1 = (Ch3Sam1Dim1 + Ch3Sam2Dim1 + Ch3Sam3Dim1 + Ch3Sam4Dim1) / SampleSize

    Ch3Sam1Dim2 = Me.textboxC3S1D2.Value
    Ch3Sam2Dim2 = Me.textboxC3S2D2.Value
    Ch3Sam3Dim2 = Me.textboxC3S3D2.Value
    Ch3Sam4Dim2 = Me.textboxC3S4D2.Value
    AvgCh3Dim2 = (Ch3Sam1Dim2 + Ch3Sam2Dim2 + Ch3Sam3Dim2 + Ch3Sam4Dim2) / SampleSize

    Ch3Sam1Dim3 = Me.textboxC3S1D3.Value
    Ch3Sam2Dim3 = Me.textboxC3S2D3.Value
    Ch3Sam3Dim3 = Me.textboxC3S3D3.Value
    Ch3Sam4Dim3 = Me.textboxC3S4D3.Value
    AvgCh3Dim3 = (Ch3Sam1Dim3 + Ch3Sam2Dim3 + Ch3Sam3Dim3 + Ch3Sam4Dim3) / SampleSize

    Ch3Sam1Dim4 = Me.textboxC3S1D4.Value
    Ch3Sam2Dim4 = Me.textboxC3S2D4.Value
    Ch3Sam3Dim4 = Me.textboxC3S3D4.Value
    Ch3Sam4Dim4 = Me.textboxC3S4D4.Value
    AvgCh3Dim4 = (Ch3Sam1Dim4 + Ch3Sam2Dim4 + Ch3Sam3Dim4 + Ch3Sam4Dim4) / SampleSize
    
    Ch3Sam1Dim5 = Me.textboxC3S1D5.Value
    Ch3Sam2Dim5 = Me.textboxC3S2D5.Value
    Ch3Sam3Dim5 = Me.textboxC3S3D5.Value
    Ch3Sam4Dim5 = Me.textboxC3S4D5.Value
    AvgCh3Dim5 = (Ch3Sam1Dim5 + Ch3Sam2Dim5 + Ch3Sam3Dim5 + Ch3Sam4Dim5) / SampleSize
    
    'Chuck 4 Samples Designated to Variables and Calculates Average of Dimensions
    Ch4Sam1Dim1 = Me.textboxC4S1D1.Value
    Ch4Sam2Dim1 = Me.textboxC4S2D1.Value
    Ch4Sam3Dim1 = Me.textboxC4S3D1.Value
    Ch4Sam4Dim1 = Me.textboxC4S4D1.Value
    AvgCh4Dim1 = (Ch4Sam1Dim1 + Ch4Sam2Dim1 + Ch4Sam3Dim1 + Ch4Sam4Dim1) / SampleSize

    Ch4Sam1Dim2 = Me.textboxC4S1D2.Value
    Ch4Sam2Dim2 = Me.textboxC4S2D2.Value
    Ch4Sam3Dim2 = Me.textboxC4S3D2.Value
    Ch4Sam4Dim2 = Me.textboxC4S4D2.Value
    AvgCh4Dim2 = (Ch4Sam1Dim2 + Ch4Sam2Dim2 + Ch4Sam3Dim2 + Ch4Sam4Dim2) / SampleSize

    Ch4Sam1Dim3 = Me.textboxC4S1D3.Value
    Ch4Sam2Dim3 = Me.textboxC4S2D3.Value
    Ch4Sam3Dim3 = Me.textboxC4S3D3.Value
    Ch4Sam4Dim3 = Me.textboxC4S4D3.Value
    AvgCh4Dim3 = (Ch4Sam1Dim3 + Ch4Sam2Dim3 + Ch4Sam3Dim3 + Ch4Sam4Dim3) / SampleSize

    Ch4Sam1Dim4 = Me.textboxC4S1D4.Value
    Ch4Sam2Dim4 = Me.textboxC4S2D4.Value
    Ch4Sam3Dim4 = Me.textboxC4S3D4.Value
    Ch4Sam4Dim4 = Me.textboxC4S4D4.Value
    AvgCh4Dim4 = (Ch4Sam1Dim4 + Ch4Sam2Dim4 + Ch4Sam3Dim4 + Ch4Sam4Dim4) / SampleSize
    
    Ch4Sam1Dim5 = Me.textboxC4S1D5.Value
    Ch4Sam2Dim5 = Me.textboxC4S2D5.Value
    Ch4Sam3Dim5 = Me.textboxC4S3D5.Value
    Ch4Sam4Dim5 = Me.textboxC4S4D5.Value
    AvgCh4Dim5 = (Ch4Sam1Dim5 + Ch4Sam2Dim5 + Ch4Sam3Dim5 + Ch4Sam4Dim5) / SampleSize
    
    'Sets the values to calculate towards based off the part number selected on the previous form
    With shDimValues
        Dim iRow As Integer
        Dim Count As Integer
        
        iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            
        For Count = 2 To iRow
            If formOffsetCalculator.ComboBoxPartNumber.Value = .Cells(Count, 1).Value Then
                Dim1Val = .Cells(Count, 2).Value
                Dim2Val = .Cells(Count, 3).Value
                Dim3Val = .Cells(Count, 4).Value
                Dim4Val = .Cells(Count, 5).Value
                Dim5Val = .Cells(Count, 6).Value
            End If
        Next
    End With
    
    
    'Calculate Offsets if its a C0200 Family
    If Me.TextBoxPartNum.Value.Value = "C0200-01" Or _
     Me.TextBoxPartNum.Value.Value = "C0200-02" Or _
     Me.TextBoxPartNum.Value.Value = "C0200-03, C0200-06, C18719-03" Or _
     Me.TextBoxPartNum.Value.Value = "C0200-04" Or _
     Me.TextBoxPartNum.Value.Value = "C20902-01, C22422-03" Then
        'Calculate Chuck 1's offsets
        Dim CalcDim1Ch1 As Double, CalcDim2Ch1 As Double, CalcDim3Ch1 As Double, CalcDim4Ch1 As Double, CalcDim5Ch1 As Double
        Dim Ch1XOffset As String, Ch1YOffset As String, Ch1ZOffset As String
        Dim RghtToCenterCh1 As Double
                
        'formatting the variable
        CalcDim1Ch1 = Round(Abs(Dim1Val - AvgCh1Dim1), 6)
        CalcDim2Ch1 = Round(Abs(Dim2Val - AvgCh1Dim2), 6)
        RghtToCenterCh1 = AvgCh1Dim3 - AvgCh1Dim4
        CalcDim4Ch1 = Round(Abs(AvgCh1Dim4 - RghtToCenterCh1), 6)
              
        If AvgCh1Dim1 < Dim1Val Then
            Ch1ZOffset = "+" & CalcDim1Ch1
        Else
            Ch1ZOffset = "-" & CalcDim1Ch1
        End If
        
        If AvgCh1Dim2 > Dim2Val Then
            Ch1XOffset = "+" & CalcDim2Ch1
        Else
            Ch1XOffset = "-" & CalcDim2Ch1
        End If
        
        If AvgCh1Dim4 < RghtToCenterCh1 Then
            Ch1YOffset = "+" & CalcDim4Ch1
        Else
            Ch1YOffset = "-" & CalcDim4Ch1
        End If
        
        'Calculate Chuck 2's offsets
        Dim CalcDim1Ch2 As Double, CalcDim2Ch2 As Double, CalcDim3Ch2 As Double, CalcDim4Ch2 As Double, CalcDim5Ch2 As Double
        Dim Ch2XOffset As String, Ch2YOffset As String, Ch2ZOffset As String
        Dim RghtToCenterCh2 As Double
                      
        CalcDim1Ch2 = Round(Abs(Dim1Val - AvgCh2Dim1), 6)
        CalcDim2Ch2 = Round(Abs(Dim2Val - AvgCh2Dim2), 6)
        RghtToCenterCh2 = AvgCh2Dim3 - AvgCh2Dim4
        CalcDim4Ch2 = Round(Abs(AvgCh2Dim4 - RghtToCenterCh2), 6)
              
        If AvgCh2Dim1 < Dim1Val Then
            Ch2ZOffset = "+" & CalcDim1Ch2
        Else
            Ch2ZOffset = "-" & CalcDim1Ch2
        End If
        
        If AvgCh2Dim2 > Dim2Val Then
            Ch2XOffset = "-" & CalcDim2Ch2
        Else
            Ch2XOffset = "+" & CalcDim2Ch2
        End If
        
        If AvgCh2Dim4 < RghtToCenterCh2 Then
            Ch2YOffset = "-" & CalcDim4Ch2
        Else
            Ch2YOffset = "+" & CalcDim4Ch2
        End If
        
        'Calculate Chuck 3's offsets
        Dim CalcDim1Ch3 As Double, CalcDim2Ch3 As Double, CalcDim3Ch3 As Double, CalcDim4Ch3 As Double, CalcDim5Ch3 As Double
        Dim Ch3XOffset As String, Ch3YOffset As String, Ch3ZOffset As String
        Dim RghtToCenterCh3 As Double
                      
        CalcDim1Ch3 = Round(Abs(Dim1Val - AvgCh3Dim1), 6)
        CalcDim2Ch3 = Round(Abs(Dim2Val - AvgCh3Dim2), 6)
        RghtToCenterCh3 = AvgCh1Dim3 - AvgCh3Dim4
        CalcDim4Ch3 = Round(Abs(AvgCh3Dim4 - RghtToCenterCh3), 6)
              
        If AvgCh3Dim1 < Dim1Val Then
            Ch3ZOffset = "+" & CalcDim1Ch3
        Else
            Ch3ZOffset = "-" & CalcDim1Ch3
        End If
        
        If AvgCh3Dim2 > Dim2Val Then
            Ch3XOffset = "+" & CalcDim2Ch3
        Else
            Ch3XOffset = "-" & CalcDim2Ch3
        End If
        
        If AvgCh3Dim4 < RghtToCenterCh3 Then
            Ch3YOffset = "+" & CalcDim4Ch3
        Else
            Ch3YOffset = "-" & CalcDim4Ch3
        End If
        
        'Calculate Chuck 4's offsets
        Dim CalcDim1Ch4 As Double, CalcDim2Ch4 As Double, CalcDim3Ch4 As Double, CalcDim4Ch4 As Double, CalcDim5Ch4 As Double
        Dim Ch4XOffset As String, Ch4YOffset As String, Ch4ZOffset As String
        Dim RghtToCenterCh4 As Double
                      
        CalcDim1Ch4 = Round(Abs(Dim1Val - AvgCh4Dim1), 6)
        CalcDim2Ch4 = Round(Abs(Dim2Val - AvgCh4Dim2), 6)
        RghtToCenterCh4 = AvgCh4Dim3 - AvgCh4Dim4
        CalcDim4Ch4 = Round(Abs(AvgCh4Dim4 - RghtToCenterCh4), 6)
              
        If AvgCh4Dim1 < Dim1Val Then
            Ch4ZOffset = "+" & CalcDim1Ch4
        Else
            Ch4ZOffset = "-" & CalcDim1Ch4
        End If
        
        If AvgCh4Dim2 > Dim2Val Then
            Ch4XOffset = "-" & CalcDim2Ch4
        Else
            Ch4XOffset = "+" & CalcDim2Ch4
        End If
        
        If AvgCh4Dim4 < RghtToCenterCh4 Then
            Ch4YOffset = "-" & CalcDim4Ch4
        Else
            Ch4YOffset = "+" & CalcDim4Ch4
        End If
        
        MsgBox ("Chuck 1 Offsets" & vbCrLf & _
                "X = " & Ch1XOffset & vbCrLf & _
                "Y = " & Ch1YOffset & vbCrLf & _
                "Z = " & Ch1ZOffset & vbCrLf & _
                "Chuck 2 Offsets" & vbCrLf & _
                "X = " & Ch2XOffset & vbCrLf & _
                "Y = " & Ch2YOffset & vbCrLf & _
                "Z = " & Ch2ZOffset & vbCrLf & _
                "Chuck 3 Offsets" & vbCrLf & _
                "X = " & Ch3XOffset & vbCrLf & _
                "Y = " & Ch3YOffset & vbCrLf & _
                "Z = " & Ch3ZOffset & vbCrLf & _
                "Chuck 4 Offsets" & vbCrLf & _
                "X = " & Ch4XOffset & vbCrLf & _
                "Y = " & Ch4YOffset & vbCrLf & _
                "Z = " & Ch4ZOffset), vbOKOnly + vbInformation, "Parts Page Adjustment"
    End If
    
    
    'Calculate Offsets if its a C0745/C20674 Family
    If Me.TextBoxPartNum.Value.Value = "C0745-07, C20945-02" Or _
     Me.TextBoxPartNum.Value.Value = "C0745-08" Or _
     Me.TextBoxPartNum.Value.Value = "C0745-09, C0745-11, C18700-04" Or _
     Me.TextBoxPartNum.Value.Value = "C0745-10" Or _
     Me.TextBoxPartNum.Value.Value = "C20674-01, C18700-05" Then
        
        'Calculate Chuck 1's offsets
        Dim CalcDim1Ch1 As Double, CalcDim2Ch1 As Double, CalcDim3Ch1 As Double, CalcDim4Ch1 As Double, CalcDim5Ch1 As Double
        Dim Ch1XOffset As String, Ch1YOffset As String, Ch1ZOffset As String
        Dim RghtToCenterCh1 As Double
                
        
        'Calculating the offset to even the side of the LANs
        CalcDim4Ch1 = Round(Abs((AvgCh1Dim4 - AvgCh1Dim5) / 2), 6)
        
        'Calculating the offset to even the tip of the LAN with the new "theoretical" even side of LANs
        'Also, Sets the chuck 1 Y offset Value
        If AvgCh1Dim4 < AvgCh1Dim5 Then
            CalcDim3Ch1 = Round(Abs((AvgCh1Dim4 + CalcDim4Ch1) - AvgCh1Dim3), 6)
            CalcDim5Ch1 = Round(Abs((AvgCh1Dim4 + CalcDim4Ch1) - Dim5Val), 6)
            Ch1YOffset = "-" & CalcDim4Ch1
        Else
            CalcDim3Ch1 = Round(Abs((AvgCh1Dim4 - CalcDim4Ch1) - AvgCh1Dim3), 6)
            CalcDim5Ch1 = Round(Abs((AvgCh1Dim4 - CalcDim4Ch1) - Dim5Val), 6)
            Ch1YOffset = "+" & CalcDim4Ch1
        End If
        
        
        CalcDim2Ch1 = Round(Abs(Dim2Val - AvgCh1Dim2), 6)
        'Sets the chuck 1 X Offset Value
        If AvgCh1Dim3 < Dim3Val And AvgCh1Dim2 < Dim2Val Then
            Ch1XOffset = "+" & CalcDim3Ch1
        Else
            If AvgCh1Dim3 < Dim3Val And AvgCh1Dim2 > Dim2Val Then
                MsgBox "Talk to Randy about the adjustment to make in X before continueing"
            Else
                Ch1XOffset = "-" & CalcDim3Ch1
            End If
        End If
        
        'Sets the Chuck 1 Z offset Value and looks at if the height wont effect it
        If AvgCh1Dim5 < Dim5Val And AvgCh1Dim1 < Dim1Val Then
            Ch1ZOffset = "+" & CalcDim5Ch1
        Else
            If AvgCh1Dim5 < Dim5Val And AvgCh1Dim1 > Dim1Val Then
                CalcDim1Ch1 = Round(Abs(((Dim1Val + 0.005) - AvgCh1Dim1) / 2), 6)
                Ch1ZOffset = "+" & CalcDim1Ch1
            Else
                If AvgCh1Dim5 < Dim5Val And AvgCh1Dim1 > Dim1Val And AvgCh1Dim2 > Dim2Val Then
                    MsgBox "Talk to Randy about the adjustment to make in X before continueing"
                Else
                    Ch1ZOffset = "-" & CalcDim5Ch1
                End If
            End If
        End If
        
        
        
        
        
        'Calculate Chuck 2's offsets
        Dim CalcDim1Ch2 As Double, CalcDim2Ch2 As Double, CalcDim3Ch2 As Double, CalcDim4Ch2 As Double, CalcDim5Ch2 As Double
        Dim Ch2XOffset As String, Ch2YOffset As String, Ch2ZOffset As String
        Dim RghtToCenterCh2 As Double
                      
        CalcDim1Ch2 = Round(Abs(Dim1Val - AvgCh2Dim1), 6)
        CalcDim2Ch2 = Round(Abs(Dim2Val - AvgCh2Dim2), 6)
        RghtToCenterCh2 = AvgCh2Dim3 - AvgCh2Dim4
        CalcDim4Ch2 = Round(Abs(AvgCh2Dim4 - RghtToCenterCh2), 6)
        
        
        'Calculate Chuck 3's offsets
        Dim CalcDim1Ch3 As Double, CalcDim2Ch3 As Double, CalcDim3Ch3 As Double, CalcDim4Ch3 As Double, CalcDim5Ch3 As Double
        Dim Ch3XOffset As String, Ch3YOffset As String, Ch3ZOffset As String
        Dim RghtToCenterCh3 As Double
                      
        CalcDim1Ch3 = Round(Abs(Dim1Val - AvgCh3Dim1), 6)
        CalcDim2Ch3 = Round(Abs(Dim2Val - AvgCh3Dim2), 6)
        RghtToCenterCh3 = AvgCh1Dim3 - AvgCh3Dim4
        CalcDim4Ch3 = Round(Abs(AvgCh3Dim4 - RghtToCenterCh3), 6)
        
        
        'Calculate Chuck 4's offsets
        Dim CalcDim1Ch4 As Double, CalcDim2Ch4 As Double, CalcDim3Ch4 As Double, CalcDim4Ch4 As Double, CalcDim5Ch4 As Double
        Dim Ch4XOffset As String, Ch4YOffset As String, Ch4ZOffset As String
        Dim RghtToCenterCh4 As Double
                      
        CalcDim1Ch4 = Round(Abs(Dim1Val - AvgCh4Dim1), 6)
        CalcDim2Ch4 = Round(Abs(Dim2Val - AvgCh4Dim2), 6)
        RghtToCenterCh4 = AvgCh4Dim3 - AvgCh4Dim4
        CalcDim4Ch4 = Round(Abs(AvgCh4Dim4 - RghtToCenterCh4), 6)
              

        
        MsgBox ("Chuck 1 Offsets" & vbCrLf & _
                "X = " & Ch1XOffset & vbCrLf & _
                "Y = " & Ch1YOffset & vbCrLf & _
                "Z = " & Ch1ZOffset & vbCrLf & _
                "Chuck 2 Offsets" & vbCrLf & _
                "X = " & Ch2XOffset & vbCrLf & _
                "Y = " & Ch2YOffset & vbCrLf & _
                "Z = " & Ch2ZOffset & vbCrLf & _
                "Chuck 3 Offsets" & vbCrLf & _
                "X = " & Ch3XOffset & vbCrLf & _
                "Y = " & Ch3YOffset & vbCrLf & _
                "Z = " & Ch3ZOffset & vbCrLf & _
                "Chuck 4 Offsets" & vbCrLf & _
                "X = " & Ch4XOffset & vbCrLf & _
                "Y = " & Ch4YOffset & vbCrLf & _
                "Z = " & Ch4ZOffset), vbOKOnly + vbInformation, "Parts Page Adjustment"
    End If
End Sub

rheintz · Jul 24, 2024

It seems when I run the Command Button none of my UserForms are holding their values. I just put a small code on the first form to save the values to 2 cells and nothing happened. I put a stop right after this save option and the combobox and textbox values are "". But when I execute from the Coder they store values.

dmt32 · Jul 24, 2024

Hi
welcome to the forum

With such a complex project it would be helpful to forum if you could place a copy of your workbook with dummy data in a file sharing site like Dropbox & maybe someone here will have time to see if can assist with your issue.

Dave

rheintz · Jul 24, 2024

dmt32 said:
Hi
welcome to the forum

With such a complex project it would be helpful to forum if you could place a copy of your workbook with dummy data in a file sharing site like Dropbox & maybe someone here will have time to see if can assist with your issue.

Dave

Have you seen this happen before, though? Everything works fine when launching from the VBA Editor, but an Excel command button doesn't work properly. For example, the command button launches my form, but once I fill in the information, it doesn't retain it.

VBA Code Works in Visual Basic Coder, but not with Command Button in Excel

rheintz

New Member

Attachments

Excel Facts

rheintz

New Member

dmt32

Well-known Member

rheintz

New Member

Similar threads

Forum statistics

Share this page

VBA Code Works in Visual Basic Coder, but not with Command Button in Excel

rheintz

New Member

Attachments

Excel Facts

rheintz

New Member

dmt32

Well-known Member

rheintz

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock