Display data from a cell in User Form

[Connie]

I have a spreadsheet with the numbers 1-100 in column A. Column B
contains exam questions in rows 1-100. I would like to create a user
form that will display the questions one at a time to my students and
allow them to enter an answer selection (A, B, C, or D). That answer
will be stored on another tab for scoring. I would also like the
studentgs to be able to navigate forward and backwards (in case they
want to go back and change an answer to a previously answered
question). I know this has to be simple, but I'm having trouble
getting started. I've created other applications using Excel. I'm
not sure if a user form is the way to go or not. Could someone
please
point me in the right direction to get started? I would really
appreciate it. Thanks.

 

[John Bundy]

Of course you will have to do some adjustments but this should get you most
of the way there. I created a userform with a textbox for the question, 4
optionbuttons label A,B,C,D a commandbutton, and a spinbutton. If the
questions are in column A, then when you load, the question in Row 1 appears
in the textbox, use the up and down on the spinbutton to change selections (i
handled the error going back before question one, you will have to maintain
when to stop). When they click the commandbutton, it writes to a second sheet
named "Answers" the option they chose. So if the choose B for the question in
row 3, then on the "Answer" sheet you will see a B in row 3.


Dim question As Integer
Dim answer As String
Private Sub CommandButton1_Click()
Sheets("Answers").Cells(question, 1) = answer
End Sub

Private Sub OptionButton1_Click()
answer = "A"
End Sub

Private Sub OptionButton2_Click()
answer = "B"
End Sub

Private Sub OptionButton3_Click()
answer = "C"
End Sub

Private Sub OptionButton4_Click()
answer = "D"
End Sub

Private Sub SpinButton1_SpinDown()
If question > 1 Then question = question - 1
updateTextbox
End Sub

Private Sub SpinButton1_SpinUp()
question = question + 1
updateTextbox
End Sub

Private Sub updateTextbox()

TextBox1.Text = Sheets(1).Cells(question, 1)
End Sub

Private Sub UserForm_Activate()
question = 1
TextBox1.Text = Sheets(1).Cells(question, 1)
End Sub

 

[Shane Devenshire]

Hi,

I know you asked for the answers on a different sheet but first you should
look at this:

Put your cursor in the data and choose Data, Form. This gives you the
flexibiity you want without any need to program.

To take care of the issue of putting the answers on a different sheet, you
could put them, as another column in the data area, then you could format
them with a custom format of ;;; which hides the cell contents from
displaying in the cell. (There is also a way to hide the cell contents from
displaying on the formula bar.) Finally, you could set up formulas that
refer to the answer cells discussed above on another sheet.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire