CODEWITHSUNDEEP
excelvbaobjectole
Ali
Ali

Reputation: 3

Checking if an OLE Object exists in a worksheet

I'm working on a user form which requires the user to attach a file in the Excel.

I'm trying to build a control within the user form. I have placed a "Save & Close" command button within the form which checks if all the fields have been filled by the user and all attachments have been attached.

I have issue with checking the attachments in the worksheet. How do I make VBA check if any OLE objects exists in my worksheet which were attached by the user?

'Below is my code for checking and input into the file

Private Sub SAC_Click()
    If CLN.Text = "" Then
        MsgBox ("Company Legal Name is a mandatory field!")
    Else
        Range("A2").Value = CLN.Text
    End If

    If BRL.Text = "" Then
        MsgBox ("Business Registration/ License is a mandatory field!")
    Else
        Range("B2").Value = BRL.Text
    End If

    If COA.Text = "" Then
        MsgBox ("Company Address is a mandatory field!")
    Else
        Range("C2").Value = COA.Text
    End If

    Range("D2").Value = PON.Text
    Range("E2").Value = TNR.Text

    If BLA.Text = "" Then
        MsgBox ("Billing Address is a mandatory field!")
    Else
        Range("F2").Value = BLA.Text
    End If

'VAT Registration data

    If VRN.Text = "" Then
        MsgBox ("VAT Registration Number is a mandatory field!")
    Else
        Range("G2").Value = VRN.Text
    End If

If VRD.Text = "" Then
        MsgBox ("VAT Registration Date is a mandatory field!")
    Else
        Range("H2").Value = VRD.Text
    End If

If COR.Text = "" Then
        MsgBox ("Country of Registration is a mandatory field!")
    Else
        Range("I2").Value = COR.Text
    End If

'Finance information data
'Payment Terms
If PmtTerms.Text = "" Then
        MsgBox ("Payment Terms is a mandatory field!")
    Else
        Range("J2").Value = PmtTerms.Text
    End If

If PmtMtd.Text = "" Then
        MsgBox ("Method of Payment is a mandatory field!")
    Else
        Range("K2").Value = PmtMtd.Text
    End If

'Finance other contacts

If NAS.Text = "" Then
        MsgBox ("Name of Authorized Signatory is a mandatory field!")
    Else
        Range("L2").Value = NAS.Text
    End If

If EMA.Text = "" Then
        MsgBox ("E-mail address is a mandatory field!")
    Else
        Range("M2").Value = EMA.Text
    End If

If MNR.Text = "" Then
        MsgBox ("Mobile number is a mandatory field!")
    Else
        Range("N2").Value = MNR.Text
    End If

If FCF.Text = "" Then
        MsgBox ("Finace contact is a mandatory field!")
    Else
        Range("O2").Value = FCF.Text
    End If

If EMA2.Text = "" Then
        MsgBox ("Finace E-mail address is a mandatory field!")
    Else
        Range("P2").Value = EMA2.Text
    End If

If MNR2.Text = "" Then
        MsgBox ("Finace mobile number is a mandatory field!")
    Else
        Range("Q2").Value = MNR2.Text
    End If

'Business contact information

    Range("R2").Value = CFN.Text
    Range("S2").Value = EMA3.Text
    Range("T2").Value = MNR3.Text

'Testing saving control


End Sub

Upvotes: 0

Views: 1598

Answers (1)

Harassed Dad
Harassed Dad

Reputation: 4704

ThisWorkbook.Sheets(1).oleobjects.Count will be zero if there are no OLEObjects in a sheet. If you have some existing objects, then as long as you know how many objects you started with, then you could check if it's increased by one to determine if one was added

Upvotes: 1

Related Questions