Excel VBA - Need Code to allow User to Select PDF file to Attach as Object to a Protected Worksheet

[Randy11]

Appears that Excel does not have a Sheet Protection Option that will allow a file Object to be attached to a worksheet when the sheet is protected. If there is a way to do this by adding a line to the ProtectAll code referred to below, a sample line would be very helpful. Barring this is not possible....

What I would like code to do is...

Call UnprotectAll (Have code for this)

Open the Insert Object option box in Excel so Operator can Select PDF & Insert as Object in excel.
Upon clicking OK, Insert Object into active cell.

Call ProtectAll (Have code for this)

 

[SkipVought]

hi,

In your code you must...
[tt]
1. UNPROTECT the sheet

2. add, change, delete, format, insert etc.

3. PROTECT the sheet
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Correct. Is there Code to Open the Object Box so File can be user selected then upon clicking ok to attach the file that the balance of the code will run.

 

[SkipVought]

Turn on your macro recorder and record doing what you intend.

Observe your recorded code.

Post back with your recorded code for help cstomizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

This macro was recorded to attach the BIT.pdf file. What is needed is that the User can choose the location & file to attach. Default Location can be "C:\Temp" if that is possible but still allow user to change locations if needed.


Sub Attach_File()
'
' Attach_File Macro
'

'
ActiveSheet.OLEObjects.Add(Filename:= _
"C:\Temp\BIT.PDF", Link _
:=False, DisplayAsIcon:=True, IconFileName:= _
"C:\WINDOWS\Installer\{AC76BA86-7AD7-1033-7B44-AA1000000001}\PDFFile_8.ico", _
IconIndex:=0, IconLabel:="BIT.PDF").Select
End Sub

 

[SkipVought]

Just ONE pdf?

What is needed is that the User can choose the location & file to attach.

Check out the GetOpenFileName method

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Located the attached code which works but has a few snags. One this inserts a Large box as the Icon, needs to be normal sized Icon. Also, would like the file name show up on the icon which does not in this case. Ideas???

Sub cmdPhoto_Click()
Dim VarFile As Variant, Ans As Variant
Dim col As Variant
'col = 3
'Sheet3.Cells(1, col).Select
VarFile = Application.GetOpenFilename("All Files (*.*), *.*")

If VarFile <> False Then
ActiveSheet.OLEObjects.Add(Filename:=VarFile, Link:=False, DisplayAsIcon:=True).Select

End If

Ans = MsgBox("Do you want to add another photo file?", vbYesNo)
While Ans = vbYes
'col = col + 1
'Sheet3.Cells(1, col).Select
VarFile = Application.GetOpenFilename("All Files (*.*), *.*")
If VarFile <> False Then
ActiveSheet.OLEObjects.Add(Filename:=VarFile, Link:=False, DisplayAsIcon:=True).Select
End If
Ans = MsgBox("Do you want to add another file?", vbYesNo)
Wend

End Sub

 

[SkipVought]

One this inserts a Large box as the Icon, needs to be normal sized Icon.

Then change the Height or Width size property.

Also, would like the file name show ...

A Label control maybe?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Would like to add height & width to the following line of cose but not sure how to accomplish.

ActiveSheet.OLEObjects.Add(Filename:=VarFile, Link:=False, DisplayAsIcon:=True).Select

Have added the below two lines after the above code line which does fix the size but in a separate step. Is there a way to add this within the OLEObjects.Add code line?

Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 42#

Can you expand on the label control?

 

[SkipVought]

Open you Developer > Controls > Insert.

When you figure out what you need, then turn on your macro recorder.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Unfortunately that does not help. What I want to happen is the icon inserted to get the name of the file it was created from.
Can add the following to the code line but this relies on the file being open to get the name which does not happen in the code. Ideas on how to make reference to the selected files name?

IconLabel:=VarFile (Instead of VarFile Selected file?)
Ideas?

 

[SkipVought]

Unfortunately that does not help.

What does that mean? That is a very nebulous statement; not very helpful to someone who wants to help.

What did you do that did not yield the expected result?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Labelling Icon, this works for a lebel that stays the same.
Ideas on how we can get the Filename show instead of "SAMPLE"

ActiveSheet.OLEObjects.Add(FileName:=VarFile, IconFileName:=VarFile, Link:=False, DisplayAsIcon:=True, IconIndex:=0, IconLabel:="SAMPLE").Select

 

[SkipVought]

YourLabelObject.Text= VarFile

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...or assign VarFile to a cell below the shape.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

How about IconLabel:=VarFile

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...or this

Iconlabel:=Split(Split(varFile,".")(0),"\")(UBound(Split(Split(varFile,".")(0),"\")))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}