Vlookup is this what I need???

[csavini]

I am in dire need of help. I have a spreadsheet in Excel, there are 13 columns of information being used. 3 of the columns have just data I typed in (model name, item code, original price) the other 10 have formulas (these formulas are price discounts that will be taken off of the original price. 9 of them also have a check box on the top of the column so if the the checkbox is selected, the formula will give the customer the amount discounted off the original price(keep in mind that not all of the columns can be used together, for example, on product "A" maybe only 3 of the boxes can be used whereas on another product maybe 5 can be used). I made a multiple listbox, so that if a customer selects a product or multiple products and clicks the ok button, the sheet will only show the specific products they selected. My problem is that when I press the "OK" button nothing happens. I don't know how to link all of this together. Please tell me you can help me.:banghead:

[figment]

sounds like your using a macro on the button, if you could post the code, or post the workbook, then some one will be able to help you.
If you do post the workbook, whis is the most helpfull of the two options, feel free to aulter the data. we just need to know what your doing we dont need to know your companines private info.

[csavini]

This is what I have so far as code goes. this is for the userform box. That all works good, it is just when I select a machine(s) and then hit ok. it all just disappears. I have all of the data in sheet 1. Thanks for the help.

Private Sub CancelButton_Click()
Unload userform1
End Sub
Private Sub OKButton_Click()
Dim cell As Range



' unload the dialog box
Unload userform1
End Sub
Sub UserForm_Initialize()
' fill the list box
With ListBox1
.AddItem "imageRUNNER 1023"
.AddItem "imageRUNNER 1023iF"
.AddItem "imageRUNNER 1023N"
.AddItem "imageRUNNER 2016"
.AddItem "imageRUNNER 2016i"
.AddItem "imageRUNNER 2020"
.AddItem "imageRUNNER 2020i"
.AddItem "imageRUNNER 2025i"
.AddItem "imageRUNNER 2030i"
.AddItem "imageRUNNER 2230"
.AddItem "imageRUNNER 2830"
.AddItem "imageRUNNER 3025"
.AddItem "imageRUNNER 3030"
.AddItem "imageRUNNER 3035"
.AddItem "imageRUNNER 3045"
.AddItem "imageRUNNER 3530"
.AddItem "imageRUNNER 5050"
.AddItem "imageRUNNER 5055 V2"
.AddItem "imageRUNNER 5065"
.AddItem "imageRUNNER 5065 V2"
.AddItem "imageRUNNER 5070"
.AddItem "imageRUNNER 5075"
.AddItem "imageRUNNER 5075 V2"
.AddItem "imageRUNNER 7086"
.AddItem "imageRUNNER 7086 V2"
.AddItem "imageRUNNER 7095"
.AddItem "imageRUNNER 7095 V2"
.AddItem "imageRUNNER 7095 Printer"
.AddItem "imageRUNNER 7095 Printer V2"
.AddItem "imageRUNNER 7095 Printer W/Multi-PDL"
.AddItem "imageRUNNER 7095 Printer w/imagePASS-S1"
.AddItem "imageRUNNER 7095 Printer W/imagePASS-S2"
.AddItem "imageRUNNER 7105 V2"
.AddItem "imageRUNNER C2880 V2"
.AddItem "imageRUNNER C2880i V2"
.AddItem "imageRUNNER C3380"
.AddItem "imageRUNNER C3380 V2"
.AddItem "imageRUNNER C3380i V2"
.AddItem "imageRUNNER C4080 V2"
.AddItem "imageRUNNER C4080i V2"
.AddItem "imageRUNNER C4580 V2"
.AddItem "imageRUNNER C4580i V2"
.AddItem "imageRUNNER C5058"
.AddItem "imageRUNNER C5068"
.AddItem "imageRUNNER C5185"
.AddItem "imageRUNNER C5185i"
.AddItem "imageRUNNER C5870U"
.AddItem "imageRUNNER C6870U"
.AddItem "imageRUNNER Pro 7110VP"
.AddItem "imageRUNNER Pro 7125VP"
.AddItem "imageRUNNER Pro 7138VP"
.AddItem "imageRUNNER Pro 7150VP"
.AddItem "imageRUNNER C5180"
.AddItem "imageRUNNER C5180i"
.AddItem "imageRUNNER C3220"
.AddItem "imageRUNNER C3220 Printer"
.AddItem "imageRUNNER 8070"
.AddItem "imageRUNNER 5570"
.AddItem "imageRUNNER 6570"
.AddItem "imageRUNNER 3570"
.AddItem "imageRUNNER 4570"
.AddItem "imageRUNNER 2870"
.AddItem "imageRUNNER 2270"
.AddItem "imageRUNNER 1630"
.AddItem "imageRUNNER 1670F"
.AddItem "imageRUNNER 2010F"
End With

' select the first list item
ListBox1.ListIndex = 0
userform1.ListBox1.MultiSelect = 1
End Sub
Sub showlist()
userform1.Show
End Sub

[lucas]

all works good, it is just when I select a machine(s) and then hit ok. it all just disappears.
That is because you have told it to dissapear. In fact that is the only thing you have told it to do when you click the ok button:
Private Sub OKButton_Click()
Dim cell As Range
' unload the dialog box
Unload userform1
End Sub

You even have a comment where you say that...'unload the dialog box

You will need to provide us with a little more info and a sample file would help a lot. Why not just have the selections go into a second listbox?

[csavini]

there are too many columns of info being used to put in a another list box. I am good with excel but horrible with VBA. I attached a view of the excel file as to what I am trying to do. once a machine is chosen from the list box and ok is clicked, the machine that was selected is the only machine to be seen with all of the information in the corresponding row. If it is easier to call me you can my number is 516-327-2082. I hate doing stuff like this over email...I am never sure I am explaining my self correctly.

Thanks for the help.
chris

7396

[figment]

try this

Private Sub CancelButton_Click()
Unload userform1
End Sub
Private Sub OKButton_Click()
Dim cell As Range
Dim a As Long, b As Long
b = a
For a = 5 To Range("A5").End(xlDown).Row - 3
If Range("A" & a) = listbox1.Index And a <> b Then
Rows(b & ":" & a - 1).Hide
b = a + 1
End If
Next
If b <> a Then Rows(b & ":" & a).Hide
' unload the dialog box
Unload userform1
End Sub


Sub UserForm_Initialize()
' fill the list box
listbox1.List = Range("A5").Resize(Range("A5").End(xlDown).Row - 4)
' select the first list item
listbox1.ListIndex = 0
userform1.listbox1.MultiSelect = 1
End Sub
Sub showlist()
userform1.Show
End Sub


i didn't test it so there might be on or two small bugs

[csavini]

ok i put in that code and even sent an image of what i see. there is an error and I don't know what to do. Was I supposed to go over all of my old code?

I really hate not knowing what I am doing with this VBA stuff.

You are really helping me out!!!!!
I soooooo appreciate it.

chris
7398

[mdmackillop]

Try

ListBox1.List = Range("A5").Resize(Range("A5").End(xlDown).Row - 4).Value

[csavini]

nope...didn't work. I still get the same error. :banghead:

[figment]

its been a long time sence i have used a listbox or the hide show function

try this:

Private Sub CancelButton_Click()
Unload UserForm1
End Sub
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)) And a <> b Then
Rows(b & ":" & a - 1).Hidden = True
b = a + 1
End If
Next
If b <> a Then Rows(b & ":" & a).Hidden = True
' unload the dialog box
Unload UserForm1
End Sub


