VBA in module vs slide

[Guest]

Hi,

I'm trying to modify some VAB snippets. One comes from a presentation where
the code is displayed via the Project window under the MS PP Objects section
and is assigned to a specific slide and runs via a command button. It takes
info from text boxes (tbLName etc) on the slide and exports it to an Excel
file.

The rest came from a differrent presentation where they were in a module and
run in response to an Action button. They get the user name and print it out
on a certificate when the presentation is viewed.

I'd like to only have the user enter their info once and get both things
done. But if I try to put either bit of code in the other location (slide to
module or vice versa) it doesn't run. Can someone help me understand the
difference between code in the two locations so I can get this working?


'The first sub works from a slide via command button
Private Sub btnSubmit_Click()
Dim oAppXL As Object 'Declare the Excel application object

Dim strPath As String 'Let's the database location be in same
'location as presentation
'no matter what drive you are on
'First check if all the required fields are completed
If tbLName.Text = "" Or tbFName.Text = "" Or tbEmpNo.Text = "" Or
tbWorkAssignment.Text = "" Or tbDate.Text = "" Then
MsgBox "A necessary field is empty. Please check your " _
& "entries and submit again.", vbExclamation
Exit Sub
Else
'Will use the current path of this presentation to look
'for the Excel file in the same folder.
strPath = ActivePresentation.Path
Set oAppXL = CreateObject("Excel.application")
With oAppXL
'Each Excel command is preceded with an extra "."
'compared to normal code in Excel
'since PPT is actually telling Excel to run the code in Excel.
.workbooks.Open FileName:=strPath & "\" & "PassedQFTest.xls"
'We don't want to see Excel and it doesn't have to be visible to
run
.Visible = False

'Now run Excel and place the values
'This next line refers to the first open cell in column A that
is blank
'It will accept up to 65536 entries and if this
'were a really intelligent application
'it would check for the .row <> 65536 and
'create a new sheet to add more names.
If .Sheets("Database").Range("A65536"). _
End(xlup).Offset(1, 0).Value = "" Then
With .Sheets("Database").Range("A65536").End(xlup).Offset(1,
0)
'Now get the values from the text boxes and
'combobox in PowerPoint & place values in public variables
strUserLName = tbLName.Text
strUserFName = tbFName.Text
strUserEmpNo = tbEmpNo.Text
strUserAssignment = tbWorkAssignment.Text
strDate = tbDate.Text
'Add the values from the PPT page
.Value = strUserLName
.Offset(0, 1).Value = strUserFName
.Offset(0, 2).Value = strUserEmpNo
.Offset(0, 3).Value = strUserAssignment
.Offset(0, 4).Value = strDate
End With
End If
End With
End If
'Turn off warnings in Excel since the file is overwriting an existing file
oAppXL.Application.DisplayAlerts = False
'Wouldn't need to close or Quit if the upfront code handled that
differently
'but it still all happens quickly, so it might be a good idea anyway.
'Close and Save the file, the (True) tells Excel to save it.
oAppXL.activeworkbook.Close (True)
oAppXL.Application.Quit
'Clear the memory used by Excel
Set oAppXL = Nothing

'Now clear the existing values and be ready for a new entry.
tbLName.Text = ""
tbFName.Text = ""
tbEmpNo.Text = ""
tbWorkAssignment.Text = ""
tbDate.Text = ""

'Go to next slide
ActivePresentation.SlideShowWindow.View.Next

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The following comes from a module and runs via an action button

