Excel - .pdf file not working in UserForm

Asked By Phil H

17-Mar-10 07:59 AM

SITUATION: want to place a .pdf file in a UserForm so when a user clicks a
CommandButton in a worksheet, the UserForm comes up displaying the .pdf file
and the user can scroll through the pages.

WHAT???S BEEN DONE:
1.  UserForm3 created.
2.  Adobe AcroPDF1 control inserted/confirmed in UserForm ??? from Toolbox.
3.  CommandButton1 inserted in worksheet.
4.  The following code inserted in Module1 [thanks go to Jacob Skaria on
MSDN]:
Private Sub CommandButton1_Click()
With Me.AcroPDF1
.LoadFile "S:\.........\DTM-09-007.pdf"  [the file on local
share drive]
.setShowToolbar (False)
.gotoFirstPage
End With
End Sub

RESULT: On clicking CommandButton1:
1.  Error message: ???Compile Error:???
a.  ???Method or data member not found???
b.  Private Sub line yellow highlighted
c.  .AcroPDF1 cited (highlighted blue)

REQUEST:
1.  Resolve compile error.
2.  Have the .pdf file imbedded in the Excel workbook so when the workbook
is used by others outside the local share drive (.pdf file not accessible),
the .pdf file will come up and be operable in the UserForm.

17-Mar-10 10:22 AM

You say CommandButton1 is on a worksheet and the AcroPDF1 is in a userform
but that is completely at odds with your code. Assuming the setup is as you
describe, try something like this -

Private Sub CommandButton1_Click()
Dim sFile As String
sFile = "S:\.........\DTM-09-007.p"

With UserForm1.AcroPDF1
.LoadFile sFile
.setShowToolbar False
End With
UserForm1.Show

End Sub

Regards,
Peter T

17-Mar-10 12:47 PM

Peter,

Thanks for your help.  I did as you suggested and progress made.  The
compile error is gone and UserForm3 appears in the worksheet; however, there
is a blank white rectangular space in the middle of the UserForm, where page
one of the pdf file should appear.  In the VB Forms editor, UserForm3 shows
the same white space with the Adobe PDF (AcroPDF1) icon.  I have inserted the
complete file path (it???s accurate) in the code.  Any idea what to do next?
Also, is Module1 the correct place for the code?

Thanks, Phil

17-Mar-10 01:12 PM

Not sure why you get the white space, seems to work fine for me. Try loading
the pdf as the form is shown. The user will see the form load first then a
slight pause while the pdf is loaded, rather than the other way round.

'' userform code

Public gsFile As String

Private Sub UserForm_Activate()

With Me.AcroPDF1
.LoadFile gsFile
.setShowToolbar False
End With
End Sub

' worksheet module of the sheet with the CommandButton

Private Sub CommandButton1_Click()
Dim sFile As String
sFile = "S:\.........\DTM-09-007.pdf"

UserForm1.gsFile = sFile
UserForm1.Show ' this triggers the form's Activate event

End Sub


Well, the CommandButton's click event must be in the worksheet module of the
sheet that contains the CommandButton. Code in the event could go on to call
other code in normal modules.

Regards,
Peter T

17-Mar-10 02:41 PM

Thanks, Peter - will work on it tomorrow and get back.  R/Phil