• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

How to get a UserForms CommandButton to carry out multiple operations in separated steps

Hi all,
I am fairly new to the VBA world and I have a problem developing a simple puzzle game for a project. I created a userform and a commandbutton which I want to use to enter the right solutions into the predefined cells. What I want to do is to punch OK when the answer is correct, write the solutions into cells (this part works) and update the label caption with new question each time the previous answer is correct. Everything would work fine however VBA doesn`t allow me to carry out multiple clicks on commandbutton in order to separate each operation meaning I would like to use one UserForm for all 9 of my questions separately punching in the correct answers step by step. VBA runs the whole thing at one time instead and I have become clueless no matter what I do.
Anyone could help on this please? I know its none of your business but it is kinda urgentJ
Thanks and appreciate any help in advance  

0
lejohney
Asked:
lejohney
  • 4
  • 3
1 Solution
 
ExcelGuideConsultantCommented:
maybe you could post an example?
0
 
lejohneyAuthor Commented:
Well as for right now it's a mess...but hope this helps



Private Sub UserForm_Activate()
        If [NbEssaisAnticipe] = [NbEssaisReel] Then
       
        MsgBox ("Vous avez malheuresement depase votre estimate de " & [NbEssaisAnticipe] & Chr(10) & _
    " mais vous pouvez quand meme continuer :)")
       
        End If

'Question Initiale Affichage
       
         Question1.Caption = [QuestionN1] & [CountAnswer2] & " LETTRES"
       
       
         
End Sub
           
Private Sub OKButton_Click()

           'Question 1
             If AnswerInput.Text = [Answer1Solution] Then
            Lg = LastRow(Sheets("Quiz")) + 1
            Set Check = Sheets("Check").Cells([A1].Row, 1).Range("D13:S13")
            With Check
            Set Quiz = Sheets("Quiz").Range("D" & Lg).Resize(.Rows.Count, .Columns.Count)
            End With
            Quiz.Value = Check.Value
            [Answer1Caption] = AnswerInput.Text
            AnswerInput.Text = ""
            MsgBox ("Bien joué")
            Else
            MsgBox ("Reessayer")
            End If
           
            'Question 2
           
            Question1.Caption = [QuestionN2] & " " & [CountAnswer3] & " LETTRES"
            For Question = Question + 1 To Question + 2
            If AnswerInput.Text = [Ans2S] Then
            Lg = LastRow(Sheets("Quiz")) + 1
            Set Check = Sheets("Check").Cells([B2].Row, 1).Range("D13:S13")
            With Check
            Set Quiz = Sheets("Quiz").Range("D" & Lg).Resize(.Rows.Count, .Columns.Count)
            End With
            Quiz.Value = Check.Value
            [Answer2Caption] = AnswerInput.Text
            AnswerInput.Text = ""
            MsgBox ("Bien joué")
            Else
            MsgBox ("Reessayer")
            End If
            Next
                       
            'Question 3
           
            For Question = Question + 2 To Question = Question + 3
            Question1.Caption = [QuestionN3] & " " & [CountAnswer4] & " LETTRES"
            [Answer1Caption] = AnswerInput.Text
            If AnswerInput.Text = [Answer1Solution] Then
            Range("B8").Select
            Lg = LastRow(Sheets("Quiz")) + 1
            Set Check = Sheets("Check").Cells([C3].Row, 1).Range("D13:S13")
            With Check
            Set Quiz = Sheets("Quiz").Range("D" & Lg).Resize(.Rows.Count, .Columns.Count)
            End With
            Quiz.Value = Check.Value
            MsgBox ("Bien joué")
            Else
            MsgBox ("Reessayer")
            End If
            Next
0
 
lejohneyAuthor Commented:
maybe to precise it a bit more...I have a check sheet where I store all my solutions (in individual cells - the mirror of the puzzle in Interface Sheet). Then I have cells where I have the solutions in form of words to which I refer when I write the answer in the UserForm TextBox. What I want to do is that when the answer in the text box is correct, it'll go find the range for solution in the Check sheet and paste it in the Interface poping up a message "Good job". And there is 9 questions of this sort that I want to do with only one userform, placing the solutions one below each other to form a column with solution in yellow...nothing fancy I know but I have just begun getting into it...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ExcelGuideConsultantCommented:
I think I know the solution but it would be great if you could post an example of the file.....you can get rid of all important things and leave something I can work with.
0
 
lejohneyAuthor Commented:
Here is the file...thanks for all your help
File-Example.xls
0
 
ExcelGuideConsultantCommented:
ok, actually nothing is working for me in this file. Anyway, I still came up with an solution if I understood you completely.

Could you build up your code in this way and test it and reply with your results?(see code snippet)

What I did:
In sheet "Check" in cell "W1" I entered "Finished" and right next to this cell you will find a number. This number tells the code which question the game is. So if you run the code like in de code snippet, this cell will begin with the value 1. Then the code goes as followed:
When X1 = 1, then run question 1. If question is not good, X1 = 1 and exit sub. If question is good, X1 = 2 and exit sub. Press OK again and he will go to the second question, because the second question will start if X1 = 2. You should continue this till question nr 9. I could not do this becuase your code is not finished yet.

I hope this is what you are looking for.

Good luck
Private Sub OKButton_Click()
Sheets("Check").Range("X1").Value = 1
 
If Sheets("Check").Range("X1").Value = 1 Then
           'Question 1
             If AnswerInput.Text = [Answer1Solution] Then
            Lg = LastRow(Sheets("Quiz")) + 1
            Set Check = Sheets("Check").Cells([A1].Row, 1).Range("D13:S13")
            With Check
            Set Quiz = Sheets("Quiz").Range("D" & Lg).Resize(.Rows.Count, .Columns.Count)
            End With
            Quiz.Value = Check.Value
            [Answer1Caption] = AnswerInput.Text
            AnswerInput.Text = ""
            MsgBox ("Bien joué")
            Sheets("Check").Range("X1").Value = 2
            Exit Sub
            Else
            MsgBox ("Reessayer")
            Sheets("Check").Range("X1").Value = 1
            Exit Sub
            End If
            
ElseIf Sheets("Check").Range("X1").Value = 2 Then
            'Question 2
            
            Question1.Caption = [QuestionN2] & " " & [CountAnswer3] & " LETTRES"
            For Question = Question + 1 To Question + 2
            If AnswerInput.Text = [Ans2S] Then
            Lg = LastRow(Sheets("Quiz")) + 1
            Set Check = Sheets("Check").Cells([B2].Row, 1).Range("D13:S13")
            With Check
            Set Quiz = Sheets("Quiz").Range("D" & Lg).Resize(.Rows.Count, .Columns.Count)
            End With
            Quiz.Value = Check.Value
            [Answer2Caption] = AnswerInput.Text
            AnswerInput.Text = ""
            MsgBox ("Bien joué")
            Sheets("Check").Range("X1").Value = 3
            Exit Sub
            Else
            MsgBox ("Reessayer")
            Sheets("Check").Range("X1").Value = 2
            Exit Sub
            End If
            Next
                        
ElseIf Sheets("Check").Range("X1").Value = 3 Then
            'Question 3
            
            For Question = Question + 2 To Question = Question + 3
            Question1.Caption = [QuestionN3] & " " & [CountAnswer4] & " LETTRES"
            [Answer1Caption] = AnswerInput.Text
            If AnswerInput.Text = [Answer1Solution] Then
            Range("B8").Select
            Lg = LastRow(Sheets("Quiz")) + 1
            Set Check = Sheets("Check").Cells([C3].Row, 1).Range("D13:S13")
            With Check
            Set Quiz = Sheets("Quiz").Range("D" & Lg).Resize(.Rows.Count, .Columns.Count)
            End With
            Quiz.Value = Check.Value
            MsgBox ("Bien joué")
            Sheets("Check").Range("X1").Value = 4
            Exit Sub
            Else
            MsgBox ("Reessayer")
            Sheets("Check").Range("X1").Value = 3
            Exit Sub
            End If
            Next

Open in new window

0
 
lejohneyAuthor Commented:
Thanks, awesome! I changed a few things and now it works just fine. Had to do some heavy shifting over the data field but it's the minimum. Now I feel I am kinda getting in the logic of VBA. Thanks again
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now