Sub GetStarted()
strUserLName = InputBox(Prompt:="Type your LAST name as it appears on
the hospital paycheck")
strUserFName = InputBox(Prompt:="Type your FIRST name as it appears on
the hospital paycheck")
strUserEmpNo = InputBox(Prompt:="Type your Employee Number")
strDate = InputBox(Prompt:="Type Today's Date")
ActivePresentation.SlideShowWindow.View.Next
End Sub

Sub PrintablePage()
Dim printableSlide As Slide

'The following lines are commented out because they have been moved to
the top
'of the module. This will allow PrintResults to use them to hide the
buttons
'before printing and show them again after printing.
'Dim DoneButton As Shape
'Dim printButton As Shape

Set printableSlide = ActivePresentation.Slides.Add(Index:=41, _
Layout:=ppLayoutText)

With printableSlide.Shapes _
.AddShape(1, 50, 200, 550, 50).TextFrame
.TextRange.Text = strUserLName & ", " & strUserFName & " # " &
strUserEmpNo
.TextRange.Font.Size = 24
.TextRange.Font.Bold = True
End With

With printableSlide.Shapes _
.AddShape(1, 50, 50, 550, 100).TextFrame
.TextRange.Text = "Core Measures & Quality Forms"
.TextRange.Font.Size = 36
.TextRange.Font.Bold = True
.MarginBottom = 1
.MarginLeft = 1
.MarginRight = 1
.MarginTop = 1
End With

printableSlide.Shapes(2).TextFrame.TextRange.Text = _
"The above named employee has been determined to be competent" & _
" to perform the above skills. Related policies and procedures" & _
" with competent demonstration are assessed, demonstrated," & _
" and validated by testing on" & strDate & "."

'Set DoneButton = ActivePresentation.Slides(41).Shapes.AddShape _
'(msoShapeActionButtonCustom, 400, 430, 150, 50)
'DoneButton.TextFrame.TextRange.Text = "Done!"
'DoneButton.ActionSettings(ppMouseClick).Action = ppActionRunMacro
' DoneButton.ActionSettings(ppMouseClick).Run = "Done"

Set printButton = ActivePresentation.Slides(41).Shapes.AddShape _
(msoShapeActionButtonCustom, 100, 430, 150, 50)
printButton.TextFrame.TextRange.Text = "Print Results"
printButton.ActionSettings(ppMouseClick).Action = ppActionRunMacro
printButton.ActionSettings(ppMouseClick).Run = "PrintResults"

ActivePresentation.SlideShowWindow.View.Next
ActivePresentation.Saved = True
End Sub

Sub PrintResults()
'DoneButton.Visible = False 'Added to hide button before printing
printButton.Visible = False 'Added to hide button before printing
With ActivePresentation
With .PrintOptions
.OutputType = ppPrintOutputSlides
.RangeType = ppPrintSlideRange
With .Ranges
.Add 41, 41
End With
End With
.PrintOut
End With

'DoneButton.Visible = True 'Added to show button again after printing
printButton.Visible = True 'Added to show button again after printing
Done

End Sub

 

[David M. Marcovitz]

Generally, code that is triggered by control toolbox objects is done in
the slide module and code that is triggered by buttons is done in a
regular module. Working between modules can be a bit tricky, but it can
be done. The easiest thing to do is declare your variables as Public in
your regular module. Then you can access those variables in other
modules. Here's a simple example.

In a regular module, I can put the following code:

Public userName As String

Sub GetName()
userName = InputBox(prompt:="What's your name?")
End Sub

I can assign this to an action button. I can then create a control
command button and put that on a slide and put the following in the slide
module:

Private Sub CommandButton1_Click()
MsgBox userName
End Sub

If the user clicks on the action button to enter a name, they can also
click on the command button to run the code in the slide modeul which
accesses that name.

Additionally, the regular module can access the values in a control
object. For example, if a text box is on Slide 1, then the user could
display the contents of that text box by running code that is in the
regular module:

MsgBox Slide1.TextBox1.Text

--David
--
David M. Marcovitz
Microsoft PowerPoint MVP
Director of Graduate Programs in Educational Technology
Loyola College in Maryland
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/

=?Utf-8?B?Q2hyaXMgU3ltb25kcw==?=

 

[Guest]

Thanks very much, I'll give that a try. You probably recognized the second
half of the code as modified from your web page. Thanks very much for making
that available. I do some Access VBA but am new to PP so I'll be getting your
book too.

 

[David M. Marcovitz]

Yes, the code had my fingerprints all over it. Let us know how it works for
you.
--David

--
David M. Marcovitz
Microsoft PowerPoint MVP
Director of Graduate Programs in Educational Technology
Loyola College in Maryland
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/

=?Utf-8?B?Q2hyaXMgU3ltb25kcw==?=

 

[Steve Rindsberg]

Hi,

I'm trying to modify some VAB snippets. One comes from a presentation where
the code is displayed via the Project window under the MS PP Objects section
and is assigned to a specific slide and runs via a command button. It takes
info from text boxes (tbLName etc) on the slide and exports it to an Excel
file.

And comes from Ellen Finkelstein's "Do Everything With PowerPoint" book and was written
by our own Brian Reilly. And I've been working with it in the last few days too. And
the other is from David. Small world, isn't it? ;-)

Anyhow, see suggestions below:

The rest came from a differrent presentation where they were in a module and
run in response to an Action button. They get the user name and print it out
on a certificate when the presentation is viewed.

I'd like to only have the user enter their info once and get both things
done. But if I try to put either bit of code in the other location (slide to
module or vice versa) it doesn't run. Can someone help me understand the
difference between code in the two locations so I can get this working?

I'd approach this quite differently. The code in the first module is ok as is, assuming
it collects the data you need (easily enough modified if not) and stores them in
variables here:

strUserLName = tbLName.Text
strUserFName = tbFName.Text
strUserEmpNo = tbEmpNo.Text
strUserAssignment = tbWorkAssignment.Text
strDate = tbDate.Text

I'd consider adding another slide in the same presentation, all laid out as a certificate
but with dummy text in the text boxes you'll fill later. If you name the slide and each
of the text boxes, you can do something like this, which you'd insert just below the Set
XLApp=Nothing line:

' <aircode>
Dim oSl as Slide
Set oSl = ActivePresentation.Slides("Certificate") ' you've named the slide this
With oSl
.Shapes("UserName").TextFrame.TextRange.Text = strUserFname & " " & strUserLname
.Shapes("UserEmpNo".TextFrame.TextRange.Text = strUserEmpNo
' and so on
End With

With ActivePresentation
With .PrintOptions
.OutputType = ppPrintOutputSlides
.RangeType = ppPrintSlideRange
With .Ranges
.Add oSl.SlideIndex
End With
End With
.PrintOut
End With
' </aircode>