Excel Forum

Code: Select all

Sub Update_Weight() '''    *** Update   *** '''
    Dim sh As Worksheet
    Dim iRow As Long
    Dim x As Object
    Dim str As String

    For Each x In frmForm.Frame5.Controls
        If x.Value = True Then
            str = x.Name
            Exit For
        End If
    Next

        Set sh = ThisWorkbook.Sheets(str)

        If frmForm.txtRowNumber.Value = "" Then
            iRow = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
        Else
            iRow = frmForm.txtRowNumber.Value
        End If
            If frmForm.txtPart.Value = "" Then
                MsgBox "Please specify Part No."
            ElseIf frmForm.txtName.Value = "" Then
                MsgBox "Please specify PART Name."
            ElseIf Len(frmForm.txtModel.Value) <> 3 Then
                MsgBox "Please specify Model"
            ElseIf Len(frmForm.txtControl.Value) <> 4 Then
                MsgBox "Please specify CONTROL"
            ElseIf frmForm.txtQty.Value = "" Then
                MsgBox "Please specify Q'TY"
            ElseIf frmForm.txtPiece.Value = "" Then
                MsgBox "Please specify By Piece"
            ElseIf Len(frmForm.txtDate.Value) <> 8 Then
                MsgBox "Please specify Date."
            ElseIf Len(frmForm.txtID.Value) <> 4 Then
                frmForm.txtPart.SetFocus
            Else
            
             With sh
                .Cells(iRow, 1) = iRow - 2
                .Cells(iRow, 2) = frmForm.txtPart.Value
                .Cells(iRow, 3) = frmForm.txtName.Value
                .Cells(iRow, 4) = frmForm.txtModel.Value
                .Cells(iRow, 5) = frmForm.txtControl.Value
                .Cells(iRow, 6) = frmForm.txtQty.Value
                .Cells(iRow, 7) = frmForm.txtBox1.Value
                .Cells(iRow, 8) = frmForm.txtBox2.Value
                .Cells(iRow, 9) = frmForm.txtBox3.Value
                .Cells(iRow, 10) = frmForm.txtBox4.Value
                .Cells(iRow, 11) = frmForm.txtBox5.Value
                .Cells(iRow, 12) = frmForm.txtBox6.Value
                .Cells(iRow, 13) = frmForm.txtBox7.Value
                .Cells(iRow, 14) = frmForm.txtBox8.Value
                .Cells(iRow, 15) = frmForm.txtBox9.Value
                .Cells(iRow, 16) = frmForm.txtBox10.Value
                .Cells(iRow, 17) = frmForm.txtPiece.Value
                .Cells(iRow, 18) = frmForm.txtStandard.Value
                .Cells(iRow, 19) = frmForm.txtLower.Value
                .Cells(iRow, 20) = frmForm.txtUpper.Value
                .Cells(iRow, 21) = frmForm.txtPercent.Value
                .Cells(iRow, 22) = frmForm.txtPercent1.Value
                .Cells(iRow, 23) = frmForm.txtID.Value
                .Cells(iRow, 24) = frmForm.txtDate.Value
                .Cells(iRow, 25) = frmForm.txtIDName.Value
                .Cells(iRow, 26) = Left(frmForm.txtPart.Value, 8)
                .Cells(iRow, 27) = frmForm.txtCheck.Value
            End With
        End If
    'Call SaveData
 End Sub

Code: Select all

Sub Update_Weight() '''    *** Update   *** '''
    Dim sh As Worksheet
    Dim iRow As Long
    Dim lrow As Long
    Dim x As Object
    Dim str As String

    For Each x In frmForm.Frame5.Controls
        If x.Value = True Then
            str = x.Name
            Exit For
        End If
    Next

        Set sh = ThisWorkbook.Sheets(str)
      lrow = sh.Range("b" & sh.Rows.Count).End(xlUp).Row
        If frmForm.txtRowNumber.Value = "" Then
           ' iRow = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
            iRow = Application.Match(frmForm.txtPart.Value, sh.Range("b1:b" & lrow), 0)
        Else
            iRow = frmForm.txtRowNumber.Value
        End If