PDA

View Full Version : [SOLVED] Excel Jeopardy with randomly selected topics and questions.



daraymond
03-18-2019, 06:04 PM
I have created a jeopardy style game in excel using a list of topics, and lists of questions and answers for each topic i am on version 5 and everything works great, i just wanted to get peoples opinion of the project and see if there was a way to maybe make the code more elegant, im still relatively new to programming and most of what i know is from google searches and kind of plugging in the answer where ever it works.

It is currently just a template with space to plug in what ever you want.

thank you all for your feedback.

Logit
03-19-2019, 08:08 AM
.
Might be more convenient to include a command button on the answer sheet / view used for closing instead of relying on the BIG RED X in the upper right corner.

daraymond
03-19-2019, 09:22 AM
.
Might be more convenient to include a command button on the answer sheet / view used for closing instead of relying on the BIG RED X in the upper right corner.

if you look on the start page, it tells you that by double clicking anywhere on the question and answer screen you will return to the home screen.

daraymond
03-22-2019, 02:44 PM
in particular, this is the part i would like to fix, first it checks to see what topic is in each column then fills the questions and answers accordingly, it works fine, but is very redundant. it checks each column 10 times(for each topic) and there are 5 columns so its doing 50 if then statements to just populate the questions. is there a better way to do this?

If Final.Range("A1").Text = Topics.Range("E2").Text Then Final.Range("A2") = CatA.Range("H2").Text
Final.Range("B2") = CatA.Range("I2").Text
Final.Range("A3") = CatA.Range("H3").Text
Final.Range("B3") = CatA.Range("I3").Text
Final.Range("A4") = CatA.Range("H4").Text
Final.Range("B4") = CatA.Range("I4").Text
Final.Range("A5") = CatA.Range("H5").Text
Final.Range("B5") = CatA.Range("I5").Text
Final.Range("A6") = CatA.Range("H6").Text
Final.Range("B6") = CatA.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E3").Text Then
Final.Range("A2") = CatB.Range("H2").Text
Final.Range("B2") = CatB.Range("I2").Text
Final.Range("A3") = CatB.Range("H3").Text
Final.Range("B3") = CatB.Range("I3").Text
Final.Range("A4") = CatB.Range("H4").Text
Final.Range("B4") = CatB.Range("I4").Text
Final.Range("A5") = CatB.Range("H5").Text
Final.Range("B5") = CatB.Range("I5").Text
Final.Range("A6") = CatB.Range("H6").Text
Final.Range("B6") = CatB.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E4").Text Then
Final.Range("A2") = CatC.Range("H2").Text
Final.Range("B2") = CatC.Range("I2").Text
Final.Range("A3") = CatC.Range("H3").Text
Final.Range("B3") = CatC.Range("I3").Text
Final.Range("A4") = CatC.Range("H4").Text
Final.Range("B4") = CatC.Range("I4").Text
Final.Range("A5") = CatC.Range("H5").Text
Final.Range("B5") = CatC.Range("I5").Text
Final.Range("A6") = CatC.Range("H6").Text
Final.Range("B6") = CatC.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E5").Text Then
Final.Range("A2") = CatD.Range("H2").Text
Final.Range("B2") = CatD.Range("I2").Text
Final.Range("A3") = CatD.Range("H3").Text
Final.Range("B3") = CatD.Range("I3").Text
Final.Range("A4") = CatD.Range("H4").Text
Final.Range("B4") = CatD.Range("I4").Text
Final.Range("A5") = CatD.Range("H5").Text
Final.Range("B5") = CatD.Range("I5").Text
Final.Range("A6") = CatD.Range("H6").Text
Final.Range("B6") = CatD.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E6").Text Then
Final.Range("A2") = CatE.Range("H2").Text
Final.Range("B2") = CatE.Range("I2").Text
Final.Range("A3") = CatE.Range("H3").Text
Final.Range("B3") = CatE.Range("I3").Text
Final.Range("A4") = CatE.Range("H4").Text
Final.Range("B4") = CatE.Range("I4").Text
Final.Range("A5") = CatE.Range("H5").Text
Final.Range("B5") = CatE.Range("I5").Text
Final.Range("A6") = CatE.Range("H6").Text
Final.Range("B6") = CatE.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E7").Text Then
Final.Range("A2") = CatF.Range("H2").Text
Final.Range("B2") = CatF.Range("I2").Text
Final.Range("A3") = CatF.Range("H3").Text
Final.Range("B3") = CatF.Range("I3").Text
Final.Range("A4") = CatF.Range("H4").Text
Final.Range("B4") = CatF.Range("I4").Text
Final.Range("A5") = CatF.Range("H5").Text
Final.Range("B5") = CatF.Range("I5").Text
Final.Range("A6") = CatF.Range("H6").Text
Final.Range("B6") = CatF.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E8").Text Then
Final.Range("A2") = CatG.Range("H2").Text
Final.Range("B2") = CatG.Range("I2").Text
Final.Range("A3") = CatG.Range("H3").Text
Final.Range("B3") = CatG.Range("I3").Text
Final.Range("A4") = CatG.Range("H4").Text
Final.Range("B4") = CatG.Range("I4").Text
Final.Range("A5") = CatG.Range("H5").Text
Final.Range("B5") = CatG.Range("I5").Text
Final.Range("A6") = CatG.Range("H6").Text
Final.Range("B6") = CatG.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E9").Text Then
Final.Range("A2") = CatH.Range("H2").Text
Final.Range("B2") = CatH.Range("I2").Text
Final.Range("A3") = CatH.Range("H3").Text
Final.Range("B3") = CatH.Range("I3").Text
Final.Range("A4") = CatH.Range("H4").Text
Final.Range("B4") = CatH.Range("I4").Text
Final.Range("A5") = CatH.Range("H5").Text
Final.Range("B5") = CatH.Range("I5").Text
Final.Range("A6") = CatH.Range("H6").Text
Final.Range("B6") = CatH.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E10").Text Then
Final.Range("A2") = CatI.Range("H2").Text
Final.Range("B2") = CatI.Range("I2").Text
Final.Range("A3") = CatI.Range("H3").Text
Final.Range("B3") = CatI.Range("I3").Text
Final.Range("A4") = CatI.Range("H4").Text
Final.Range("B4") = CatI.Range("I4").Text
Final.Range("A5") = CatI.Range("H5").Text
Final.Range("B5") = CatI.Range("I5").Text
Final.Range("A6") = CatI.Range("H6").Text
Final.Range("B6") = CatI.Range("I6").Text
End If


