Question on Forms and functionality of macros
 

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

Question on Forms and functionality of macros
 

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"
crosoft.com wrote in
message ...
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

Question on Forms and functionality of macros
 

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"
crosoft.com wrote in
message ...
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

Question on Forms and functionality of macros
 

Thanks Bob and Nick!!

"Bob Phillips" wrote:

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"
crosoft.com wrote in
message ...
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