Excel Jeopardy with randomly selected topics and questions.

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.

.
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.

Quote:

---

Originally Posted by Logit

.
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.

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?

Code:

---

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

---

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

Code:

---

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

Code:

---

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


---

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.

Public array to hold the new buttons class to allow each of the 25 to share the click event

Code:

---

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

---

Loads the 25 button into the array

Code:

---

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

Code:

---

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

---

Quote:

---

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?

No problem

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

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.

2 Public variables

Code:

---

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

Code:

---

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

Code:

---

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

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:

Code:

---

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:

Code:

---

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.

Quote:

---

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