Solved

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

Posted on 2008-10-16
7
155 Views
Last Modified: 2011-10-19
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
Comment
Question by:lejohney
  • 4
  • 3
7 Comments
 
LVL 17

Expert Comment

by:ExcelGuide
ID: 22729152
maybe you could post an example?
0
 

Author Comment

by:lejohney
ID: 22729192
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
 

Author Comment

by:lejohney
ID: 22729229
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 17

Expert Comment

by:ExcelGuide
ID: 22729257
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
 

Author Comment

by:lejohney
ID: 22729298
Here is the file...thanks for all your help
File-Example.xls
0
 
LVL 17

Accepted Solution

by:
ExcelGuide earned 500 total points
ID: 22729749
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
 

Author Closing Comment

by:lejohney
ID: 31506647
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now