If Final.Range("A1").Text = Topics.Range("E11").Text Then
Final.Range("A2") = CatJ.Range("H2").Text
Final.Range("B2") = CatJ.Range("I2").Text
Final.Range("A3") = CatJ.Range("H3").Text
Final.Range("B3") = CatJ.Range("I3").Text
Final.Range("A4") = CatJ.Range("H4").Text
Final.Range("B4") = CatJ.Range("I4").Text
Final.Range("A5") = CatJ.Range("H5").Text
Final.Range("B5") = CatJ.Range("I5").Text
Final.Range("A6") = CatJ.Range("H6").Text
Final.Range("B6") = CatJ.Range("I6").Text
End If


'//////////////////////////////////////////////////////


If Final.Range("C1").Text = Topics.Range("E2").Text Then
Final.Range("C2") = CatA.Range("H2").Text
Final.Range("D2") = CatA.Range("I2").Text
Final.Range("C3") = CatA.Range("H3").Text
Final.Range("D3") = CatA.Range("I3").Text
Final.Range("C4") = CatA.Range("H4").Text
Final.Range("D4") = CatA.Range("I4").Text
Final.Range("C5") = CatA.Range("H5").Text
Final.Range("D5") = CatA.Range("I5").Text
Final.Range("C6") = CatA.Range("H6").Text
Final.Range("D6") = CatA.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E3").Text Then
Final.Range("C2") = CatB.Range("H2").Text
Final.Range("D2") = CatB.Range("I2").Text
Final.Range("C3") = CatB.Range("H3").Text
Final.Range("D3") = CatB.Range("I3").Text
Final.Range("C4") = CatB.Range("H4").Text
Final.Range("D4") = CatB.Range("I4").Text
Final.Range("C5") = CatB.Range("H5").Text
Final.Range("D5") = CatB.Range("I5").Text
Final.Range("C6") = CatB.Range("H6").Text
Final.Range("D6") = CatB.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E4").Text Then
Final.Range("C2") = CatC.Range("H2").Text
Final.Range("D2") = CatC.Range("I2").Text
Final.Range("C3") = CatC.Range("H3").Text
Final.Range("D3") = CatC.Range("I3").Text
Final.Range("C4") = CatC.Range("H4").Text
Final.Range("D4") = CatC.Range("I4").Text
Final.Range("C5") = CatC.Range("H5").Text
Final.Range("D5") = CatC.Range("I5").Text
Final.Range("C6") = CatC.Range("H6").Text
Final.Range("D6") = CatC.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E5").Text Then
Final.Range("C2") = CatD.Range("H2").Text
Final.Range("D2") = CatD.Range("I2").Text
Final.Range("C3") = CatD.Range("H3").Text
Final.Range("D3") = CatD.Range("I3").Text
Final.Range("C4") = CatD.Range("H4").Text
Final.Range("D4") = CatD.Range("I4").Text
Final.Range("C5") = CatD.Range("H5").Text
Final.Range("D5") = CatD.Range("I5").Text
Final.Range("C6") = CatD.Range("H6").Text
Final.Range("D6") = CatD.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E6").Text Then
Final.Range("C2") = CatE.Range("H2").Text
Final.Range("D2") = CatE.Range("I2").Text
Final.Range("C3") = CatE.Range("H3").Text
Final.Range("D3") = CatE.Range("I3").Text
Final.Range("C4") = CatE.Range("H4").Text
Final.Range("D4") = CatE.Range("I4").Text
Final.Range("C5") = CatE.Range("H5").Text
Final.Range("D5") = CatE.Range("I5").Text
Final.Range("C6") = CatE.Range("H6").Text
Final.Range("D6") = CatE.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E7").Text Then
Final.Range("C2") = CatF.Range("H2").Text
Final.Range("D2") = CatF.Range("I2").Text
Final.Range("C3") = CatF.Range("H3").Text
Final.Range("D3") = CatF.Range("I3").Text
Final.Range("C4") = CatF.Range("H4").Text
Final.Range("D4") = CatF.Range("I4").Text
Final.Range("C5") = CatF.Range("H5").Text
Final.Range("D5") = CatF.Range("I5").Text
Final.Range("C6") = CatF.Range("H6").Text
Final.Range("D6") = CatF.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E8").Text Then
Final.Range("C2") = CatG.Range("H2").Text
Final.Range("D2") = CatG.Range("I2").Text
Final.Range("C3") = CatG.Range("H3").Text
Final.Range("D3") = CatG.Range("I3").Text
Final.Range("C4") = CatG.Range("H4").Text
Final.Range("D4") = CatG.Range("I4").Text
Final.Range("C5") = CatG.Range("H5").Text
Final.Range("D5") = CatG.Range("I5").Text
Final.Range("C6") = CatG.Range("H6").Text
Final.Range("D6") = CatG.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E9").Text Then
Final.Range("C2") = CatH.Range("H2").Text
Final.Range("D2") = CatH.Range("I2").Text
Final.Range("C3") = CatH.Range("H3").Text
Final.Range("D3") = CatH.Range("I3").Text
Final.Range("C4") = CatH.Range("H4").Text
Final.Range("D4") = CatH.Range("I4").Text
Final.Range("C5") = CatH.Range("H5").Text
Final.Range("D5") = CatH.Range("I5").Text
Final.Range("C6") = CatH.Range("H6").Text
Final.Range("D6") = CatH.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E10").Text Then
Final.Range("C2") = CatI.Range("H2").Text
Final.Range("D2") = CatI.Range("I2").Text
Final.Range("C3") = CatI.Range("H3").Text
Final.Range("D3") = CatI.Range("I3").Text
Final.Range("C4") = CatI.Range("H4").Text
Final.Range("D4") = CatI.Range("I4").Text
Final.Range("C5") = CatI.Range("H5").Text
Final.Range("D5") = CatI.Range("I5").Text
Final.Range("C6") = CatI.Range("H6").Text
Final.Range("D6") = CatI.Range("I6").Text
End If


