PDA

View Full Version : Formula to check for embedded object and return a checkmark



samidi
10-12-2017, 08:39 AM
Hello everyone,

I'm working on an excel form template, and there is a portion where the user can upload/attach a receipt to claim expenses. What I want to do is have some sort of if statement that checks if a file has been attached, and returns a checkmark if it is the case. The problem I'm having is that I can't reference the area in which the embedded object appears, as it floats over the spreadsheet behind it. Any thoughts? Sorry if it is a bit unclear.

Kenneth Hobs
10-12-2017, 11:46 AM
Welcome to the forum!

For sheet1 codename, in a Module, run:

Sub oleCheck()
Dim o As OLEObject
If Sheet1.OLEObjects.Count = 0 Then Exit Sub
For Each o In Sheet1.OLEObjects
MsgBox o.TopLeftCell.Address(False, False), , o.Name
Next o
End Sub

samidi
10-12-2017, 12:08 PM
Thanks for the reply! In my case, the document upload is done on a separate sheet from where the checkmark goes. The object that is created when the document is uploaded is the 5th one in the workbook. How could I modify this macro to check off the box when the home sheet when the the 5th object is created in a separate sheet? Do I need to run an object count on the whole workbook?

Kenneth Hobs
10-12-2017, 12:19 PM
I guess if you don't know what sheet, you have to iterate most all sheets and count oleobjects to see if at least 5 exist.

As for the checkmark, do you mean a checkmark character or Form Checkbox control or ActiveX Checkbox control?

Since that would not be a change event, you would have to auto trigger by some other means or run by an event like Workbook Open or preferred macro run type.

samidi
10-19-2017, 11:36 AM
Hmm, still no luck... I'm trying to get a checkmark character =CHAR(80) to display in cell L7 of a page called "Main menu". The file is uploaded in a different sheet called SOW. There should actually only be one embedded object in the file as well, so as long as there is one embedded file in the entire document the check mark should be there.

Kenneth Hobs
10-19-2017, 01:22 PM
I guess you set a font that does that. I would use this one.

Sub oleCheck()
Dim o As OLEObject, i As Integer, ws As Worksheet
For Each ws In Worksheets
For Each o In ws.OLEObjects
i = i + 1
Next o
Next ws
'If i > 0 Then Worksheets("Main menu").Range("L7").Formula = "=Char(80)"
With Worksheets("Main menu").Range("L7")
.Value = ""
If i > 0 Then
.Font.Name = "WingDings"
.Value = "ü" 'Wingdings font, Alt+0252=checkmark symbol
End If
End With
End Sub