+ Reply to Thread
Results 1 to 1 of 1

Excel survey using groups of radio buttons side by side

Hybrid View

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    1

    Excel survey using groups of radio buttons side by side

    Hello
    I'm trying to modify this piece of code so that I get a Performance - Importance set of radio buttons on an Excel-Based Questionnaire

    For a number of items, I want to ask 1 ) how well the employee can do the task and 2 ) How important is that task for the successful completion of the work

    For example:
    How well can you do this? How important is this?
    Read Spanish 5 radio buttons 5 radio buttons
    Speak Mandarin 5 radio buttons 5 radio buttons
    Observe punctuality 5 radio buttons 5 radio buttons

    The code below (thank you Dave!) was developed to create one column group of radio buttons, and that works.
    My problem comes when I created the "How important is this?" second column group - for some reason, I cannot get the group to the right to be fully independent.
    That is, if I first answer the "how well can you do this" and then answer the second question, it works.
    But if I reverse this and first answer the "how important is this?", a radio button gets selected automatically in the first group under the "How well can you do this" question.

    It probably is something simple but I can't find it. Help?
    Thanks!

    Sub SetupSurveyForm_1()
    'code written by Dave Peterson 2005-10-27
    'creates a survey form with option buttons
    'http://www.contextures.com/xlForm01.html
    
    '
      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 OptBtnCell1 As Range
    '
      Dim NumberOfQuestions As Long
      Dim myBorders As Variant
    
      myBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
            xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
    
      maxBtns = 5
      NumberOfQuestions = 16
    
      Set wks = ActiveSheet
      
      With wks
        Set OptBtnCell1 = .Range("h26")
        With OptBtnCell1.Offset(-1, 0).Resize(1, maxBtns)
            .Value = Array("Not Well", "A little", _
                                       "OK", "Well", "Very Well")
            .Orientation = 90
            .HorizontalAlignment = xlCenter
        End With
    
        Set myRange = OptBtnCell1.Resize(NumberOfQuestions, 1)
    
    
        With myRange.Offset(0, 14)
          .FormulaR1C1 = "=rc[1]*1"
        End With
    
    
    
        myRange.EntireRow.RowHeight = 28
        myRange.Resize(, maxBtns).EntireColumn.ColumnWidth = 4
    
    
      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 = ""
            If iCtr = 0 Then
              With myCell.Offset(0, 14)
                optBtn.LinkedCell = .Address(external:=True)
              End With
            End If
          End With
        Next iCtr
      Next myCell
    
    End Sub
    
    Sub SetupSurveyForm_2()
    'code written by Dave Peterson 2005-10-27
    'creates a survey form with option buttons
    'http://www.contextures.com/xlForm01.html
    '
      Dim grpBox2 As GroupBox
      Dim optBtn2 As OptionButton
      Dim maxBtns2 As Long
      Dim myCell2 As Range
      Dim myRange2 As Range
      Dim wks As Worksheet
      Dim iCtr2 As Long
      Dim OptBtnCell2 As Range
      Dim NumberOfQuestions2 As Long
      Dim myBorders As Variant
    
      myBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
            xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
    
      maxBtns2 = 5
      NumberOfQuestions2 = 16
    
      Set wks = ActiveSheet
      
      With wks
        Set OptBtnCell2 = .Range("n26")
        With OptBtnCell2.Offset(-1, 0).Resize(1, maxBtns2)
            .Value = Array("Not Well", "A little", _
                                       "OK", "Well", "Very Well")
            .Orientation = 90
            .HorizontalAlignment = xlCenter
        End With
    
        Set myRange2 = OptBtnCell2.Resize(NumberOfQuestions2, 1)
    
    
        With myRange2.Offset(0, 9)
          .FormulaR1C1 = "=rc[1]*1"
        End With
    
    
    
        myRange2.EntireRow.RowHeight = 28
        myRange2.Resize(, maxBtns2).EntireColumn.ColumnWidth = 4
    
    
      End With
    
      For Each myCell2 In myRange2
        With myCell2.Resize(1, maxBtns2)
          Set grpBox2 = wks.GroupBoxes.Add _
              (Top:=.Top, Left:=.Left, Height:=.Height, _
               Width:=.Width)
          With grpBox2
            .Caption = ""
            .Visible = True 'False
          End With
        End With
        For iCtr2 = 0 To maxBtns2 - 1
          With myCell2.Offset(0, iCtr2)
            Set optBtn2 = wks.OptionButtons.Add _
                  (Top:=.Top, Left:=.Left, Height:=.Height, _
                   Width:=.Width)
            optBtn2.Caption = ""
            If iCtr2 = 0 Then
              With myCell2.Offset(0, 9)
                optBtn2.LinkedCell = .Address(external:=True)
              End With
            End If
          End With
        Next iCtr2
      Next myCell2
    End Sub
    Last edited by Leith Ross; 05-18-2013 at 04:06 AM. Reason: Added Code Tags

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1