external link to start excel form

[lorraine]

Through a lot of trial and error, I finally got an excel form to run (I'm new
to this). I'd like to start the form from within a powerpoint presentation.
I can link to the macro in excel to start the form but then have to run the
macro. I'd like to be able to just click on something from powerpoint that
will make the excel form pop up (automatically run the macro). Is there some
code that I can copy or is there another way of getting this to work?

Thank you in advance for your help.
Lorraine

 

[Lman]

insert the following into your workbook code

Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
UserForm1.Show
End Sub

(where UserForm1 is the name of your form)

You should then be able to create a hyperlink to the excel file from within
powerpoint and when clicked on it will show the form but it should still keep
the powerpoint slide in the background.

Hope this helps..

 

[Gary Brown]

Using Automation, you could....

1) Make sure that there is a macro in the Excel workbook
that opens the form.
Example:
Public Sub OpenTheForm()
ufTest.Show
End Sub
- where 'OpenTheForm' is the macro
- where 'ufTest' is the name of the form

2) In PowerPoint, create a Command Button on your slide by...
- opening up the 'Control Toolbox' commandbar
- select the 'Command Button'
- draw the button at the bottom of the slide

3) Right Click on the Command Button and go to 'Properties'

4) Change the Caption to something like 'Open Excel Form'

5) x out of the Properties window

6) Double-click on the Command Button and the
Visual Basic Editor will appear

7) You will see something like...
Private Sub CommandButton1_Click()

End Sub

8)
- where strMacro is the macro in Excel
that opens the form
- where strWorkbook is the workbook that
contains the form
Replace that code with...

'/--- S T A R T -- O F -- M A C R O --------------/
Private Sub CommandButton1_Click()
Dim appExcel As Object
Dim strWorkbook As String
Dim strMacro As String
Dim x As Variant

On Error GoTo err_Sub

'- - - - V A R I A B L E S - - - - - -
strWorkbook = "C:\Temp\Book1.xls"
strMacro = "OpenTheForm"
'- - - - V A R I A B L E S - - - - - -

'open a new instance of Microsoft Excel (late binding)
Set appExcel = CreateObject("Excel.Application")
appExcel.Application.Visible = True

'Open a workbook
appExcel.Application.Workbooks.Open strWorkbook

'run the macro in Excel that opens the form
x = appExcel.Application.Run(strMacro)

'close the workbook with the form without saving
appExcel.ActiveWorkbook.Close SaveChanges:=False

'get out of Excel
appExcel.Quit

exit_Sub:
On Error Resume Next
Set appExcel = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & ") - " & Now()
GoTo exit_Sub

End Sub
'/--- E N D -- O F -- M A C R O --------------/