PDA

View Full Version : help with a price list demo



csavini
12-03-2007, 09:27 AM
Hi, Figment helped me out tremendously with this but there are a few little bugs with it. for example if I select the first machine in the filter it does not show up. Also if I pick multiple machine with the same name (i.e. imageRUNNER 2016 and imageRUNNER 2016i) only one shows up instead of both. I am good with excel but horrible with VBA if there is anyone to help me with this, I know figment is a bit busy. Your help is greatly appreciated. I attached a copy of the file.
7437

Thanks,

Chris

figment
12-03-2007, 10:17 AM
i managed to find some time during lunch to go back through the code. here is the fix

Private Sub OKButton_Click()
Dim cell As Range
Dim a As Long, b As Long
b = 5
For a = 5 To Range("A5").End(xlDown).Row
If check(Range("A" & a)) Then
If a <> b Then
Rows(b & ":" & a - 1).Hidden = True
End If
b = a + 1
End If
Next
If b <> a Then Rows(b & ":" & a).Hidden = True
' unload the dialog box
Unload userform2
End Sub

Bob Phillips
12-03-2007, 10:40 AM
Try this



Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
Dim i As Long
Dim mpRows As Long
Dim mpRowsToHide As Range
Dim mpRow As Range

With ActiveSheet

mpRows = .Range("A5").End(xlDown).Row
For i = 5 To mpRows
If Not Matched(.Cells(i, "A").Value) Then

If mpRowsToHide Is Nothing Then

Set mpRowsToHide = .Rows(i)
Else

Set mpRowsToHide = Union(mpRowsToHide, .Rows(i))
End If
End If
Next i
End With

If Not mpRowsToHide Is Nothing Then

Application.ScreenUpdating = False
For Each mpRow In mpRowsToHide.Rows

mpRow.Hidden = True
Next mpRow
Application.ScreenUpdating = True
End If

' unload the dialog box
Unload Me
End Sub

Sub UserForm_Initialize()
Dim mpList As Variant
Dim mpRows As Long

With ActiveSheet

mpRows = .Range("A5").End(xlDown).Row - 4
.Rows("5:" & .Range("A5").End(xlDown).Row).Hidden = False
ReDim mpList(1 To mpRows) As Variant

' fill the list box
mpList = .Range("A5").Resize(mpRows)
Me.ListBox1.List = mpList
' select the first list item
Me.ListBox1.ListIndex = 0
Me.ListBox1.MultiSelect = 1
End With
End Sub

Function Matched(Lookup As Variant) As Boolean
Dim mpIndex As Long

With Me

Matched = False
For mpIndex = 0 To .ListBox1.ListCount - 1

If .ListBox1.Selected(mpIndex) And _
.ListBox1.List(mpIndex) = Lookup Then

Matched = True
Exit Function
End If
Next
End With
End Function