I need a form with 5 buttons that correspond with values 1-5

I need to create a form with 20 questions. For each question I'd like the 
user to check one of five buttons corresponding to the values 1-5. Then I'd 
like the form to total the score from 20 (1x20) to 100 (5x20).

If possible I'd like to weight some questions heavier then others.

Thanks for your help.

Stevereddy@aol.com
0
reddy (6)
9/24/2005 9:27:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
529 Views

Similar Articles

[PageSpeed] 44

Hi

Have the form ask one question at the time. Then you need a form with a
question and 5 score buttons plus a "next" button and maybe even a
"previous" button, an invisible question storage area and somewhere to
collect and calculate the returned scores.

HTH. Best wishes Harald

"Steven J Reddy" <Steven J Reddy@discussions.microsoft.com> skrev i melding
news:285E6DC8-97B8-46BE-86B6-1C41DE30BE86@microsoft.com...
> I need to create a form with 20 questions. For each question I'd like the
> user to check one of five buttons corresponding to the values 1-5. Then
I'd
> like the form to total the score from 20 (1x20) to 100 (5x20).
>
> If possible I'd like to weight some questions heavier then others.
>
> Thanks for your help.
>
> Stevereddy@aol.com


0
innocent (844)
9/24/2005 9:46:00 PM
Another option might be to make a worksheet look like that form.

Put 20 groupboxes from the Forms toolbar in (say) rows 2-21 (headers in rows
1??) in columns E:I.  (Question number in column D and descriptions in column
J.)

Then put 5 optionbuttons in each groupbox and link an optionbutton from each
groupbox to column C of that each row.  Put your weighting factor in column B of
each row and put a formula in column A of each row.

If question 3 is weighted 5, you could put 5 in B4 and =b4*c4 in A4.

If you want a macro that sets up a worksheet like that, you can try this against
a test worksheet:

Option Explicit
Sub SetupOneTime()

    Dim grpBox As GroupBox
    Dim optBtn As OptionButton
    Dim maxBtns As Long
    Dim myCell As Range
    Dim myRange As Range
    Dim wks As Worksheet
    Dim iCtr As Long
    Dim FirstOptBtnCell As Range
    Dim NumberOfQuestions As Long
    Dim myBorders As Variant
    
    myBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
                    xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
    
    maxBtns = 5
    NumberOfQuestions = 20
    
    Set wks = ActiveSheet
    With wks
        Set FirstOptBtnCell = .Range("e2")
        .Range("a:i").Clear
        With FirstOptBtnCell.Offset(-1, -1).Resize(1, maxBtns + 1)
            .Value = Array("Question#", "Resp1", "Resp2", _
                                       "Resp3", "Resp4", "Resp5")
            .Orientation = 90
            .HorizontalAlignment = xlCenter
        End With
        
        Set myRange = FirstOptBtnCell.Resize(NumberOfQuestions, 1)
        
        With myRange.Offset(0, -1)
            .Formula = "=row()-" & myRange.Row - 1
            .Value = .Value
        End With
        
        myRange.Offset(0, -3).Value = 1
        
        With myRange.Offset(0, -4)
            .FormulaR1C1 = "=rc[1]*rc[2]"
        End With
        
        .Range("a1").Formula = "=sum(A2:A" & NumberOfQuestions + 1 & ")"
               
        
        With myRange.Offset(0, -4).Resize(, 4)
            For iCtr = LBound(myBorders) To UBound(myBorders)
                With .Borders(myBorders(iCtr))
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            Next iCtr
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
        
        myRange.EntireRow.RowHeight = 28
        myRange.Resize(, maxBtns).EntireColumn.ColumnWidth = 4

        'clean up existing junk
        .GroupBoxes.Delete
        .OptionButtons.Delete

    End With

    For Each myCell In myRange
        With myCell.Resize(1, maxBtns)
            Set grpBox = wks.GroupBoxes.Add _
                            (Top:=.Top, Left:=.Left, Height:=.Height, _
                             Width:=.Width)
            With grpBox
                .Caption = ""
                .Visible = True 'False
            End With
        End With
        For iCtr = 0 To maxBtns - 1
            With myCell.Offset(0, iCtr)
                Set optBtn = wks.OptionButtons.Add _
                                (Top:=.Top, Left:=.Left, Height:=.Height, _
                                 Width:=.Width)
                optBtn.Caption = ""
                'optBtn.OnAction = ThisWorkbook.Name & "!CheckOpt"
                If iCtr = 0 Then
                    With myCell.Offset(0, -2)
                        optBtn.LinkedCell = .Address(external:=True)
                        '.NumberFormat = ";;;"
                    End With
                End If
            End With
        Next iCtr
    Next myCell
    
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Steven J Reddy wrote:
> 
> I need to create a form with 20 questions. For each question I'd like the
> user to check one of five buttons corresponding to the values 1-5. Then I'd
> like the form to total the score from 20 (1x20) to 100 (5x20).
> 
> If possible I'd like to weight some questions heavier then others.
> 
> Thanks for your help.
> 
> Stevereddy@aol.com

