Consulting

Results 1 to 13 of 13

Thread: Excel Jeopardy with randomly selected topics and questions.

  1. #1

    Lightbulb 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.
    Attached Files Attached Files

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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.

  3. #3
    Quote Originally Posted by Logit View Post
    .
    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.

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

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    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?

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    No problem

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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    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.
    Last edited by daraymond; 05-02-2019 at 09:25 PM.

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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

    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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