If Final.Range("C1").Text = Topics.Range("E11").Text Then
Final.Range("C2") = CatJ.Range("H2").Text
Final.Range("D2") = CatJ.Range("I2").Text
Final.Range("C3") = CatJ.Range("H3").Text
Final.Range("D3") = CatJ.Range("I3").Text
Final.Range("C4") = CatJ.Range("H4").Text
Final.Range("D4") = CatJ.Range("I4").Text
Final.Range("C5") = CatJ.Range("H5").Text
Final.Range("D5") = CatJ.Range("I5").Text
Final.Range("C6") = CatJ.Range("H6").Text
Final.Range("D6") = CatJ.Range("I6").Text
End If


'/////////////////////////////////////////////////////////


If Final.Range("E1").Text = Topics.Range("E2").Text Then
Final.Range("E2") = CatA.Range("H2").Text
Final.Range("F2") = CatA.Range("I2").Text
Final.Range("E3") = CatA.Range("H3").Text
Final.Range("F3") = CatA.Range("I3").Text
Final.Range("E4") = CatA.Range("H4").Text
Final.Range("F4") = CatA.Range("I4").Text
Final.Range("E5") = CatA.Range("H5").Text
Final.Range("F5") = CatA.Range("I5").Text
Final.Range("E6") = CatA.Range("H6").Text
Final.Range("F6") = CatA.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E3").Text Then
Final.Range("E2") = CatB.Range("H2").Text
Final.Range("F2") = CatB.Range("I2").Text
Final.Range("E3") = CatB.Range("H3").Text
Final.Range("F3") = CatB.Range("I3").Text
Final.Range("E4") = CatB.Range("H4").Text
Final.Range("F4") = CatB.Range("I4").Text
Final.Range("E5") = CatB.Range("H5").Text
Final.Range("F5") = CatB.Range("I5").Text
Final.Range("E6") = CatB.Range("H6").Text
Final.Range("F6") = CatB.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E4").Text Then
Final.Range("E2") = CatC.Range("H2").Text
Final.Range("F2") = CatC.Range("I2").Text
Final.Range("E3") = CatC.Range("H3").Text
Final.Range("F3") = CatC.Range("I3").Text
Final.Range("E4") = CatC.Range("H4").Text
Final.Range("F4") = CatC.Range("I4").Text
Final.Range("E5") = CatC.Range("H5").Text
Final.Range("F5") = CatC.Range("I5").Text
Final.Range("E6") = CatC.Range("H6").Text
Final.Range("F6") = CatC.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E5").Text Then
Final.Range("E2") = CatD.Range("H2").Text
Final.Range("F2") = CatD.Range("I2").Text
Final.Range("E3") = CatD.Range("H3").Text
Final.Range("F3") = CatD.Range("I3").Text
Final.Range("E4") = CatD.Range("H4").Text
Final.Range("F4") = CatD.Range("I4").Text
Final.Range("E5") = CatD.Range("H5").Text
Final.Range("F5") = CatD.Range("I5").Text
Final.Range("E6") = CatD.Range("H6").Text
Final.Range("F6") = CatD.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E6").Text Then
Final.Range("E2") = CatE.Range("H2").Text
Final.Range("F2") = CatE.Range("I2").Text
Final.Range("E3") = CatE.Range("H3").Text
Final.Range("F3") = CatE.Range("I3").Text
Final.Range("E4") = CatE.Range("H4").Text
Final.Range("F4") = CatE.Range("I4").Text
Final.Range("E5") = CatE.Range("H5").Text
Final.Range("F5") = CatE.Range("I5").Text
Final.Range("E6") = CatE.Range("H6").Text
Final.Range("F6") = CatE.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E7").Text Then
Final.Range("E2") = CatF.Range("H2").Text
Final.Range("F2") = CatF.Range("I2").Text
Final.Range("E3") = CatF.Range("H3").Text
Final.Range("F3") = CatF.Range("I3").Text
Final.Range("E4") = CatF.Range("H4").Text
Final.Range("F4") = CatF.Range("I4").Text
Final.Range("E5") = CatF.Range("H5").Text
Final.Range("F5") = CatF.Range("I5").Text
Final.Range("E6") = CatF.Range("H6").Text
Final.Range("F6") = CatF.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E8").Text Then
Final.Range("E2") = CatG.Range("H2").Text
Final.Range("F2") = CatG.Range("I2").Text
Final.Range("E3") = CatG.Range("H3").Text
Final.Range("F3") = CatG.Range("I3").Text
Final.Range("E4") = CatG.Range("H4").Text
Final.Range("F4") = CatG.Range("I4").Text
Final.Range("E5") = CatG.Range("H5").Text
Final.Range("F5") = CatG.Range("I5").Text
Final.Range("E6") = CatG.Range("H6").Text
Final.Range("F6") = CatG.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E9").Text Then
Final.Range("E2") = CatH.Range("H2").Text
Final.Range("F2") = CatH.Range("I2").Text
Final.Range("E3") = CatH.Range("H3").Text
Final.Range("F3") = CatH.Range("I3").Text
Final.Range("E4") = CatH.Range("H4").Text
Final.Range("F4") = CatH.Range("I4").Text
Final.Range("E5") = CatH.Range("H5").Text
Final.Range("F5") = CatH.Range("I5").Text
Final.Range("E6") = CatH.Range("H6").Text
Final.Range("F6") = CatH.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E10").Text Then
Final.Range("E2") = CatI.Range("H2").Text
Final.Range("F2") = CatI.Range("I2").Text
Final.Range("E3") = CatI.Range("H3").Text
Final.Range("F3") = CatI.Range("I3").Text
Final.Range("E4") = CatI.Range("H4").Text
Final.Range("F4") = CatI.Range("I4").Text
Final.Range("E5") = CatI.Range("H5").Text
Final.Range("F5") = CatI.Range("I5").Text
Final.Range("E6") = CatI.Range("H6").Text
Final.Range("F6") = CatI.Range("I6").Text
End If


