Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros, Forms, & Protected Worksheets..... how would I... | Setting up and Configuration of Excel | |||
Excel forms in macros | Excel Discussion (Misc queries) | |||
Forms and Macros | Excel Programming | |||
Question about XML functionality in Excel 2000/XP | Setting up and Configuration of Excel | |||
Question about XML functionality (add-ons?) in Excel 2000 & 2002 | Excel Discussion (Misc queries) |