Question on Forms and functionality of macros

[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

 

[NickHK]

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"

 

[Bob Phillips]

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"

 

[Guest]

Thanks Bob and Nick!!

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"