Solved

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

Posted on 2008-10-16
7
156 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

861 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

25 Experts available now in Live!

Get 1:1 Help Now