PDA

View Full Version : Load Excel Userform from Powerpoint Button



f2e4
08-10-2009, 01:50 AM
Morning everyone,

I'm having one of those Mondays and drawing a real blank even though I know it is really simple.

Also, apologies if this is in the wrong section - Powerpoint / Excel????

I have a userform in a worksheet - C:\test\test.xls

I have a powerpoint presentation (powerpoint.ppt) in the same folder with a command button on slide1

Can anyone think of the code to call the userform from the closed workbook after clicking the powerpoint command button.

I just want it to load the form and show it on screen.

I know Userform1.show goes somewhere in there but lost after that.

Any help appreciated

F

kakaruzas
08-10-2009, 02:10 AM
Hi there,

I just wanted to know, why don't you put the form on the .ppt file? I think it would be much easier. The button code should be simple then:
Private Sub CommandButton_Click()
frmName.Show
End Sub

f2e4
08-10-2009, 03:05 AM
Hi there,

I just wanted to know, why don't you put the form on the .ppt file? I think it would be much easier. The button code should be simple then:
Private Sub CommandButton_Click()
frmName.Show
End Sub

The form is linked directly to some data in the excel workbook and is not a standalone form.

I'm doing a presentation and just want to press a button on a slide and have the form pop up to demonstrate what it looks like then I can close it and carry on.

Bob Phillips
08-10-2009, 03:46 AM
You will need a procedure called ShowForm in the workbook that does a simple



FormName.Show


and then use



Public Sub DoExcel()
Dim XLApp As Object
Dim XLWB As Object

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False
Set XLWB = XLApp.workbooks.Open("C:\test\Book With Form.xls")
XLApp.Windows(XLWB.Name).Visible = False
XLApp.Run "'" & XLWB.Name & "'!ShowForm"

XLWB.Close SaveChanges:=False
Set XLWB = Nothing
XLApp.Quit
Set XLApp = Nothing
End Sub

f2e4
08-10-2009, 04:00 AM
Thanks Bob,

I'm getting a 'Server threw up exception' automation error and 'Object Required' at this line:

XLWB.Close SaveChanges:=False

Bob Phillips
08-10-2009, 05:16 AM
Does your form do anything nasty?

f2e4
08-25-2009, 09:05 AM
Not that I am aware of

I have been playing around with it but still getting the error.

I think this is how the process should go:

1. Press command button in powerpoint

2. Code should only open the spreadsheet - no need to tell it to show userform and the excel spreadsheet does this automatically

3. Press the close spreadsheet button on the userform and excel closes.

4. Return to Powerpoint