....SPOT THE USERFORM!!!!.....

[WhytheQ]

I have the following little routine:

'===================
Private Sub CmdSelectData_Click()
Me.Hide
Call SelectDataRow
Me.Show
End Sub
'===================

All looks fairly standard!

The called routine, "SelectDataRow", is the following:

'===================
Sub SelectDataRow()
'finds the last used row in the sheet that the user presently
has active
Dim LastUsedRow As Integer
LastUsedRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row

'get the user to select a row of data for moving to the Log
Application.Calculation = xlCalculationAutomatic
On Error Resume Next
Set InvoiceRange = Application.InputBox("Either: " & vbCrLf & _
"1.Confirm the present selection by hitting OK" & vbCrLf & _
"2.Select a cell in another row and hit OK" & vbCrLf & _
"3.Hit CANCEL to stop the macro here", _
"RELEVANT PAYMENT INFORMATION", "A" & LastUsedRow & ":L" & _
LastUsedRow, , , , , 8)
On Error GoTo 0

'if nothing has been selected on the activesheet then this is
flagged to the user
If InvoiceRange Is Nothing Then
MsgBox "No cells on the ACTIVESHEET have been selected"
End If

End Sub
'===================


The above looks pretty standard to me aswell. The problem is that when
the code has run the routine SelectDataRow and then hits the line
Me.Show the userform appears in a different position on the screen!! Do
I need to change one of the userform's property's in Design Time - or
will I need to write some extra code to be executed in run time?

Any help greatly appreciated,
Jason.

 

[NickHK]

If you set the StartUpPosition to 0 - Manual, it will reappear in the last
position.
You should set the correct position before the first .Show, otherwise it
will show in the top, left corner.
Load UserForm1

Private Sub UserForm_Initialize()
Me.Move 100, 100
Me.Show , vbModeless
End Sub

But you could show the userform modeless (as shown), so you not have to hide
to show the InputBox.

NickHK

 

[WhytheQ]

Nice one Nick.
I've ended up going for "2 - CenterScreen" as some future users have
two screens and some just have one so I'm a bit worried about what sort
of impact that'll have.
May well experiment with using the modeless argument, although the code
behind the userform will open various other workbooks, apart from the
one which owns the userform so I'm already thinking it might be safer
to stick to a modal form.

Thanks for your help
Jason.

 

[NickHK]

Jason,
At least you know the settings you have to play with.
Depending how the 2-screen users' systems/video driver are set up,
"CenterScreen", could well split your userform over the 2 screens, so you
may need some extra code handle this.

I seems to remember some thread in this NG (or may be
microsoft.public.vb.general.discussion) on detecting/working with multiple
monitors.

NickHK

 

[WhytheQ]

I'm one of the lucky ones with 2 monitors and it just plonks the form
in the middle of the monitor which I have Excel running on. I'll just
have to take a gamble and assume it will behave the same on any
colleagues who have twin monitors.

Thanks again
Jason