If Final.Range("E1").Text = Topics.Range("E11").Text Then
Final.Range("E2") = CatJ.Range("H2").Text
Final.Range("F2") = CatJ.Range("I2").Text
Final.Range("E3") = CatJ.Range("H3").Text
Final.Range("F3") = CatJ.Range("I3").Text
Final.Range("E4") = CatJ.Range("H4").Text
Final.Range("F4") = CatJ.Range("I4").Text
Final.Range("E5") = CatJ.Range("H5").Text
Final.Range("F5") = CatJ.Range("I5").Text
Final.Range("E6") = CatJ.Range("H6").Text
Final.Range("F6") = CatJ.Range("I6").Text
End If


'///////////////////////////////////////////////////////


If Final.Range("G1").Text = Topics.Range("E2").Text Then
Final.Range("G2") = CatA.Range("H2").Text
Final.Range("H2") = CatA.Range("I2").Text
Final.Range("G3") = CatA.Range("H3").Text
Final.Range("H3") = CatA.Range("I3").Text
Final.Range("G4") = CatA.Range("H4").Text
Final.Range("H4") = CatA.Range("I4").Text
Final.Range("G5") = CatA.Range("H5").Text
Final.Range("H5") = CatA.Range("I5").Text
Final.Range("G6") = CatA.Range("H6").Text
Final.Range("H6") = CatA.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E3").Text Then
Final.Range("G2") = CatB.Range("H2").Text
Final.Range("H2") = CatB.Range("I2").Text
Final.Range("G3") = CatB.Range("H3").Text
Final.Range("H3") = CatB.Range("I3").Text
Final.Range("G4") = CatB.Range("H4").Text
Final.Range("H4") = CatB.Range("I4").Text
Final.Range("G5") = CatB.Range("H5").Text
Final.Range("H5") = CatB.Range("I5").Text
Final.Range("G6") = CatB.Range("H6").Text
Final.Range("H6") = CatB.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E4").Text Then
Final.Range("G2") = CatC.Range("H2").Text
Final.Range("H2") = CatC.Range("I2").Text
Final.Range("G3") = CatC.Range("H3").Text
Final.Range("H3") = CatC.Range("I3").Text
Final.Range("G4") = CatC.Range("H4").Text
Final.Range("H4") = CatC.Range("I4").Text
Final.Range("G5") = CatC.Range("H5").Text
Final.Range("H5") = CatC.Range("I5").Text
Final.Range("G6") = CatC.Range("H6").Text
Final.Range("H6") = CatC.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E5").Text Then
Final.Range("G2") = CatD.Range("H2").Text
Final.Range("H2") = CatD.Range("I2").Text
Final.Range("G3") = CatD.Range("H3").Text
Final.Range("H3") = CatD.Range("I3").Text
Final.Range("G4") = CatD.Range("H4").Text
Final.Range("H4") = CatD.Range("I4").Text
Final.Range("G5") = CatD.Range("H5").Text
Final.Range("H5") = CatD.Range("I5").Text
Final.Range("G6") = CatD.Range("H6").Text
Final.Range("H6") = CatD.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E6").Text Then
Final.Range("G2") = CatE.Range("H2").Text
Final.Range("H2") = CatE.Range("I2").Text
Final.Range("G3") = CatE.Range("H3").Text
Final.Range("H3") = CatE.Range("I3").Text
Final.Range("G4") = CatE.Range("H4").Text
Final.Range("H4") = CatE.Range("I4").Text
Final.Range("G5") = CatE.Range("H5").Text
Final.Range("H5") = CatE.Range("I5").Text
Final.Range("G6") = CatE.Range("H6").Text
Final.Range("H6") = CatE.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E7").Text Then
Final.Range("G2") = CatF.Range("H2").Text
Final.Range("H2") = CatF.Range("I2").Text
Final.Range("G3") = CatF.Range("H3").Text
Final.Range("H3") = CatF.Range("I3").Text
Final.Range("G4") = CatF.Range("H4").Text
Final.Range("H4") = CatF.Range("I4").Text
Final.Range("G5") = CatF.Range("H5").Text
Final.Range("H5") = CatF.Range("I5").Text
Final.Range("G6") = CatF.Range("H6").Text
Final.Range("H6") = CatF.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E8").Text Then
Final.Range("G2") = CatG.Range("H2").Text
Final.Range("H2") = CatG.Range("I2").Text
Final.Range("G3") = CatG.Range("H3").Text
Final.Range("H3") = CatG.Range("I3").Text
Final.Range("G4") = CatG.Range("H4").Text
Final.Range("H4") = CatG.Range("I4").Text
Final.Range("G5") = CatG.Range("H5").Text
Final.Range("H5") = CatG.Range("I5").Text
Final.Range("G6") = CatG.Range("H6").Text
Final.Range("H6") = CatG.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E9").Text Then
Final.Range("G2") = CatH.Range("H2").Text
Final.Range("H2") = CatH.Range("I2").Text
Final.Range("G3") = CatH.Range("H3").Text
Final.Range("H3") = CatH.Range("I3").Text
Final.Range("G4") = CatH.Range("H4").Text
Final.Range("H4") = CatH.Range("I4").Text
Final.Range("G5") = CatH.Range("H5").Text
Final.Range("H5") = CatH.Range("I5").Text
Final.Range("G6") = CatH.Range("H6").Text
Final.Range("H6") = CatH.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E10").Text Then
Final.Range("G2") = CatI.Range("H2").Text
Final.Range("H2") = CatI.Range("I2").Text
Final.Range("G3") = CatI.Range("H3").Text
Final.Range("H3") = CatI.Range("I3").Text
Final.Range("G4") = CatI.Range("H4").Text
Final.Range("H4") = CatI.Range("I4").Text
Final.Range("G5") = CatI.Range("H5").Text
Final.Range("H5") = CatI.Range("I5").Text
Final.Range("G6") = CatI.Range("H6").Text
Final.Range("H6") = CatI.Range("I6").Text
End If


