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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.