Solved

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

Posted on 2008-10-16
7
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…

738 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