If Final.Range("G1").Text = Topics.Range("E11").Text Then
Final.Range("G2") = CatJ.Range("H2").Text
Final.Range("H2") = CatJ.Range("I2").Text
Final.Range("G3") = CatJ.Range("H3").Text
Final.Range("H3") = CatJ.Range("I3").Text
Final.Range("G4") = CatJ.Range("H4").Text
Final.Range("H4") = CatJ.Range("I4").Text
Final.Range("G5") = CatJ.Range("H5").Text
Final.Range("H5") = CatJ.Range("I5").Text
Final.Range("G6") = CatJ.Range("H6").Text
Final.Range("H6") = CatJ.Range("I6").Text
End If


'///////////////////////////////////////////////////////////


If Final.Range("I1").Text = Topics.Range("E2").Text Then
Final.Range("I2") = CatA.Range("H2").Text
Final.Range("J2") = CatA.Range("I2").Text
Final.Range("I3") = CatA.Range("H3").Text
Final.Range("J3") = CatA.Range("I3").Text
Final.Range("I4") = CatA.Range("H4").Text
Final.Range("J4") = CatA.Range("I4").Text
Final.Range("I5") = CatA.Range("H5").Text
Final.Range("J5") = CatA.Range("I5").Text
Final.Range("I6") = CatA.Range("H6").Text
Final.Range("J6") = CatA.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E3").Text Then
Final.Range("I2") = CatB.Range("H2").Text
Final.Range("J2") = CatB.Range("I2").Text
Final.Range("I3") = CatB.Range("H3").Text
Final.Range("J3") = CatB.Range("I3").Text
Final.Range("I4") = CatB.Range("H4").Text
Final.Range("J4") = CatB.Range("I4").Text
Final.Range("I5") = CatB.Range("H5").Text
Final.Range("J5") = CatB.Range("I5").Text
Final.Range("I6") = CatB.Range("H6").Text
Final.Range("J6") = CatB.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E4").Text Then
Final.Range("I2") = CatC.Range("H2").Text
Final.Range("J2") = CatC.Range("I2").Text
Final.Range("I3") = CatC.Range("H3").Text
Final.Range("J3") = CatC.Range("I3").Text
Final.Range("I4") = CatC.Range("H4").Text
Final.Range("J4") = CatC.Range("I4").Text
Final.Range("I5") = CatC.Range("H5").Text
Final.Range("J5") = CatC.Range("I5").Text
Final.Range("I6") = CatC.Range("H6").Text
Final.Range("J6") = CatC.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E5").Text Then
Final.Range("I2") = CatD.Range("H2").Text
Final.Range("J2") = CatD.Range("I2").Text
Final.Range("I3") = CatD.Range("H3").Text
Final.Range("J3") = CatD.Range("I3").Text
Final.Range("I4") = CatD.Range("H4").Text
Final.Range("J4") = CatD.Range("I4").Text
Final.Range("I5") = CatD.Range("H5").Text
Final.Range("J5") = CatD.Range("I5").Text
Final.Range("I6") = CatD.Range("H6").Text
Final.Range("J6") = CatD.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E6").Text Then
Final.Range("I2") = CatE.Range("H2").Text
Final.Range("J2") = CatE.Range("I2").Text
Final.Range("I3") = CatE.Range("H3").Text
Final.Range("J3") = CatE.Range("I3").Text
Final.Range("I4") = CatE.Range("H4").Text
Final.Range("J4") = CatE.Range("I4").Text
Final.Range("I5") = CatE.Range("H5").Text
Final.Range("J5") = CatE.Range("I5").Text
Final.Range("I6") = CatE.Range("H6").Text
Final.Range("J6") = CatE.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E7").Text Then
Final.Range("I2") = CatF.Range("H2").Text
Final.Range("J2") = CatF.Range("I2").Text
Final.Range("I3") = CatF.Range("H3").Text
Final.Range("J3") = CatF.Range("I3").Text
Final.Range("I4") = CatF.Range("H4").Text
Final.Range("J4") = CatF.Range("I4").Text
Final.Range("I5") = CatF.Range("H5").Text
Final.Range("J5") = CatF.Range("I5").Text
Final.Range("I6") = CatF.Range("H6").Text
Final.Range("J6") = CatF.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E8").Text Then
Final.Range("I2") = CatG.Range("H2").Text
Final.Range("J2") = CatG.Range("I2").Text
Final.Range("I3") = CatG.Range("H3").Text
Final.Range("J3") = CatG.Range("I3").Text
Final.Range("I4") = CatG.Range("H4").Text
Final.Range("J4") = CatG.Range("I4").Text
Final.Range("I5") = CatG.Range("H5").Text
Final.Range("J5") = CatG.Range("I5").Text
Final.Range("I6") = CatG.Range("H6").Text
Final.Range("J6") = CatG.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E9").Text Then
Final.Range("I2") = CatH.Range("H2").Text
Final.Range("J2") = CatH.Range("I2").Text
Final.Range("I3") = CatH.Range("H3").Text
Final.Range("J3") = CatH.Range("I3").Text
Final.Range("I4") = CatH.Range("H4").Text
Final.Range("J4") = CatH.Range("I4").Text
Final.Range("I5") = CatH.Range("H5").Text
Final.Range("J5") = CatH.Range("I5").Text
Final.Range("I6") = CatH.Range("H6").Text
Final.Range("J6") = CatH.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E10").Text Then
Final.Range("I2") = CatI.Range("H2").Text
Final.Range("J2") = CatI.Range("I2").Text
Final.Range("I3") = CatI.Range("H3").Text
Final.Range("J3") = CatI.Range("I3").Text
Final.Range("I4") = CatI.Range("H4").Text
Final.Range("J4") = CatI.Range("I4").Text
Final.Range("I5") = CatI.Range("H5").Text
Final.Range("J5") = CatI.Range("I5").Text
Final.Range("I6") = CatI.Range("H6").Text
Final.Range("J6") = CatI.Range("I6").Text
End If


