Letting a user click on a listbox after a mutlipage has been selected 1

[clouddog9]

I have a userform that has three multipage tabs with one listbox per page (also an OK button and a cancel button). I want to let the user click on the one of the multipage tabs and then choose an item from listbox. What actually happens is when I choose the multipage it calls the listbox but doesn't allow the user to choose a listbox item before finishing the execution. What do I need to do is allow the form to wait until the user chooses an item from the listbox before the finishing the execution. The Code is below. Any help would be greatly appreciated.



Private Sub CancelButton_Click()
strDCodePathFolder = ""
Me.Tag = 1
Me.Hide
End Sub



Private Sub ListDCodeXYZ_Click()
Select Case ListDCodeXYZ.ListIndex
Case 0: strDCodePathFolder = Sheets("DCode Pivot").Range("A5")
Case 1: strDCodePathFolder = Sheets("DCode Pivot").Range("A6")
Case 2: strDCodePathFolder = Sheets("DCode Pivot").Range("A7")
Case 3: strDCodePathFolder = Sheets("DCode Pivot").Range("A8")
Case 4: strDCodePathFolder = Sheets("DCode Pivot").Range("A9")
Case 5: strDCodePathFolder = Sheets("DCode Pivot").Range("A10")
Case 6: strDCodePathFolder = Sheets("DCode Pivot").Range("A11")
Case 7: strDCodePathFolder = Sheets("DCode Pivot").Range("A12")
Case 8: strDCodePathFolder = Sheets("DCode Pivot").Range("A13")
Case 9: strDCodePathFolder = Sheets("DCode Pivot").Range("A14")
Case 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A15")
Case 11: strDCodePathFolder = Sheets("DCode Pivot").Range("A16")
Case Else: MsgBox "No Entry Selected"
End Select
End Sub

Private Sub ListDCodeDEF_Click()
Select Case ListDCodeDEF.ListIndex
Case 0: strDCodePathFolder = Sheets("DCode Pivot").Range("A5")
Case 1: strDCodePathFolder = Sheets("DCode Pivot").Range("A6")
Case 2: strDCodePathFolder = Sheets("DCode Pivot").Range("A7")
Case 3: strDCodePathFolder = Sheets("DCode Pivot").Range("A8")
Case 4: strDCodePathFolder = Sheets("DCode Pivot").Range("A9")
Case 5: strDCodePathFolder = Sheets("DCode Pivot").Range("A10")
Case 6: strDCodePathFolder = Sheets("DCode Pivot").Range("A11")
Case 7: strDCodePathFolder = Sheets("DCode Pivot").Range("A12")
Case 8: strDCodePathFolder = Sheets("DCode Pivot").Range("A13")
Case 9: strDCodePathFolder = Sheets("DCode Pivot").Range("A14")
Case 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A15")
Case Else: MsgBox "No Entry Selected"
End Select
End Sub
Private Sub ListDCodeABC_Click()
Select Case ListDCodeABC.ListIndex
Case 0: strDCodePathFolder = Sheets("DCode Pivot").Range("A5")
Case 1: strDCodePathFolder = Sheets("DCode Pivot").Range("A6")
Case 2: strDCodePathFolder = Sheets("DCode Pivot").Range("A7")
Case 3: strDCodePathFolder = Sheets("DCode Pivot").Range("A8")
Case 4: strDCodePathFolder = Sheets("DCode Pivot").Range("A9")
Case 5: strDCodePathFolder = Sheets("DCode Pivot").Range("A10")
Case 6: strDCodePathFolder = Sheets("DCode Pivot").Range("A11")
Case 7: strDCodePathFolder = Sheets("DCode Pivot").Range("A12")
Case 8: strDCodePathFolder = Sheets("DCode Pivot").Range("A13")
Case 9: strDCodePathFolder = Sheets("DCode Pivot").Range("A14")
Case 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A15")
Case 11: strDCodePathFolder = Sheets("DCode Pivot").Range("A16")
Case Else: MsgBox "No Entry Selected"
End Select
End Sub

Private Sub MultiPage1_Change()

Dim i As Integer
Select Case MultiPage1.Value
Case 0
Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "XYZ"
Call ListDCodeXYZ_Click
Case 1
Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "DEF"
Call ListDCodeDEF_Click
Case 2
Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "ABC"
Call ListDCodeABC_Click
End Select

End Sub


Private Sub OKButton_Click()
Me.Tag = 0
Me.Hide
End Sub

 

[osx99]

Your events run as soon as the list box is clicked. Therefore, change your event procedures to run on either a listbox change or the listbox losing focus

eg.

Private Sub ListDCodeXYZ_Change()
Private Sub ListDCodeXYZ_lostfocus()

 

[clouddog9]

So just change the _click() to _change() on the ListBoxes? Is there anything else that I need to do or is that simple?

 

[osx99]

Yes. It is that simple but just make sure your form performs how you expect for your users. The wrong event can have unexpected results that could confuse the user (eg needing 2 clicks to select next listbox)

See this link for all listbox events you can use

http://msdn.microsoft.com/en-us/library/system.windows.forms.listbox_events.aspx

 

[osx99]

Also you could simplify your code to

Private Sub ListDCodeDEF_lostfocus()

Select Case ListDCodeDEF.ListIndex
  Case 0 To 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A5").Offset(ListDCodeDEF.ListIndex, 0)
  Case Else: MsgBox "No Entry Selected"
