+ Reply to Thread
Results 1 to 4 of 4

Run code based on list selected

Hybrid View

majime01 Run code based on list... 11-04-2015, 02:51 AM
Kaper Re: Run code based on list... 11-04-2015, 04:36 AM
majime01 Re: Run code based on list... 11-04-2015, 06:17 AM
Kaper Re: Run code based on list... 11-05-2015, 01:57 PM
  1. #1
    Forum Contributor
    Join Date
    09-12-2015
    Location
    Okinawa, Japan
    MS-Off Ver
    2013
    Posts
    178

    Run code based on list selected

    I have a bit of code tied into a form. On the form are 2 lists. I want the user to pick from either of the two lists and when they hit click the ENTER button, one of the two sections of code will run based on which list they selected. I can't seem to figure out how to make it run one part and not the other. I get both MSGBOXs one after the other. Your help/advice/input is greatly appreciated.
    Ken

    Private Sub Enter_Click()
    
            
            'check to see if positive behavior list selected
            If Positive_Behaviors_List.Text <> "" Then
            Sheets("Multiples").Range("k6") = Positive_Behaviors_List.Text
            End If
            
            x = Sheets("Multiples").Range("k6")
            For Each ws In Sheets(Array("Student 01", "Student 02", "Student 03", "Student 04", "Student 05", "Student 06", "Student 07", "Student 08", "Student 09", "Student 10", "Student 11", "Student 12", "Student 13", "Student 14", "Student 15", "Student 16", "Student 17", "Student 18", "Student 19", "Student 20", "Student 21", "Student 22", "Student 23", "Student 24", "Student 25", "Student 26", "Student 27", "Student 28", "Student 29", "Student 30"))
            
            If Sheets("TableAssignments").Range("T" & Right(ws.Name, 2)).Value <> False Then
            ws.Range("B" & Rows.Count).End(3)(2) = x
            End If
            
    Next ws
              
            Beep
            MsgBox "You Earned Points"
            Unload Me
              
            'if not then run the following
            If Negative_Behaviors_List.Text <> "" Then
            Sheets("Multiples").Range("k6") = Negative_Behaviors_List.Text
            End If
            
            x = Sheets("Multiples").Range("k6")
            For Each ws In Sheets(Array("Student 01", "Student 02", "Student 03", "Student 04", "Student 05", "Student 06", "Student 07", "Student 08", "Student 09", "Student 10", "Student 11", "Student 12", "Student 13", "Student 14", "Student 15", "Student 16", "Student 17", "Student 18", "Student 19", "Student 20", "Student 21", "Student 22", "Student 23", "Student 24", "Student 25", "Student 26", "Student 27", "Student 28", "Student 29", "Student 30"))
            
            If Sheets("TableAssignments").Range("T" & Right(ws.Name, 2)).Value <> False Then
            ws.Range("B" & Rows.Count).End(3)(2) = x
            End If
            
    Next ws
            Beep
            MsgBox "Make a Better Choice"
            Unload Me
              
     End Sub

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,864

    Re: Run code based on list selected

    Not seeing the form is not that obvious but probably:

    Private Sub Enter_Click()
    
    'check to see if positive behavior list selected
    If Positive_Behaviors_List.Text <> "" Then
      Sheets("Multiples").Range("k6") = Positive_Behaviors_List.Text
      x = Sheets("Multiples").Range("k6")
      For Each ws In Sheets(Array("Student 01", "Student 02", "Student 03", "Student 04", "Student 05", "Student 06", "Student 07", "Student 08", "Student 09", "Student 10", "Student 11", "Student 12", "Student 13", "Student 14", "Student 15", "Student 16", "Student 17", "Student 18", "Student 19", "Student 20", "Student 21", "Student 22", "Student 23", "Student 24", "Student 25", "Student 26", "Student 27", "Student 28", "Student 29", "Student 30"))
       If Sheets("TableAssignments").Range("T" & Right(ws.Name, 2)).Value <> False Then
         ws.Range("B" & Rows.Count).End(3)(2) = x
       End If
      Next ws
      Beep
      MsgBox "You Earned Points"
      Unload Me
    'moved end if down until here
    End If
              
            'if not then run the following
            If Negative_Behaviors_List.Text <> "" Then
            Sheets("Multiples").Range("k6") = Negative_Behaviors_List.Text
            
            x = Sheets("Multiples").Range("k6")
            For Each ws In Sheets(Array("Student 01", "Student 02", "Student 03", "Student 04", "Student 05", "Student 06", "Student 07", "Student 08", "Student 09", "Student 10", "Student 11", "Student 12", "Student 13", "Student 14", "Student 15", "Student 16", "Student 17", "Student 18", "Student 19", "Student 20", "Student 21", "Student 22", "Student 23", "Student 24", "Student 25", "Student 26", "Student 27", "Student 28", "Student 29", "Student 30"))
            
            If Sheets("TableAssignments").Range("T" & Right(ws.Name, 2)).Value <> False Then
            ws.Range("B" & Rows.Count).End(3)(2) = x
            End If
            
    Next ws
            Beep
            MsgBox "Make a Better Choice"
            Unload Me
    'moved second end if here
    End If
              
     End Sub
    Last edited by Kaper; 11-04-2015 at 04:39 AM. Reason: edited indents in first part of code - using them makes control flow easier to follow
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    09-12-2015
    Location
    Okinawa, Japan
    MS-Off Ver
    2013
    Posts
    178

    Re: Run code based on list selected

    solid fix, thank you much!
    ken

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,864

    Re: Run code based on list selected

    Glad to hear that and thanks for marking thread solved and for reputation :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Don't run code based on selected cell
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2014, 07:12 AM
  2. VBA Code To Transfer Selected Data To List / Manifest
    By Rosco88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2014, 06:09 AM
  3. Excel Code run when combox list item is selected.
    By cllaguno in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-15-2013, 07:58 AM
  4. [SOLVED] Creating a grocery list in a new worksheet based on selected items in a master list
    By jacolli4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2012, 07:53 AM
  5. Code to Open A list of Path and copy a selected cell
    By roipatrick in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-21-2010, 02:40 PM
  6. Excel 2007 : creating a list based off selected cells
    By jon@karhu in forum Excel General
    Replies: 33
    Last Post: 11-30-2010, 05:51 PM
  7. populate list based on two selected conditions
    By simpo067 in forum Excel General
    Replies: 7
    Last Post: 04-06-2009, 05:01 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1