If Final.Range("I1").Text = Topics.Range("E11").Text Then
Final.Range("I2") = CatJ.Range("H2").Text
Final.Range("J2") = CatJ.Range("I2").Text
Final.Range("I3") = CatJ.Range("H3").Text
Final.Range("J3") = CatJ.Range("I3").Text
Final.Range("I4") = CatJ.Range("H4").Text
Final.Range("J4") = CatJ.Range("I4").Text
Final.Range("I5") = CatJ.Range("H5").Text
Final.Range("J5") = CatJ.Range("I5").Text
Final.Range("I6") = CatJ.Range("H6").Text
Final.Range("J6") = CatJ.Range("I6").Text
End If

Paul_Hossler
05-02-2019, 09:03 AM
Nice

Since the "Final' is fixed position and the Q&A positions are , I think you can take advantage of fomulas and parameters

There's some assumptions about sheet names below, but you can easily work around that

I did change the sheet layouts a bit, and really didn't do anything with the user form part, just the Final worksheeet



Option Explicit


Private Sub Randomize_Click()
Dim ws As Worksheet
Dim iCat As Long

'added "RN" to A1 in each as marker
For Each ws In ThisWorkbook.Worksheets
If VarType(ws.Cells(1, 1).Value) = vbString Then ' skip error
If ws.Cells(1, 1).Value = "RN" Then
Call Sort_Category(ws)
End If
End If
Next
For iCat = 1 To 9 Step 2
Call Select_QA(Final.Cells(1, iCat))
Next iCat
End Sub


