Solved

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

Posted on 2008-10-16
7
159 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

808 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