Nick72310
05-06-2016, 03:03 PM
I have never used a UserForm before so I am looking for some help. I want the form to stay displayed on the upper right hand corner of the screen, even when the user is scolling up, down, left or right in the worksheet. I only want this displayed on one worksheet in my workbook. The form should display as a scrolling list and update on each selection change. I have code below that gets me my results without using any form (but it doesn't move when scrolling). Some of the code will need to be modified to work in the form. For instance, I obviously do not want data to be printed on Sheets("WI").Cells(1150, 7). I want it in the form.
There may be a better way to code all of this. I feel like it may slow down the workbook quite a bit so I would appreciate the help on that too. The idea behind the code is: I have a large data table (70,000 rows), and I want to display every item that has the value of Sheets("WI").Cells(Selection.Row, 2) in column 2 of the data table. That's all I'm trying to do. It's basically a filter.
Sorry, I don't have a file to share.
Sub WI_List()
CellLoopR = Selection.Row
Do
Sheets("WI").Cells(1150, 7) = Cells(CellLoopR, 2).End(xlUp)
CellLoopR = Cells(CellLoopR, 2).End(xlUp).Row
Loop While Len(Cells(CellLoopR, 2)) <= 3
Sheets("Code Testing").Range("H1").FormulaArray = "=MATCH(""" & Sheets("WI").Cells(1150, 7) & """,Table3[WI],0)+1"
rr = Sheets("Code Testing").Range("H1")
For i = 0 To WorksheetFunction.CountIf([Table3[WI]], Sheets("WI").Cells(1150, 7)) - 1
With Sheets("Sheet1")
'With Sheets("Oracle Item Numbers")
Range(.Cells(rr, 2), .Cells(.Range("A1").End(xlDown).Row, 2)).Name = "WI_Range"
End With
Sheets("Code Testing").Range("H1").FormulaArray = "=MATCH(""" & Sheets("WI").Cells(1150, 7) & """,WI_Range,0)"
Cells(1150 + 1 + i, 7) = Sheets("Sheet1").Cells(Sheets("Code Testing").Range("H1") + rr - 1, 1)
'Cells(1150 + 1 + i, 7) = Sheets("Oracle Item Numbers").Cells(Sheets("Code Testing").Range("G1") + rr - 1, 1)
rr = rr + Sheets("Code Testing").Range("H1")
Next i
End Sub
There may be a better way to code all of this. I feel like it may slow down the workbook quite a bit so I would appreciate the help on that too. The idea behind the code is: I have a large data table (70,000 rows), and I want to display every item that has the value of Sheets("WI").Cells(Selection.Row, 2) in column 2 of the data table. That's all I'm trying to do. It's basically a filter.
Sorry, I don't have a file to share.
Sub WI_List()
CellLoopR = Selection.Row
Do
Sheets("WI").Cells(1150, 7) = Cells(CellLoopR, 2).End(xlUp)
CellLoopR = Cells(CellLoopR, 2).End(xlUp).Row
Loop While Len(Cells(CellLoopR, 2)) <= 3
Sheets("Code Testing").Range("H1").FormulaArray = "=MATCH(""" & Sheets("WI").Cells(1150, 7) & """,Table3[WI],0)+1"
rr = Sheets("Code Testing").Range("H1")
For i = 0 To WorksheetFunction.CountIf([Table3[WI]], Sheets("WI").Cells(1150, 7)) - 1
With Sheets("Sheet1")
'With Sheets("Oracle Item Numbers")
Range(.Cells(rr, 2), .Cells(.Range("A1").End(xlDown).Row, 2)).Name = "WI_Range"
End With
Sheets("Code Testing").Range("H1").FormulaArray = "=MATCH(""" & Sheets("WI").Cells(1150, 7) & """,WI_Range,0)"
Cells(1150 + 1 + i, 7) = Sheets("Sheet1").Cells(Sheets("Code Testing").Range("H1") + rr - 1, 1)
'Cells(1150 + 1 + i, 7) = Sheets("Oracle Item Numbers").Cells(Sheets("Code Testing").Range("G1") + rr - 1, 1)
rr = rr + Sheets("Code Testing").Range("H1")
Next i
End Sub