Private Sub Start_Click()
Randomize_Click
StartPage.Show
End Sub




and



Option Explicit
Sub Sort_Category(ws As Worksheet)
Dim r As Range, r1 As Range, c As Range

With ws

Randomize

.Calculate

Set r = .Cells(1, 1).CurrentRegion
Set r1 = Range(r.Cells(2, 1), r.Cells(2, 1).End(xlDown))

For Each c In r1.Cells
c.Value = Rnd
Next

With .Sort
.SortFields.Clear
.SortFields.Add Key:=r1
.Header = xlYes
.SetRange r
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub


's = worksheet name
Sub Select_QA(r As Range)
Dim rFinal As Range, rCat As Range

Set rFinal = r.Offset(1, 0).Resize(5, 2)
Set rCat = Worksheets(r.Text).Range("B2:C6")

rCat.Copy rFinal
End Sub

daraymond
05-02-2019, 12:14 PM
Paul, that is exactly what i was trying to do but didn't know how, you are awesome. that also eliminates having to write a bunch of code for a new category every time you need to add a new one, I do see that my auto naming of the tabs has been "removed" but i understand why with the new function, so my follow up question would be if i manually renamed the tabs would that cause an issue? i dont think it would because your using the for each method, but im not 100% sure.

Paul_Hossler
05-02-2019, 06:29 PM
Public array to hold the new buttons class to allow each of the 25 to share the click event


Public aryQA(1 To 5, 1 To 5) As New clsCommandButton ' dollars as row, categories as columns





Loads the 25 button into the array




Private Sub StartButton_Click()
Dim c As Long, d As Long, i As Long
Load JHome
'only works because you have the controls named nice
For i = 0 To JHome.Controls.Count - 1
If JHome.Controls(i).Name Like "[A-E][1-5]00" Then
d = CLng(Right(JHome.Controls(i).Name, 3)) / 100 ' "300" = 300 / 100 = 3
c = Asc(Left(JHome.Controls(i).Name, 1)) - 64 ' "A" = 65, -64 = 1
Set aryQA(d, c).evtCommandButton = JHome.Controls(i)
End If
Next i

For d = 1 To 5
For c = 1 To 5
aryQA(d, c).evtCommandButton.Visible = True
aryQA(d, c).evtCommandButton.Enabled = True
Next c
Next d
JHome.FinalJeopardy.Visible = False
JHome.FinalJeopardy.Enabled = False
JHome.Show
Unload Me
End Sub






