Inputboxes And Getting Data

Sebhughes.com

Sebhughes.com

Well-known Member
Joined
Oct 11, 2004
Messages
650
On sheet 2 in cell a1 i have a question and in cell b1 the answer, and in cell a2 a question and b2 the answer and so on till cell a10. I need a macro so when i press a button the it randomly gets a question from one the the cells and the user has to input the answer and if the answer is correct a yes will appear in cell a1 of sheet to if they get it worng a no will appear in cell a1 of sheet 2
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hi Seb,

Rather than overwrite the question in A1, this code puts 'Yes/No' in C1:
Code: 
Private Sub CommandButton1_Click()
Const UpperBound As Integer = 10
Const LowerBound As Integer = 1
Dim iRow As Integer
Dim vReply As Variant

Dim WS As Worksheet

Set WS = Sheets("Sheet2")

iRow = Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)

vReply = Application.InputBox(prompt:=WS.Range("A" & iRow).Text & "?")
If LCase$(Trim$((vReply))) = LCase$(Trim$(WS.Range("B" & iRow).Text)) Then
    WS.Range("C1").Value = "Yes"
Else
    WS.Range("C1").Value = "No"
End If
End Sub
HTH

alan
 
Upvote 0
Thanks works fine btu how do i do it so it picks a random question but only once until all the question have been used. so say i have 10 question that the same question does not appear twice until all the quesations have been used
 
Upvote 0
Hi Seb,

This code assumes finds the no of questions in column A of sheet2 & selects from those. If all questions have been asked, it exits with a message 'That's all folks!"

You might want a 'Reset' button to reset the global variable 'gCheck' :
gCheck=""

Code: 
Dim gCheck As String
Private Sub CommandButton1_Click()
Const LowerBound As Integer = 1
Dim iRow As Integer
Dim iRowEnd As Integer

Dim vReply As Variant

Dim WS As Worksheet

Set WS = Sheets("Sheet2")
iRowEnd = WS.Range("A65536").End(xlUp).Row

If Len(gCheck) = 0 Then gCheck = String(iRowEnd, " ")
If gCheck = String(iRowEnd, "X") Then
    MsgBox "That's all folks!"
    Exit Sub
End If

iRow = Int((iRowEnd - LowerBound + 1) * Rnd + LowerBound)
Do While Mid$(gCheck, iRow, 1) = "X"
    iRow = iRow + 1
    If iRow > iRowEnd Then iRow = 1
Loop

vReply = Application.InputBox(prompt:=WS.Range("A" & iRow).Text & "?")
If LCase$(Trim$((vReply))) = LCase$(Trim$(WS.Range("B" & iRow).Text)) Then
    WS.Range("C1").Value = "Yes"
Else
    WS.Range("C1").Value = "No"
End If
Mid$(gCheck, iRow, 1) = "X"
End Sub

HTH

Alan

PS
I've just noticed - this is my post #1000 - I'm a one star general!
 
Upvote 0
How would I intergrate This code with the code you gave me

Code: 
Private Sub CommandButton1_Click()

Const UpperBound As Integer = 4
Const LowerBound As Integer = 3
Dim iRow As Integer
Dim vReply As Variant

Dim WS As Worksheet

Set WS = Sheets("Questions")
 CommandButton2.Visible = False
iRow = Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)
 If Intersect(Selection, Range("c8:e10")) Is Nothing Then Exit Sub
  If Selection.Rows.Count > 1 Then
    MsgBox "Please Select 1 Square Only", 0, "Error"
    Exit Sub
  End If
  If Application.CountA(Selection) <> 0 Then
    MsgBox "The Square You Have Selected Already Contains A Move", 0, " Error"
    Exit Sub
  End If
vReply = Application.InputBox(prompt:=WS.Range("A" & iRow).Text & "?")
If LCase$(Trim$((vReply))) = LCase$(Trim$(WS.Range("B" & iRow).Text)) Then
 MsgBox ("You Answered Correctly")
 ActiveCell.FormulaR1C1 = "X"
 CommandButton1.Visible = False
    CommandButton2.Visible = True

    
       
       Else
    MsgBox ("Sorry Wrong Answer")
    CommandButton1.Visible = False
    CommandButton2.Visible = True
End If


Dim sReply As String

 
sReply = CheckRows
  If sReply = "" Then sReply = CheckCols
  If sReply = "" Then sReply = CheckDiags
  Select Case sReply
    Case "X"
        [G10] = [c6] & " Wins!"
        Range("i11").Value = Range("i11").Value + 1
            Range("g8,m8").Value = Range("g8,m8").Value + 1
            Range("C8:E10").Select
   Selection.ClearContents
 Range("C7").Select
   Selection.ClearContents
   Range("m12").Select
   CommandButton1.Visible = True
    CommandButton2.Visible = True
    
    Case "O"
       [G10] = [E6] & " Wins!"
        Range("I8,m9").Value = Range("I8,m9").Value + 1
         Range("i11").Value = Range("i11").Value + 1
       Range("C8:E10").Select
   Selection.ClearContents
 Range("C7").Select
   Selection.ClearContents
   Range("m12").Select
   CommandButton1.Visible = True
    CommandButton2.Visible = True
    Case "*"
          [G10] = "Tie!"
        Range("H8").Value = Range("H8").Value + 1
         Range("i11").Value = Range("i11").Value + 1
       Range("C8:E10").Select
   Selection.ClearContents
 Range("C7").Select
   Selection.ClearContents
   Range("m12").Select
   CommandButton1.Visible = True
    CommandButton2.Visible = True
  End Select
 
        
