how to scroll spreadsheet while my userform is still on

[berry456456]

I worte a Macro for search in my spreadsheet.
So i designed a userform with a listbox.
If you choose an item from the listbox and click "OK" commandbutton, the data line in the spreadsheet for that item will be selected.
But now i can not scroll the bar in the spreadsheet. How can scroll spreadsheet to view the selected lines while my useform windown is till float on the screen? in this way, i can keep select different item from the listbox in userform and view the correspondent data line in spreadsheet many times? Thanks!

 

[NWBeaver]

In the properties dialog box for the userform, set the "ShowModal" property to false.

 

[xlbo]

...or just pop up the FIND dialog box....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DaveInIowa]

...or

Application.Goto Reference:=myRange, Scroll:=True

 

[berry456456]

Thank you all very much! Actually, I still have some questions:

To NWBeaver:
I tried in coding like:
Userform.showmodal=false
and it does not work. Is that true you can only set this properity manualy?

To DaveInIowa:
I tries this code:
Application.Goto Reference:=myRange, Scroll:=True
my whole code is:

Private Sub OK_Click()
If State_ListBox.ListIndex < 0 Then
MsgBox "you should choose at one state"
Else
Range(Cells(State_ListBox.ListIndex + 2, 1), Cells(State_ListBox.ListIndex + 2, 17)).Select
Application.Goto Reference:=myRange, Scroll:=True
End If
End Sub

but does not work. Is that because my Excel is 2003 and this works only in higher version such as 2007?

Thanks again and have a wonderful day

 

[DaveInIowa]

myRange" was not meant to be taken literally, but as a placeholder for whatever range you want selected. You may want to set it to column "A" of whatever row you're selecting so the window doesn't scroll to the right. Also, you'll probably want to do this before you do the Select as I believe Goto will select a new cell.

Dim myRange As Range
Set myRange = Range(Cells(State_ListBox.ListIndex + 2, 1), Cells(State_ListBox.ListIndex + 2, 17))
Application.Goto Reference:=myRange, Scroll:=True

 

[NWBeaver]

The userform mode can be set using VBA code.
You control the mode through the .show method of the userform, such as in the following example:

Sub OpenForm()

modevalue = 0 'modeless=0, modal=1
frmUserForm.Show modevalue

End Sub

In the example above I have used a variable 'modevalue', so I can change the mode by just changing its value from 0 to 1.

Or you could just have two lines of code:

frmUserForm.Show 0 'modeless

frmUserForm.Show 1 'modal

and conditionally select one or the other.

 

[berry456456]

Thank you so much, DaveInIowa and NWBeaver. That's very helpful and clear. Hope both of you have a wondful day!