-- 

Dave Peterson
0
petersod (12005)
9/25/2005 2:09:30 AM
Reply:

Similar Artilces:

password for command button?
Is there a way to code a command button in its on click event that will prompt the user to enter a password to perform the command button's operation? Thank you. -Debbie On Jan 4, 11:34 am, Debbie S. <Debb...@discussions.microsoft.com> wrote: > Is there a way to code a command button in its on click event that will > prompt the user to enter a password to perform the command button's > operation? > > Thank you. > > -Debbie Yes, but you'll need to delve into VBA. Attach a click event to the button and in the code: Public Function Button1_Click() ...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

How do you turn off/on the "1" or "2" or "+" or "-" view hide/unh.
What is or how do you control, turn on/off the small "1" "2" or "+" "-" hide/unhide view buttons right above the "A" cell? Sometimes shows when I hide columns. Hi 'Data - Goup/Outline' -- Regards Frank Kabel Frankfurt, Germany Seth wrote: > What is or how do you control, turn on/off the small "1" "2" or "+" > "-" hide/unhide view buttons right above the "A" cell? Sometimes > shows when I hide columns. You can toggle them to hide or display with <Ctrl> <8> Usi...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

How disable postmaster reply in exchange 5.5
I receive mail from virus in my Exchange 5.5 with this format name@mydomain with a virus attached My exchange server reply with postmaster with the original mail attached. I think that this is Mydoom virus. Please help me to disable NDR messages for any e-mail sent to an invalid address. Virus send e-mail with field FROM with addres that want attack and my exchange reply with postmaster with the original mail that contain mydoom virus. Excuse me for my bad english. It is not possible to disable generation of NDR with E55. Why not install antivirus for Exchange? ALEX wrote: > I rece...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Word2007. Checkbox. No Form
Hello! We have a document (not a form) that we would like to use checkboxes in. This document has a couple of sections where it would be useful if the user could check if something is applicable. We can't protect the whole document. Thank you for your help! Stacey I actually used this feature in a form this morning. Although you don't say what version of Word you are using, you can access the Control Toolbox toolbar in Word 2003, or in Word 2007, it is on Developer tab. To show the developer tab, click the Microsoft Office Button and then click Word Options. S...

Unable to click customizing outlook today button
Unbable to click the customising outlook today button for Outlook 2000 install in the windows xp profession machine. I have updated the office 200 patch 3. But it seem remain the same problem. http://support.microsoft.com/default.aspx?scid=kb;en-us;820575 might be helpful. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Clarence" <anonymous@discussions.microsoft.com> wrote in message news:06d101c3c939$78175620$a401280a@phx.gbl... > Unbable to click the customising outlook today button for > Outlook 200...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

how to match the color of a button with its background?
Hi, I have a owner draw push button. I associate it with two bitmaps, one is down and the other is up. Is there a way to make the bitmaps's background color match the color of the window where the button is located. I manually choose the best matched color but still the result is not good. I can even see a white border line (not drawn by me) when the button is pushed. Please help. Thanks. Tony Check out http://msdn.microsoft.com/library/en-us/vcresed/html/_asug_choosing_an_opaque_or_transparent_background.asp May Young <tony@srac.com> wrote in message news:<408473...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

I would like to know how to set up a 'fill in the blanks' form?
I would like to set up a templet of sorts to fill in the blanks for certificates. ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

2 Domains, 1 Exchange Server
Hi, We're trying to go with 2 seperate 2003 domains, but with only one Exchange 2003 Server. What would be needed for the domain that the Exchange server is not in to access e-mail? We've contemplated having those users use OWA, but would really like them to be able to use Outlook 2003. We're a school district and want to segregate the kids domain from the administration domain, but only have funding for one Exchnage server. Any ideas\help will be appreciated. TIA. Hi, This should explain what it requires: http://support.microsoft.com/?id=278888 Leif "tj woo...

RMS 1.2 vs 1.3, plus integrate with Great Plains
1.) I am working on an assessment for upgrading our current RMS software from 1.2 to 1.3. My assessment will take in account the benefits, broken down by store operations (Retail) and the benefits to IT. Also, list risks and potential problems that might be experienced. We currently have 28 stores with 3+ registers per location with another 22 new stores on the schedule over the next 2 years. Are their other in this group of similiar size that have done this upgrade to the new version? 2.) If we do not upgrade the software, can we still integrate the RMS to Great Plains? Would we...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

Picture control on a form
What is the best way to fill a form with a picture control so that when the client window is resized the picture control will fill the form? -- Just Al Take a look at this and see if this is what you want: http://www.codeproject.com/dialog/bmpdlg01.asp Tom "Al" <Al@discussions.microsoft.com> wrote in message news:5BEA0FA2-3F95-4090-B908-A75DBF1D19DE@microsoft.com... > What is the best way to fill a form with a picture control so that when > the > client window is resized the picture control will fill the form? > -- > Just Al Thank You! -- Just Al &...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...