End Sub
 
Upvote 0
Hi Seb, I see that you have these constants defined:
Const UpperBound As Integer = 4
Const LowerBound As Integer = 3

This will limit the questions to those in row 3 or 4 only. Is that what you require, or do you want anything in column A to be a candidate?

Alan
 
Upvote 0
Hi Seb,

I'll assume that the answer to my last Q is "you want anything in column A to be a candidate", so here's your CommandButton1_click code amended (but untested):
Code: 
Dim gCheck As String
Private Sub CommandButton1_Click()
'Const UpperBound As Integer = 4
'Const LowerBound As Integer = 3
Dim iRow As Integer
Dim iRowEnd As Integer
Dim vReply As Variant

Dim WS As Worksheet

Set WS = Sheets("Questions")
iRowEnd = WS.Range("A65536").End(xlUp).Row

CommandButton2.Visible = False
If Intersect(Selection, Range("c8:e10")) Is Nothing Then Exit Sub
If Selection.Rows.Count > 1 Then
    MsgBox "Please Select 1 Square Only", 0, "Error"
    Exit Sub
End If
If Application.CountA(Selection) <> 0 Then
    MsgBox "The Square You Have Selected Already Contains A Move", 0, " Error"
    Exit Sub
End If

'iRow = Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)
iRow = Int(iRowEnd * Rnd + 1)
If Len(gCheck) = 0 Then gCheck = String(iRowEnd, " ")
If gCheck = String(iRowEnd, "X") Then
    MsgBox "All questions asked!"
    Exit Sub
End If
Do While Mid$(gCheck, iRow, 1) = "X"
    iRow = iRow + 1
    If iRow > iRowEnd Then iRow = 1
Loop

vReply = Application.InputBox(prompt:=WS.Range("A" & iRow).Text & "?")
If LCase$(Trim$((vReply))) = LCase$(Trim$(WS.Range("B" & iRow).Text)) Then
    MsgBox ("You Answered Correctly")
    ActiveCell.FormulaR1C1 = "X"
    CommandButton1.Visible = False
    CommandButton2.Visible = True
Else
    MsgBox ("Sorry Wrong Answer")
    CommandButton1.Visible = False
    CommandButton2.Visible = True
End If

Mid$(gCheck, iRow, 1) = "X"

Dim sReply As String

sReply = CheckRows
If sReply = "" Then sReply = CheckCols
If sReply = "" Then sReply = CheckDiags
Select Case sReply
Case "X"
    [G10] = [c6] & " Wins!"
    Range("i11").Value = Range("i11").Value + 1
    Range("g8,m8").Value = Range("g8,m8").Value + 1
    Range("C8:E10").Select
    Selection.ClearContents
    Range("C7").Select
    Selection.ClearContents
    Range("m12").Select
    CommandButton1.Visible = True
    CommandButton2.Visible = True
Case "O"
    [G10] = [E6] & " Wins!"
    Range("I8,m9").Value = Range("I8,m9").Value + 1
    Range("i11").Value = Range("i11").Value + 1
    Range("C8:E10").Select
    Selection.ClearContents
    Range("C7").Select
    Selection.ClearContents
    Range("m12").Select
    CommandButton1.Visible = True
    CommandButton2.Visible = True
Case "*"
    [G10] = "Tie!"
    Range("H8").Value = Range("H8").Value + 1
    Range("i11").Value = Range("i11").Value + 1
    Range("C8:E10").Select
    Selection.ClearContents
    Range("C7").Select
    Selection.ClearContents
    Range("m12").Select
    CommandButton1.Visible = True
    CommandButton2.Visible = True
End Select
        
End Sub

HTh

Alan
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Random question (and answer) generator
Replies
5
Views
248
mumps
mumps
apgmin
  • Solved
Dim text on other cell value
Replies
4
Views
181
SeniorNewbie
SeniorNewbie
I
  • Question Question
Protected access to a particular cell
Replies
2
Views
56
Fluff
Fluff
S
  • Question Question
Assistance with If then else Formula!
Replies
3
Views
45
Peter_SSs
Peter_SSs
R
  • Question Question
Show test results, % of correct answers, from a quiz.
Replies
0
Views
229
RodneyW
R

Forum statistics

Threads
1,225,190
Messages
6,183,450
Members
453,160
Latest member
DaveM_26

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top