Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Can click event be called from different event?

    Hi Experts,

    I have a form with Option Buttons. The following code sets up the form to show controls for a multiple choice quiz question. The code for the Option Button is:

    Private Sub OptMC_Click()
    Txt1.Visible = True
    Txt2.Visible = True
    Txt3.Visible = True
    Txt4.Visible = True
    Txt5.Visible = True
    LblAnswer.Visible = Not OptMC
    LblCorrect.Visible = Not OptMC
    OptTrue.Visible = Not OptMC
    OptFalse.Visible = Not OptMC
    TxtCompletion.Visible = Not OptMC


    If OptMC.Value = "MC" Then
    UserForm1.OptMC = "mc"
    Else
    UserForm1.OptMC = ""
    End If


    End Sub


    Is it possible to call the above Click Event into the following Toggle Button Click Event:

    Private Sub TogNext_Click()


    Do


    ActiveCell.Offset(1, 0).Select


    If ActiveCell.EntireRow.Hidden = False Then 'This code is needed when a filtered is being used.
    Exit Do
    End If
    Loop


    If ActiveCell.Value = "" Then
    MsgBox "Last Row"
    ActiveCell.Offset(-1, 0).Select
    End If
    Call GetTextboxData


    If ActiveCell.Offset(, -4) = MC Then


    End If
    End Sub

    Note: both of the above codes are working in the application. I'm hoping to be able to call "click events" to change the form controls for True and False Question, Multiple Choice questions, and etc.

    This question may not make sense to experts. If so, please forgive me.

    Thanks in advance for any help.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    It may be possible if they are both in the same Form. However, I would suggest that a better option would be to create a Regular Sub routine in the form code and then call that from both of the Click events. This will help to make the code clearer as it will be easy to see the code is used by two events and you only have one place to maintain code. IMHO it is NOT a good idea to call events from other events as it leads to confusion as to what triggered the code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-01-15)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-Thanks. I followed your approach and everything works.

    New question. I want the call to be based on an if statement.

    In column B I have letters (MC (Multiple Choice question), T or F (True of False question).

    When I use the toggle button and click down the sheet I want to use an if state to change the controls on the form based on the kind of question, that is, a MC question will have 5 textboxes visible, while a Tor F question will have two textboxes visible.

    I've working with:

    If Sheets("sheet1").Range("B:B").Value = MC "" Then
    Call OptMC

    If Range("B2:B100").Value = "MC" Then
    Call MCOption

    So far neither of these approaches is working.
    Last edited by Excelnewbie; 2016-01-14 at 18:22.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    You can't compare a single value to a range, which you are trying to do in your examples.

    If you are moving the cursor down the questions you can use:
    Code:
    If (Cells(Selection.Row(), 2).Value = "MC") Then
      Debug.Print "True"
    Else
      Debug.Print "False"
    End If
    With complex operations like this a sample workbook would be most useful!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-01-15)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    You can use the match function to compare a single value to a range like so:

    Code:
    On Error Resume Next
    Z = WorksheetFunction.Match("mc", Range("B1:B100"), 0)
    If Z <> "" Then
         MsgBox "Match found"
    Else:
        MsgBox "Match not found"
    End If
    On Error GoTo 0
    HTH,
    Maud

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-01-15)

  9. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    You are correct however the OP is trying to match the value in a single cell in column B to determine what to display, so finding the value anywhere in the column won't work.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-01-15)

  11. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud and RG,

    I sure appreciate your help. My understanding of VBA is growing, however, I am still very much an exelnewbie.

    I've attached a file I have been learning with. I think of it as a Frankenstein file because I have searched google and picked up a piece of code here and there and put together what might be a monster. I hope you will be patient with what you see.

    I am at a dead end for a few things. I hope you can help with the following:

    1. I have three types of questions I use in this file , 1. True and False, 2. Completion, and 3. Multiple Choice. I also have ALL. All means the user doesn't select a type of question, he just toggles down all of the questions. The problem here is that the form needs to change with each type of question. I hope you can help with code for the All Option.

    2. When the user chooses an answer the word Correct or Incorrect appears. Choose Option, True and False to see this.
    I hope you can help with code for what is needed here.

    Lastly, if you have any ideas how to improve existing code that is welcome, and if you see a better approach for a quiz application please let me know.

    Once again, thanks for all you do.

    If it turns out you don't want to work with the monster I've created, I'll understand.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2016-01-15 at 17:44.

  12. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    This is quite an ambitious project for a starter.

    I would suggest rather than charging ahead you step back and do a little more planning and I think you'll find things will work out much better.

    A few things to think about:

    1. What is going to happen when you select the All option? You don't want to have to duplicate code that you've already done for the MC, T/F, and C questions. So maybe you need to tackle what happens with the Previous/Next buttons first.
    2. Maybe you need a few Module Level variables to store the state of choices so they can be checked in any procedure.
    3. Maybe using Filters isn't a good idea as it can make looping through the questions more difficult.
    4. Maybe instead of using Text Boxes for the questions and MC answers labels would be better.
    5. Also a logical naming convention for your form objects will make the coding much easier to initially code and revise later.


    There are other considerations but this should give you some food for thought. Planning up front will save you uncounted hours on the back end and most likely make your code more logical and easy to understand and follow.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Here's some code to get you started which answers some of the questions above. Note this is not finished code but it will allow you to select a type of question, loop through viewing the questions. The Prev/Next buttons work. However, you still need a method for determining when the last question has been processed (some fake questions at the end with a field to test would do the trick).

    You'll notice that I've tried to avoid repetition of code, i.e. do it in one place not many.

    Code:
    Option Explicit
    
    '*** Module Level Variables ***
    
    '***  iQType Values ***
    '*** 0 = Not Set
    '*** 1 = All
    '*** 2 = T/F
    '*** 3 = Multiple Choice
    '*** 4 = Completion
    
    Dim iQType    As Integer
    Dim zQStr     As String   '*** Question type from worksheet to search for next question ***
    Dim iNextPrev As Integer   '** Direction to search for next question xlNext = 1, xlPrevious = 2 ***
    
    
    Private Sub UserForm_Initialize()
    
         With Me
             .txtQuestion.Visible = False
             .Txt2.Visible = False
             .Txt3.Visible = False
             .Txt4.Visible = False
             .Txt5.Visible = False
             .TxtCompletion.Visible = False
             .LblAnswer.Visible = False
             .OptTrue.Visible = False
             .OptFalse.Visible = False
             .LblCorrect.Visible = False
             .OptA.Visible = False
             .OptB.Visible = False
             .OptC.Visible = False
             .OptD.Visible = False
         End With
    
         iQType = 0
         Cells(1, 2).Select  'B1
         iNextPrev = xlNext
         
    End Sub    'UserForm_Initialize
    
    Private Sub TogPrev_Click()
    
       iNextPrev = xlPrevious
       
       Display_Question
        
    End Sub   'TogPrev_Click
    
    Private Sub TogNext_Click()
    
       iNextPrev = xlNext
       
       Display_Question
    
    End Sub  'TogNext_Click
    
    Private Sub CmdClose_Click()
    
       Unload Me
       
    End Sub     'CmdClose_Click
    
    '------------- Question Type Buttons  --------------------
    
    Private Sub OptAll_Click()
    
        iQType = 1
        [B1].Select
        Display_Question
        
    End Sub
    
    
    Private Sub OptTorF_Click()
    
        iQType = 2
        zQStr = "T or F"
        [B1].Select
        Display_Question
        
    End Sub   'OptTorF_Click
    
    Private Sub OptMC_Click()
    
        iQType = 3
        zQStr = "MC"
        [B1].Select
        Display_Question
        
    
    End Sub    'OptMC_Click
    
    Private Sub OptCompletion_Click()
    
        iQType = 4
        zQStr = "C"
        [B1].Select
        Display_Question
            
    End Sub  'OptCompletion_Click
    
    Private Sub Display_Question()
    
        Dim bMCVisible   As Boolean
        Dim bTFVisible   As Boolean
        Dim bCompVisible As Boolean
         
        bMCVisible = False
        bTFVisible = False
        bCompVisible = False
        
        Select Case iQType
              Case 1
              Case 2
                     bTFVisible = True
              Case 3
                     bMCVisible = True
              Case 4
                     bCompVisible = True
        End Select     ' iQType
        
        If iQType > 1 Then
           Columns("B:B").Find(What:=zQStr, After:=ActiveCell, _
                                  LookIn:=xlFormulas, _
                                  LookAt:=xlWhole, _
                                  SearchOrder:=xlByRows, _
                                  SearchDirection:=iNextPrev, _
                                  MatchCase:=False, _
                                  SearchFormat:=False).Select
        Else
          ActiveCell.Offset(IIf(iNextPrev = 1, 1, -1), 0).Select
        End If
        
        With txtQuestion
            .Visible = True
            .Text = ActiveCell.Offset(0, 4).Value
        End With
        
        Txt2.Visible = bMCVisible
        Txt3.Visible = bMCVisible
        Txt4.Visible = bMCVisible
        Txt5.Visible = bMCVisible
        OptA.Visible = bMCVisible
        OptB.Visible = bMCVisible
        OptC.Visible = bMCVisible
        OptD.Visible = bMCVisible
    
        OptTrue.Visible = bTFVisible
        OptFalse.Visible = bTFVisible
        LblAnswer.Visible = bCompVisible
        TxtCompletion.Visible = bCompVisible
        LblCorrect.Visible = bCompVisible
       
    
    End Sub    'Display_Question
    
    '*****************************  END of RG's Code *************************************
    My Test File: Rgs Version of WS6.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Ok here's most of the problem you should be able to finish the rest.

    Things to check out:
    1. The use of the AfterUpdate event for the Completion questions. If you use the change event the Incorrect indicator will show as the user types!
    2. The use of .SetFocus to move the focus to the Next button after question is answered.
    3. Locking of static text (questions and option answers) so user can't change.
    4. Coloring of static text background & Correct/Incorrect flags.
    5. Renaming of form objects to be more descriptive.


    Version2: Rgs Version 2 of WS6.xlsm

    BTW: You'll notice some major code changes as I didn't follow my own advice and plan well enough!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-01-19)

  16. #11
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG- Wow! You put a lot of effort into this. Thank you!

    I'm going through the code and learning all I can. It will take awhile.

    Thanks again for taking the time to give me a leg up as I study and learn Excel VBA.

  17. #12
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-I've been working on the file you so kindly provided. I have a question about code in that file.

    What do the following lines of code provide or do?


    If OptAll Then
    ActiveCell.Offset(IIf(iNextPrev = 1, 1, -1), 0).Select

    If (iNextPrev = 1 And ActiveCell.Value = "") Or _
    (iNextPrev = 2 And ActiveCell.Value = "Question")

    Thanks for everything.

  18. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,
    Code:
    If OptAll Then
    ActiveCell.Offset(IIf(iNextPrev = 1, 1, -1), 0).Select
    If OptAll Then e.g. all questions button pressed then

    IIf(iNextPrev = 1, 1, -1) determine if Next or Previous button (as set by the TogNext/TogPrev_Click() event code) was pressed and determine which way to move for the next question to be displayed: [Next = 1 = Down] [Previous = -1 = UP]. This is an immediate if (IIF) so it is evaluated and the result filters into the Offset( x, 0).select to move the cursor to the next question to be displayed.

    Code:
    If (iNextPrev = 1 And ActiveCell.Value = "") Or _
    (iNextPrev = 2 And ActiveCell.Value = "Question")
    This code detects the limits of the questions depending on the direction of movement from the last question, e.g. if the Next button was pressed and the active cell is blank then you're past the last question in the list. If the Previous button was pushed and the active cell = "Question" you've moved previous to the first question.

    HTH
    Last edited by RetiredGeek; 2016-01-21 at 14:39.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  19. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-01-23)

  20. #14
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-Thanks for the explanation.

    If OptAll Then
    ActiveCell.Offset(IIf(iNextPrev = 1, 1, -1), 0).Select

    In my words, If iNextPrev =1, is true,then go down 1 row(1), if INextPrev doesn't equal 1, then go up 1 row (-1).

    I hope I got that right.

    Question: what is the 0 about?

    I hope I don't wear you out with questions. Thanks for all you do.

  21. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    ActiveCell.Offset(Row,Column)

    The Row is taken care of by the IIF
    Since we don't want to change the column we use 0.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  22. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-01-23)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •