PDA

View Full Version : [SOLVED] UserForm that Stays Visible on Screen When Scrolling



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

jolivanes
05-06-2016, 11:18 PM
Would this do it?

Private Sub Workbook_open()
UserForm1.Show vbModeless
End Sub

gmaxey
05-07-2016, 03:25 PM
That would put it in the center. For the top right corner then something like this:

Nick72310
05-09-2016, 06:13 AM
Thank you for the responses. However, Neither of them put the form in the upper right corner. I have 2 monitors (not sure if that makes a difference).
I am more concerned with getting the functionality to work, rather than the position of the form at this point. So I would really appreciate the help with that.

snb
05-09-2016, 07:28 AM
I couldn't open your sample file.....

Nick72310
05-09-2016, 09:54 AM
I just have this little example.

16136

p45cal
05-11-2016, 09:17 AM
See attached.
Userform appears on sheet activation.
Move the userform to where you like (it can be positioned differently on showing).

Nick72310
05-11-2016, 01:27 PM
p45cal, that should do the trick! I appreciate the help!