Survey results

[proctk]

Hi

don't know where to start sorry about lenght of post

I have a worksheet with 31 survey questions on it

question 1 starts on row 5.

column 1 question number
column 2 the question
column 3 first choice
column 4 second choice
colunm 5 third choice
column 5 forth choice
column 6 fith choice

When person completes the survey they select a checkbox under the culmn which represents their answer and when they are finished they click a done button on the sheet which save thier results to a folder call "viewpoint" with a random name.

The mastersheet looks the same as the surveysheet except there are no check boxes in the columns to select a answer. I'm trying to figure out a way to create a macro that will pull the results from every file in the folder "viewpoint" and add them together and place the end results in the correct column

Example option 1 option2 option3 option4 option 5

Question 1 - I like my job 5 2 3 4 5

so five people selected option 1 and 2 selected option 2 and so on

 

[Rijnsent]

Checkbox-mania

Hi proctk,

there are 2 pieces of code I can offer:

Sub testing()

directory = "C:\temp\viewpoint\"

file = Dir(directory & "\*.xls")
ChDir directory & "\"
Do While file <> ""    'Check all xls files in the directory
    work = bestand
    Workbooks.Open Filename:=directory & "\" & file, UpdateLinks:=0
    sht = ActiveSheet.Name
    
    For question = 1 To 31
        'Here you want to check all the checkboxes for the individual questions and then do something like: if checkbox2.value = true then resultsheet.cell(row,col).value = resultsheet.cell(row,col).value+1.
    Next question
    
    ActiveWindow.Close SaveChanges:=False
    k = k + 1
    file = Dir    ' Get next entry.
Loop

End Sub

That should help you opening all the Excel files in the result directory and reading the results. I don't know what type of checkboxes you used for your form, but you need to know their names in order to put the answer of the questions at the right result spot. A little help with finding out which checkboxes belong to which question can be:

Sub shapefinding()

For Each shp In ActiveSheet.Shapes
    Debug.Print shp.Name, shp.Left, shp.Top
Next shp

End Sub

I put the Left and Top there, so you can find the location of the different checkboxes, that could help you identifying them.

Greetz,

Koen

 

[erik.van.geit]

Hello, proctk,

they select a checkbox

when you can only have one answer for each question, you would better use optionbuttons
if you want to fill a large range with optionbuttons you can use some code

when all the results are displayed in one column, you can paste the values using "add"
therefore we would need more info about your layout
here an example just on same sheet

Sub test_pastespecial_add()
'Erik Van Geit
'051101
Dim rng(3) As Range
Dim I As Integer
Set rng(1) = Sheets(1).Range("A2:A10")
Set rng(2) = Sheets(1).Range("C2:C10")
Set rng(3) = Sheets(1).Range("E2:E10")

rng(3).ClearContents

    For I = 1 To 2
    rng(I)(1).Offset(-1) = "range #" & I
        With rng(I)
        'some sample data
        .Formula = "=INT(RAND()*10)+1"
        .Value = .Value
        rng(I).Copy
        'add the values to rng(3)
        rng(3).PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd, skipblanks:=False
        'other syntax
        'rng(3).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
        End With
    Next I
    
Application.CutCopyMode = False
rng(I)(1).Offset(-1) = "sum"
End Sub

if this works for you, you could apply it to your situation
you would only need

        rng(1).Copy
        rng(3).PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd, skipblanks:=False

kind regards,
Erik

 

[proctk]

Because of formating issues, I ended up using a combobox, The results that can be selected form the combobox is eith 1 or nothing.
In columns K to O starting at row 3 to 33 there is a combox in each cell on the worksheet that was created using the control tool box.

The names of the combo boxes have a consistent patern which relates to the code that you provided me for naming many combo boxes.

http://www.mrexcel.com/board2/viewtopic.php?t=176971&highlight=

Hope this expalins my format better, every question will have an answer

 

[erik.van.geit]

The results that can be selected form the combobox is eith 1 or nothing.

you will have to click twice in each box to change it
do you know about checkboxes ?

anyway, you received a method to pastevalues adding up
start getting this to work and then you can change to your needs

sleeping now,
Erik

 

[proctk]

I was going to use checkboxes but I had a formating issue. The checkbox would be inbeded on the worksheet. The problem I was having is I don't need any caption attached to the checkbox. I wanted the little box to be bigger, but my learning taught me that you can't change the size of the box.

so I decided that a combo box would be my next best alternative.

I reviewed the above code and I can't figure out how to get it to loop through each possible answer for each question and calculate the total score for all surveys and bring it to the summary sheet

The mastersheet were the results are being totaled is called "siewpointsum"

 

[erik.van.geit]

you can use cells as checkboxes
Damon Ostrander explained that in rather recent thread
example using rightclick

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim CheckBoxRange As Range
Set CheckBoxRange = Range("B2:F15")

If Intersect(Target, CheckBoxRange) Is Nothing Then Exit Sub
Cancel = True
Target = IIf(Target = "", Chr(252), "")
End Sub

set font for the checkboxrange to wingdings

can't figure out how to get it to loop through each possible answer for each question and calculate the total score for all surveys and bring it to the summary sheet

where are the data to copy?
where is the range to add them ?
you won't need a loop if you setup your data with some logic

sleeping again,
Erik

 

[proctk]

The data is to be copied from each file in th folder starting in column K and ending at column O. the are 31 columns that the data needs to calculate for

The total results will be copied to the same column in the summary sheet

example


question response (possiblitlity of 5)

do you like your job "option 1 selected" three time out if 5 surveys, therefore the result in the summaryshet culmn K should be the number 3


Would it be wasyier if I sent you my spreadsheet.

 

[erik.van.geit]

Would it be wasyier if I sent you my spreadsheet.

you can display a sample here using Colo's HTML Maker utility for displaying your Excel Worksheet on the board
however I think post important is to now the range to be copied
please tell us
which columns will be copied ?
what's the first row ? (last row is variable ?)

 

[proctk]

The data to be copied and sumed from each survey is in the follwoing range ("K3:O33")

Under each choice is a combo box

SurveyTemp.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Statement									Strongley Agree	Somewhat agree or disagree	Neither agree nor disagree	Somewhat Disagree	Strongly Disagree
3	My area/ department is a great place to work
4	I feel my efforts are appreciated
5	Communication in my area/department is healthy (open, honest, 2-way)
6	I receive recognition for doing a good job
7	I have a clear understand of how my performance is evaluated
8	I receive proper instructions and direction when I get work assignments
9	I have a good understanding of the products and services I am expected to promote/sell
10	My area/department supervisors/manager is sensitive to the demands of my work and personal like
11	I am treated with respect as an individual at work
Survey