none
HOW TO SOLVE RUN TIME ERROR "424"-OBJECT REQUIRED RRS feed

  • Question

  • Private Sub CommandButton1_Click()
    '''''''''''validation''''''''''''''
    If VBA.IsDate(Me.TextBox1.Value) = False Then
        MsgBox "Please enter a correct Date Formate", vbCritical
        Exit Sub
        End If
    If VBA.IsNumeric(Me.TextBox2.Value) = False Then
        MsgBox "Please enter a correct OUTWORD Number", vbCritical
    Exit Sub
    End If
    If Me.TextBox3.Value = "" Then
        MsgBox "Please enter Label.value", vbCritical
    Exit Sub
    End If

    If Me.TextBox4.Value = "" Then
        MsgBox "Please enter Label.value", vbCritical
    Exit Sub
    End If
    If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False And Me.OptionButton3.Value = False Then
        MsgBox "Please Select Tapal Type", vbCritical
    Exit Sub
    End If
    If VBA.IsNumeric(Me.TextBox5.Value) = False Then
        MsgBox "Please enter a correct Rs. of Stampe", vbCritical
    Exit Sub
    End If
    ''''''''''''''''check duplcate'''''''''''''''''

    Dim SH As Worksheet
    Set SH = ThishWorkbook.Sheets("Preview")                                  "ERROR IS HERE"
    Dim n As Long
    If Application.WorksheetFunction.CountIf(SH.Range("B:B"), Me.TextBox2.Value) > 0 Then
    MsgBox "This OUTWORD Number is already Exsist in Database"
    Exit Sub
    End If
    n = SH.Range("A" & Application.Rows.Count).End(xlUp).Row

    SH.Unprotect "DPO-JUN"

    SH.Range("A" & n + 1).Value = Me.TextBox1.Value
    SH.Range("B" & n + 1).Value = Me.TextBox2.Value
    SH.Range("C" & n + 1).Value = Me.TextBox3.Value
    SH.Range("D" & n + 1).Value = Me.TextBox4.Value

    If Me.OptionButton1.Value = True Then SH.Range("E" & n + 1).Value = "Simple Tapal"
    If Me.OptionButton2.Value = True Then SH.Range("E" & n + 1).Value = "Unregisterd Post Parcel"
    If Me.OptionButton3.Value = True Then SH.Range("E" & n + 1).Value = "Reg. A.D."
    SH.Range("F" & n + 1).Value = Me.TextBox5.Value

    SH.Protect "DPO-JUN"
    End Sub

    Private Sub CommandButton2_Click()
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.OptionButton1.Value = False
    Me.OptionButton2.Value = False
    Me.OptionButton3.Value = False




    End Sub
    Wednesday, June 26, 2019 7:30 AM

All replies

  • ThishWorkbook should be ThisWorkbook

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 26, 2019 8:30 AM
  • First of all: Embed your code into your posts. Use the <> button in the toolbar.

    Then: Comments are always indicators for better code structure.. in the actual case for the extract method factoring. E.g.

    Option Explicit
    
    Private Sub CommandButton1_Click()
    
      If InputIsValid() Then
        If Not DuplicateOutputExists() Then
          CreateOutput
        End If
      End If
    
    End Sub
    
    Private Sub CommandButton2_Click()
    
      InitializeInputControls
    
    End Sub
    
    Private Sub CreateOutput()
    
      Dim SH As Worksheet
    
      Dim n As Long
    
      Set SH = ThisWorkbook.Sheets("Preview")
      n = SH.Range("A" & Application.Rows.Count).End(xlUp).Row
      SH.Unprotect "DPO-JUN"
      SH.Range("A" & n + 1).Value = Me.TextBox1.Value
      SH.Range("B" & n + 1).Value = Me.TextBox2.Value
      SH.Range("C" & n + 1).Value = Me.TextBox3.Value
      SH.Range("D" & n + 1).Value = Me.TextBox4.Value
      If Me.OptionButton1.Value Then
        SH.Range("E" & n + 1).Value = "Simple Tapal"
      End If
    
      If Me.OptionButton2.Value Then
        SH.Range("E" & n + 1).Value = "Unregisterd Post Parcel"
      End If
    
      If Me.OptionButton3.Value Then
        SH.Range("E" & n + 1).Value = "Reg. A.D."
      End If
    
      SH.Range("F" & n + 1).Value = Me.TextBox5.Value
      SH.Protect "DPO-JUN"
      Set SH = Nothing
    
    End Sub
    
    Private Function DuplicateOutputExists() As Boolean
    
      Dim SH As Worksheet
    
      Set SH = ThisWorkbook.Sheets("Preview")
      DuplicateOutputExists = (Application.WorksheetFunction.CountIf(SH.Range("B:B"), Me.TextBox2.Value) > 0)
      If DuplicateOutputExists Then
        MsgBox "This OUTWORD Number is already Exsist in Database"
      End If
    
      Set SH = Nothing
    
    End Function
    
    Private Sub InitializeInputControls()
    
      Me.TextBox1.Value = ""
      Me.TextBox2.Value = ""
      Me.TextBox3.Value = ""
      Me.TextBox4.Value = ""
      Me.TextBox5.Value = ""
      Me.OptionButton1.Value = False
      Me.OptionButton2.Value = False
      Me.OptionButton3.Value = False
    
    End Sub
    
    Private Function InputIsValid() As Boolean
    
      Dim ValiationMessage As String
    
      If Not VBA.IsDate(Me.TextBox1.Value) Then
        ValiationMessage = ValiationMessage & vbCrLf & "Please enter a correct Date Formate"
      End If
    
      If Not VBA.IsNumeric(Me.TextBox2.Value) Then
        ValiationMessage = ValiationMessage & vbCrLf & "Please enter a correct OUTWORD Number"
      End If
    
      If Me.TextBox3.Value = "" Then
        ValiationMessage = ValiationMessage & vbCrLf & "Please enter Label.value"
      End If
    
      If Me.TextBox4.Value = "" Then
        ValiationMessage = ValiationMessage & vbCrLf & "Please enter Label.value"
      End If
    
      If Not (Me.OptionButton1.Value Or Me.OptionButton2.Value Or Me.OptionButton3.Value) Then
        ValiationMessage = ValiationMessage & vbCrLf & "Please Select Tapal Type"
      End If
    
      If Not VBA.IsNumeric(Me.TextBox5.Value) Then
        ValiationMessage = ValiationMessage & vbCrLf & "Please enter a correct Rs. of Stampe"
      End If
    
      InputIsValid = (Len(ValiationMessage) = 0)
      If Not InputIsValid Then
        ValiationMessage = "Validation errors: " & ValiationMessage
        MsgBox ValiationMessage, vbCrititcal
      End If
    
    End Function

    And as already written, it's This not Thish.


    Wednesday, June 26, 2019 8:49 AM