End Select

End Sub

 

[clouddog9]

I tried and changed all the clicks on the list details and it didn't work. For either the lostfocus or the change. Am I missing something?

 

[clouddog9]

Does it matter that I am using VBA vs. VB?

 

[combo]

If you want to wait for user action after selecting a page, why do you call the ListDCodeXYZ_Click (or similar) in MultiPage1_Change procedure?
BTW, maybe less annoying could be a sequence 'Select list' => 'Click button' instead?


combo

 

[clouddog9]

What would be an example for this code?

 

[clouddog9]

Sorry about the 2x post, but the reason I used the Call was because when I included the Select Case it still would not give me the option to select the from the list.

 

[clouddog9]

So anybody got an answer?

 

[combo]

You've got the answer:

change your event procedures to run on either a listbox change or the listbox losing focus

Run your procedures after user's action. In your current code page change runs list's procedure too, is it necessary? If so, inform the user. If not, run it later. In case of long process, it is better to run it with button click and add a message to the user. It's annoying when you change selection of the list and the interface hangs.

combo

 

[osx99]

Your final code should look something like this

Private Sub CancelButton_Click()
strDCodePathFolder = ""
Me.Tag = 1
Me.Hide
End Sub



Private Sub ListDCodeXYZ_Change()
Select Case ListDCodeXYZ.ListIndex
  Case 0 To 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A5").Offset(ListDCodeXYZ.ListIndex, 0)
  Case Else: MsgBox "No Entry Selected"
    End Select
End Sub

Private Sub ListDCodeDEF_Change()
Select Case ListDCodeDEF.ListIndex
  Case 0 To 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A5").Offset(ListDCodeDEF.ListIndex, 0)
  Case Else: MsgBox "No Entry Selected"
End Select

End Sub
Private Sub ListDCodeABC_Change()
Select Case ListDCodeABC.ListIndex
  Case 0 To 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A5").Offset(ListDCodeABC.ListIndex, 0)
  Case Else: MsgBox "No Entry Selected"
End Select
    End Select
End Sub

Private Sub MultiPage1_Change()

Dim i As Integer
Select Case MultiPage1.Value
    Case 0
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "XYZ"
        
    Case 1
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "DEF"
        
    Case 2
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "ABC"
        
End Select

End Sub


Private Sub OKButton_Click()
Me.Tag = 0
Me.Hide
End Sub

 

[osx99]

You could also test if the listbox values have changed using this

Public ListDCodeXYZ_value As Variant
Public ListDCodeDEF_value As Variant
Public ListDCodeABC_value As Variant


Private Sub CancelButton_Click()
strDCodePathFolder = ""
Me.Tag = 1
Me.Hide
End Sub



Private Sub ListDCodeXYZ_click()
If ListDCodeXYZ.Value <> ListDCodeXYZ_value Then
    Select Case ListDCodeXYZ.ListIndex
      Case 0 To 11: strDCodePathFolder = Sheets("DCode Pivot").Range("A5").Offset(ListDCodeXYZ.ListIndex, 0)
      Case Else: MsgBox "No Entry Selected"
        End Select
    ListDCodeXYZ_value = ListDCodeXYZ.Value
End If
End Sub

Private Sub ListDCodeDEF_click()
If ListDCodeDEF.Value <> ListDCodeDEF_value Then
    Select Case ListDCodeDEF.ListIndex
      Case 0 To 10: strDCodePathFolder = Sheets("DCode Pivot").Range("A5").Offset(ListDCodeDEF.ListIndex, 0)
      Case Else: MsgBox "No Entry Selected"
    End Select
    ListDCodeDEF_value = ListDCodeDEF.Value
End If
End Sub
Private Sub ListDCodeABC_click()
If ListDCodeABC.Value <> ListDCodeXYZ_value Then
    Select Case ListDCodeABC.ListIndex
      Case 0 To 11: strDCodePathFolder = Sheets("DCode Pivot").Range("A5").Offset(ListDCodeABC.ListIndex, 0)
      Case Else: MsgBox "No Entry Selected"
    End Select
    ListDCodeABC_value = ListDCodeABC.Value
End If
End Sub

Private Sub MultiPage1_Change()

Dim i As Integer
Select Case MultiPage1.Value
    Case 0
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "XYZ"
        
    Case 1
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "DEF"
        
    Case 2
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").ClearAllFilters
        Sheets("DCode Pivot").PivotTables("PivotTable1").PivotFields("Plant").CurrentPage = "ABC"
        
End Select

End Sub


Private Sub OKButton_Click()
Me.Tag = 0
Me.Hide
End Sub

 

[clouddog9]

I tried the first code and it worked perfectly. I was looking at the second code and I am trying to figure out why you have:

If ListDCodeDEF.Value <> ListDCodeDEF_value Then

In the code. What does this do? I mean I can tell from the code what it does, but what benefit does it have over the first code you put up?

 

[osx99]

ListDCodeDEF.Value is the value of the listbox
ListDCodeDEF_value is a public variable which starts off empty

The click event first tests 'If ListDCodeDEF.Value <> ListDCodeDEF_value', i.e. is the listbox value different to the previous value when the code was run. Effectively it's filtering the click event to capture changes in the listbox value only.

I think there are some problems occasionally where listboxes don't always trigger events when using the scroll bars, the second code will overcome this.