The new class to allow 25 buttons to share the same event code -- the DailyDouble doesn't work right since I didn't know



Option Explicit


Public WithEvents evtCommandButton As MSForms.CommandButton

Private Sub evtCommandButton_Click()
Dim c As Long, d As Long

c = Asc(Left(evtCommandButton.Name, 1)) - 64 ' "A" = 65, -64 = 1
d = CLng(Right(evtCommandButton.Name, 3)) / 100 ' "300" = 300 / 100 = 3
slide.Question = Final.Cells(d + 1, 2 * c - 1).Text
slide.Answer = Final.Cells(d + 1, 2 * c).Text

evtCommandButton.Visible = False
evtCommandButton.Enabled = False
If Final.Range("L1").Value = "1" Then
DailyDouble.Show
Else
slide.Show
End If
For d = 1 To 5
For c = 1 To 5
If aryQA(d, c).evtCommandButton.Visible Then Exit Sub
Next c
Next d
End Sub

daraymond
05-02-2019, 06:53 PM
The new class to allow 25 buttons to share the same event code -- the DailyDouble doesn't work right since I didn't know

I see what you did and i understand how it works (mostly) and it is extremely elegant, but it does get rid of the daily double, and the final jeopardy as well, not sure how to get those back.

again, thank you so much for your feedback and help. as im sure you have realized im still very new to this, my experience is 1 year of visual basics in high school (14 years ago) and google. what did you do to become so knowledgeable?

Paul_Hossler
05-02-2019, 07:38 PM
No problem

I didn't see the logic you had for the Daily Double but I'll look again

daraymond
05-02-2019, 07:42 PM
the logic was each button had an if statement and in individual number assigned to it and if the random number between 1 and 25 in L1 equaled that number then the daily double slide displayed first, else it went to just the slide. the final jeopardy was when all the main buttons were disabled that the final jeopardy button became available.

Paul_Hossler
05-02-2019, 07:57 PM
2 Public variables



Option Explicit
Public aryQA(1 To 5, 1 To 5) As New clsCommandButton ' dollars as row, categories as columns
Public DailyDoubleAmt As Long, DailyDoubleCat As Long




Initialized



Private Sub StartButton_Click()
Dim c As Long, d As Long, i As Long

DailyDoubleAmt = Int(5 * Rnd) + 1
DailyDoubleCat = Int(5 * Rnd) + 1






In the class



Private Sub evtCommandButton_Click()
Dim c As Long, d As Long

c = Asc(Left(evtCommandButton.Name, 1)) - 64 ' "A" = 65, -64 = 1
d = CLng(Right(evtCommandButton.Name, 3)) / 100 ' "300" = 300 / 100 = 3
slide.Question = Final.Cells(d + 1, 2 * c - 1).Text
slide.Answer = Final.Cells(d + 1, 2 * c).Text

evtCommandButton.Visible = False
evtCommandButton.Enabled = False
If d = DailyDoubleAmt And c = DailyDoubleCat Then
DailyDouble.Show
Else
slide.Show
End If




Play with this version - Double Jepordy and Final Jeporday seem to work

daraymond
05-02-2019, 09:07 PM
Paul,
Thank you so much, this has been a passion project for a long time, earlier versions have worked and are in use in several areas but this is much better, you've taken me from 1400 lines of code to 222. i did make one change in the class module.

From:

Load FinalCat
FinalCat.Cat.Caption = Final.Range("K2").Value
slide.Question.Caption = Final.Range("K3").Value
slide.Answer.Caption = Final.Range("K4").Value
FinalCat.Show

To:

JHome.FinalJeopardy.Visible = True
JHome.FinalJeopardy.Enabled = True

this shows the button that goes to final jeopardy instead of just automatically doing it. my intention is that the "host" has time to talk about points and prep everyone for the final round.

again, thank you so very much, your help has been invaluable, i would love to know what steps you took to become this knowledgeable.

Paul_Hossler
05-03-2019, 07:24 AM
i would love to know what steps you took to become this knowledgeable.

I was math major and liked solving problems

I was using computers before there were PCs and Macs (and TRS-80s and Commadores and ….)

Just interested in stuff like this

My 'First Language' as JOVIAL, sort of like ALGOL, but I got the Borland Pascal pCode complier for my PC (long time ago when 64K was considered a lot). I really liked Delphi, which my first exposure to events and OOP


My first VBA project was trying to write a user defined function to replace a long ugly worksheet formula that I couldn't get right


Many VBA tutorials online, written by people way more knowledgeable than me, some more technical that others

http://www.cpearson.com/excel.htm

http://www.aivosto.com/vbtips/stringopt.html#whyslow


I have a XLSM in my XLSTART where I like to my 'toolbox' of modules where I update or add new functions so that I can re-use them in other projects.


Mostly just years of reading and hanging out in forums


As you said about your very repetitive code, "There has to be an easier / better way" so it's just asking, learning, and searching