Question on Forms and functionality of macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a client who would like for me to develop a spreadsheet that would
ideally consist of a successive set of forms - each one of which would have a
quetion. What would happen is that the user would answer the question on the
first form and then based on their answer, the next form would come up with a
new question. In total, there would be 30-50 questions.

Also based on the results of each question would be a calculation which
occurs (ie - if the user answers "No" to question 1, then perform calculatoin
A+B-C; if the user answers "Yes" to question 1, then perform (2*A)/(B+C)).
Ultimately, a set of data will be derived from the users full set of answers
and there will be graphs generated from this data.

I have the following questoins based on these requirements:

1 - Is it feasible in your mind to have 30-50 forms which are triggered by
the answer to the preceding question?

2 - Is it possible to have a macro open up a powerpoint file and copy graphs
from Excel into this file?

3 - Is there any other software (MS or other) that you think would better
suit these requirements?


I appreciate your input!


Rob
 
Rob,
You don't need 30-odd forms; use a single form and change the .text/.caption
of the controls for each question.
You could store the questions and the required branching on a worksheet.
e.g.

<QuestionNum><NextYes><NextNo><QText><ValA><ValB><ValC><Result>
<1><2><3><"Apple,Orange"><Insert><Insert><Insert><=A2+B2-C2>
<2><4><10><"Dog,Cat"><Insert><Insert><Insert><=(2*A3)/(B3+C3))>
<3><5><6><"Pen,Sword"><Insert><Insert><Insert><=A2^B2-C2>
....etc

Track which number question you are on, feed in the values of A, B and C
upon completion of the question and check the result.

One way to do it...

NickHK

"Linking to specific cells in pivot table"
 
Here is an example for point 2

Sub ChartToPresentation()
Const ppViewSlide As Long = 1
Const ppLayoutTitleOnly As Long = 11
Dim oPP As Object
Dim oPPPres As Object
Dim oPPSlide As Object

If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
On Error Resume Next
Set oPP = GetObject(, "Powerpoint.Application")
On Error GoTo 0
If oPP Is Nothing Then
Set oPP = CreateObject("Powerpoint.Application")
oPP.Visible = True
End If

Set oPPPres = oPP.Presentations.Add

oPP.ActiveWindow.ViewType = ppViewSlide

Set oPPSlide = oPPPres.Slides.Add(1, ppLayoutTitleOnly)
Set oPPPres = oPP.ActivePresentation
oPP.ActiveWindow.ViewType = ppViewSlide

Set oPPSlide = oPPPres.Slides _
(oPP.ActiveWindow.Selection.SlideRange.SlideIndex)

ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture

oPPSlide.Shapes.Paste.Select

oPP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
oPP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

Set oPPSlide = Nothing
Set oPPPres = Nothing
Set oPP = Nothing
End If

End Sub



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Linking to specific cells in pivot table"
 
Thanks Bob and Nick!!

Bob Phillips said:
Here is an example for point 2

Sub ChartToPresentation()
Const ppViewSlide As Long = 1
Const ppLayoutTitleOnly As Long = 11
Dim oPP As Object
Dim oPPPres As Object
Dim oPPSlide As Object

If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
On Error Resume Next
Set oPP = GetObject(, "Powerpoint.Application")
On Error GoTo 0
If oPP Is Nothing Then
Set oPP = CreateObject("Powerpoint.Application")
oPP.Visible = True
End If

Set oPPPres = oPP.Presentations.Add

oPP.ActiveWindow.ViewType = ppViewSlide

Set oPPSlide = oPPPres.Slides.Add(1, ppLayoutTitleOnly)
Set oPPPres = oPP.ActivePresentation
oPP.ActiveWindow.ViewType = ppViewSlide

Set oPPSlide = oPPPres.Slides _
(oPP.ActiveWindow.Selection.SlideRange.SlideIndex)

ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture

oPPSlide.Shapes.Paste.Select

oPP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
oPP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

Set oPPSlide = Nothing
Set oPPPres = Nothing
Set oPP = Nothing
End If

End Sub



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Linking to specific cells in pivot table"
Click to expand...
 
Back
Top