Sub UserForm_Initialize()
Rows("5:" & Range("A5").End(xlDown).Row).Hidden = False
ReDim b(1 To Range("A5").End(xlDown).Row - 4) As Variant
With UserForm1
' fill the list box
b = Range("A5").Resize(Range("A5").End(xlDown).Row - 4)
.ListBox1.List = b
' select the first list item
.ListBox1.ListIndex = 0
.ListBox1.MultiSelect = 1
End With
End Sub
Sub showlist()
UserForm1.Show
End Sub

Function check(a As Variant)
Dim b As Long
For b = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(b) And UserForm1.ListBox1.List(b) = a Then
check = 1
Exit Function
End If
Next
check = 0
End Function

[mdmackillop]

A Variation


Option Explicit
Sub UserForm_Initialize()
' fill the list box
ListBox1.List = Range("A5").Resize(Range("A5").End(xlDown).Row - 4).Value
' select the first list item
ListBox1.ListIndex = 0
UserForm1.ListBox1.MultiSelect = 1
End Sub

Private Sub OKButton_Click()
Dim cel As Range
Dim a As Long, i As Long, tmp
Dim Arr()
ReDim Arr(i)
For a = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(a) Then
Arr(i) = ListBox1.List(a)
i = i + 1
ReDim Preserve Arr(i)
End If
Next
For Each cel In Range(Cells(5, 1), Cells(5, 1).End(xlDown))
On Error Resume Next
tmp = Application.Match(cel.Value, Arr, 0)
If Not (tmp > 0) Then cel.EntireRow.RowHeight = 0
tmp = 0
Next
' unload the dialog box
Unload UserForm1
End Sub

Sub showlist()
UserForm1.Show
End Sub

Private Sub CancelButton_Click()
Unload UserForm1
End Sub

[csavini]

ERRRRRRRRRRRRRRRRRRRRRR!!!!! I am still getting the same problem, This is really bugging me. I am at a loss. What do I do?

Thanks,

Chris:banghead:

[csavini]

here is a copy of the file. I had to remove the prices due to the sensitivity for the company. Please help.

thanks

chris
:banghead:
7416

[figment]

this works

7418

the problem was that you were calling the userform something other then UserForm1 so every time the code tryed to refrance it with the name UserForm1 you got an object dosn't exist error.

i put my version of the code in rather the mdmackillop's becaus it makes reshowing everthing easyer. especaly if you have rows that are set to diffrent hights.

[csavini]

OH MY GOD!!!!!! You Are A GENIUS!!!!!! YOU ARE THE BEST FIGMENT!!!!!
I REALLY APPRECIATE WHAT YOU HAVE DONE!!!!

THANKS SOOOOOOOO MUCH!!!!
HAVE A GREAT HOLIDAY!!!!

CHRIS:cloud9: :thumb :ipray: :mbounce2: :yay

[csavini]

Hey