+ Reply to Thread
Results 1 to 9 of 9

VBA Userform Issue - Command Button / msg Box

Hybrid View

  1. #1
    Registered User
    Join Date
    01-07-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: VBA Userform Issue - Command Button / msg Box

    I'm getting in a right pickle with this now. I thought i'd post the full messy code. I'm getting the same problem again that the message box keeps reappearing. Once a MsgBox "Please select..." appears I want to return to the user form. Bitten off far more than I can chew, thanks for the help so far Andy!!

    Function ItemIsSelected(Lst As MSForms.ListBox) As Boolean
    Dim IngIndex As Long
    For IndIndex = 0 To Lst.ListCount - 1
        If Lst.Selected(IngIndex) Then
        ItemIsSelected = True
        Exit Function
        End If
        Next
    End Function
    
    Private Sub CommandButton1_Click()
    
    MsgBox "The Analyser needs to calculate results based on the selected criteria. This may take a few minutes. The information bar at the bottom of the worksheet will show the calculation progress. Please be patient!" _
    , vbInformation + vbOKOnly, "View Results by Selections"
    
    
    If ItemIsSelected(ListBox1) Then
         
        Sheet4.Range("A65536").End(xlUp)(2, 1) = ListBox1.List(lItem)
    
    Else
    MsgBox "Please select a question", vbExclamation
    Exit Sub
    End If
     
    If ItemIsSelected(ListBox2) Then
    
                Sheet4.Range("b65536").End(xlUp)(2, 1) = ListBox2.List(mItem)
    Else
    
    MsgBox "Please select one or more Ward", vbExclamation
    Exit Sub
    End If
    
    
    
    If ItemIsSelected(ListBox3) Then
    
                Sheet4.Range("c65536").End(xlUp)(2, 1) = ListBox3.List(nItem)
    Else
    
    MsgBox "Please select one or more Age Band", vbExclamation
    Exit Sub
    End If
    
    
    
    If ItemIsSelected(ListBox4) Then
    
                Sheet4.Range("d65536").End(xlUp)(2, 1) = ListBox4.List(oItem)
    Else
    
    MsgBox "Please select one or more Ethnicity", vbExclamation
    Exit Sub
    End If
    
    
    
    If ItemIsSelected(ListBox5) Then
    
                Sheet4.Range("e65536").End(xlUp)(2, 1) = ListBox5.List(pItem)
    Else
    
    MsgBox "Please select one or more Gender", vbExclamation
    Exit Sub
    End If
    
    Sheets("weighted2").Select
    Range("a33:fh9999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fc1:fc3"), Copytorange:=Range("A10000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a10000:fh19999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fd1:fd9"), Copytorange:=Range("A20000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a20000:fh29999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fe1:fe4"), Copytorange:=Range("A30000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a30000:fh39999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("ff1:ff25"), Copytorange:=Range("A40000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a40000:fh49999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fb1:fb3"), Copytorange:=Range("A50000"), Unique:=False
    
    Sheets("Survey Analysis").Select
    ActiveWindow.SmallScroll Down:=33
    Range("A47").Select
    
    Range("A42").Select
        Selection.Copy
        Cells.find(What:=ActiveCell, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
            .Activate
            
    Survey.Hide
    
    End Sub

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: VBA Userform Issue - Command Button / msg Box

    I can not see a reason for getting msgboxes other than if no items are selected for a particular listbox.

    That said I also don't see in your code where the specific List items is obtained.

    So for example if Listbox1 has the last item selected your code will output what exactly? What is lItem

    Private Sub CommandButton1_Click()
    
    MsgBox "The Analyser needs to calculate results based on the selected criteria. This may take a few minutes. The information bar at the bottom of the worksheet will show the calculation progress. Please be patient!" _
    , vbInformation + vbOKOnly, "View Results by Selections"
    
    
    If ItemIsSelected(ListBox1) Then
         
        Sheet4.Range("A65536").End(xlUp)(2, 1) = ListBox1.List(lItem)
    
    Else
        MsgBox "Please select a question", vbExclamation
        Exit Sub
    End If
    
    ' cut rest of code
    
    End sub
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    01-07-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: VBA Userform Issue - Command Button / msg Box

    (litem) was a left over from the original code.

    I've now got the following code, based on your advice, it tells me ethnicity (listbox4) is not selected even when a option from the list box is Arrrghhhh!! Surprise, surprise, I can't understand why!

    Any thoughts. Thanks for all your help so far!

    Function ItemIsSelected(Lst As MSForms.ListBox) As Boolean
    Dim IngIndex As Long
    For IndIndex = 0 To Lst.ListCount - 1
        If Lst.Selected(IngIndex) Then
        ItemIsSelected = True
        Exit Function
        End If
        Next
    End Function
    
    Private Sub CommandButton1_Click()
    
    MsgBox "The Analyser needs to calculate results based on the selected criteria. This may take a few minutes. The information bar at the bottom of the worksheet will show the calculation progress. Please be patient!" _
    , vbInformation + vbOKOnly, "View Results by Selections"
    
        If Not ItemIsSelected(ListBox1) Then
            MsgBox "No Question selected"
        ElseIf Not ItemIsSelected(ListBox2) Then
            MsgBox "No Ward selected"
        ElseIf Not ItemIsSelected(ListBox3) Then
            MsgBox "No Age Band selected"
        ElseIf Not ItemIsSelected(ListBox4) Then
            MsgBox "No Ethnicity selected"
        ElseIf Not ItemIsSelected(ListBox2) Then
            MsgBox "No Gender selected"
        Else
            MsgBox "Output Selected Items"
        End If
    
    End Sub
    
    Private Sub Survey_Initialize()
    
    ListBox1.List = Sheet4.Range("a65536").End(xlUp)(2, 1)
    ListBox2.List = Sheet4.Range("b65536").End(xlUp)(2, 1)
    ListBox3.List = Sheet4.Range("c65536").End(xlUp)(2, 1)
    ListBox4.List = Sheet4.Range("d65536").End(xlUp)(2, 1)
    ListBox5.List = Sheet4.Range("e65536").End(xlUp)(2, 1)
    
    Sheets("weighted2").Select
    Range("a33:fh9999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fc1:fc3"), Copytorange:=Range("A10000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a10000:fh19999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fd1:fd9"), Copytorange:=Range("A20000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a20000:fh29999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fe1:fe4"), Copytorange:=Range("A30000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a30000:fh39999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("ff1:ff25"), Copytorange:=Range("A40000"), Unique:=False
    
    Sheets("weighted2").Select
    Range("a40000:fh49999").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("fb1:fb3"), Copytorange:=Range("A50000"), Unique:=False
    
    Sheets("Survey Analysis").Select
    ActiveWindow.SmallScroll Down:=33
    Range("A47").Select
    
    Range("A42").Select
        Selection.Copy
        Cells.find(What:=ActiveCell, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
            .Activate
            
    Survey.Hide
    
    End Sub

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: VBA Userform Issue - Command Button / msg Box

    Hard to say without actually having the workbook.

    Sure listbox4 is what you think it is?
    Maybe the selected item is not visible, are all items visible or can you scroll the list?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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