I am using an InputBox to obtain a range reference instead of a RefEdit
control (see my other posts for reasons). When I click a button to launch
this InputBox, I hide the main userform. Then when the range is obtained, I
show the form again.

The problem is that the location of the userform is not saved; that is, I
move it away from window center, hide it, show it, and the window is back in
the way. How can I adjust its position?

Also, can I use the same method to force the InputBox to appear in the same
location as the userform?

Thanks,
Pflugs

Hi Pflugs,

You could trap the form's position coordinates and reset when you reshow.
FWIW I don't totally hide the form but rsize to something small out of the
way, for ideas -

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function EnableWindow Lib "user32" ( _
ByVal hWnd As Long, ByVal bEnable As Long) As Long

Private Sub UserForm_Click()
Dim r As Range
If GetInputRng(r) Then
MsgBox r.Address
End If
End Sub
Function GetInputRng(rng As Range) As Boolean
Dim hWnd As Long
Dim lt As Single, tp As Single, ht As Single
Dim sDef As String

With Me
lt = .Left
tp = .Top
ht = .Height
..Top = 0
..Left = 0
..Height = 0
End With

If tp < 0 Then tp = 0
If lt < 0 Then lt = 0
On Error Resume Next
sDef = Selection.Address
Set rng = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=8)
On Error GoTo 0
GetInputRng = Not rng Is Nothing

Me.Left = lt
Me.Top = tp
Me.Height = ht

' following only with a modal form
AppActivate Me.Caption
nHWind = FindWindowA("XLMAIN", Application.Caption)
EnableWindow nHWind, 0&

End Function

Unfortunately InputBox's optional coordinates are not relative to the screen
as it says in help, they can be calculated but probably not worth the
effort, something rough as above normally be OK. The API to restore a modal
form to modal when done (you won't see that in help!).

Another FWIW, I don't use type:8 as above but the formula method to avoid a
bug with particular CF's

Regards,
Peter T


"Pflugs" wrote in message
...
I am using an InputBox to obtain a range reference instead of a RefEdit
control (see my other posts for reasons). When I click a button to launch
this InputBox, I hide the main userform. Then when the range is obtained,
I
show the form again.

The problem is that the location of the userform is not saved; that is, I
move it away from window center, hide it, show it, and the window is back
in
the way. How can I adjust its position?

Also, can I use the same method to force the InputBox to appear in the
same
location as the userform?

Thanks,
Pflugs

Hi Peter,
In regard to...
" I don't use type:8 ... but the formula method to avoid a bug with particular CF's"

What did you find with conditional formatting that affects returning a range
object from an Input Box?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins)

Hi Jim,

With IsFormula CF's that include a worksheet function in the formula,
InputBox Type:=8 can fail to return a range.

No problem to return a formula with Type:=0. It will be in R1C1 format,
parse out the "=", ConvertFormula from R1C1 to A1 style, set the range.
Include some error handling for 'False' or an invalid address and away you
go.

Similar type CF's may affect other things such as GetOpenFileName.

Regards,
Peter T

"Jim Cone" wrote in message
...

Hi Peter,
In regard to...
" I don't use type:8 ... but the formula method to avoid a bug with
particular CF's"

What did you find with conditional formatting that affects returning a
range
object from an Input Box?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins)

Hi Peter,
Thanks for the info.
I tried using Average in a CF formulas without a problem and am
finding the desire to go thru 200? or so functions is pretty low.
I always check that the return value is a range and think I will just
"let it ride".
Regards,
Jim Cone


"Peter T" <peter_t@discussions
wrote in message
Hi Jim,
With IsFormula CF's that include a worksheet function in the formula,
InputBox Type:=8 can fail to return a range.

No problem to return a formula with Type:=0. It will be in R1C1 format,
parse out the "=", ConvertFormula from R1C1 to A1 style, set the range.
Include some error handling for 'False' or an invalid address and away you
go.
Similar type CF's may affect other things such as GetOpenFileName.
Regards,
Peter T


"Jim Cone"
wrote in message
Hi Peter,
In regard to...
" I don't use type:8 ... but the formula method to avoid a bug with
particular CF's"

What did you find with conditional formatting that affects returning a
range
object from an Input Box?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins)

Hi again,

I guessed this issue (CF's with Inputbox) must have been reported somewhere
and indeed it has -

http://www.jkp-ads.com/articles/SelectARange.asp

It also seems the issue is far more prevalent than I realised, not only with
IsFormula but any type of CF, see other links on Jan Karel Pieterse's page.

However I don't think RefEdit, as cited, is the only viable workaround,
Inputbox with Type:=0 works fine (see example posted to Pflugs).

Regards,
Peter T


"Jim Cone" wrote in message
...

Hi Peter,
Thanks for the info.
I tried using Average in a CF formulas without a problem and am
finding the desire to go thru 200? or so functions is pretty low.
I always check that the return value is a range and think I will just
"let it ride".
Regards,
Jim Cone


"Peter T" <peter_t@discussions
wrote in message
Hi Jim,
With IsFormula CF's that include a worksheet function in the formula,
InputBox Type:=8 can fail to return a range.

No problem to return a formula with Type:=0. It will be in R1C1 format,
parse out the "=", ConvertFormula from R1C1 to A1 style, set the range.
Include some error handling for 'False' or an invalid address and away you
go.
Similar type CF's may affect other things such as GetOpenFileName.
Regards,
Peter T


"Jim Cone"
wrote in message
Hi Peter,
In regard to...
" I don't use type:8 ... but the formula method to avoid a bug with
particular CF's"

What did you find with conditional formatting that affects returning a
range
object from an Input Box?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins)

Peter,

Thanks for the hlep. That works well.

Regarding your comment on not using Type:=8, how do you implement the
formula method? Similar to this:?

temp = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50, Type:=0)
temp = Application.ConvertFormula(temp, xlR1C1, xlA1)
temp = Replace(temp, "=", "")

Also, when I try to use this method, the inputbox disappears when it is
launched. I can't see what I'm entering and may only blindly hit OK or
CANCEL. Any ideas?

Thanks,
Pflugs

"Peter T" wrote:

Hi Pflugs,

You could trap the form's position coordinates and reset when you reshow.
FWIW I don't totally hide the form but rsize to something small out of the
way, for ideas -

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function EnableWindow Lib "user32" ( _
ByVal hWnd As Long, ByVal bEnable As Long) As Long

Private Sub UserForm_Click()
Dim r As Range
If GetInputRng(r) Then
MsgBox r.Address
End If
End Sub
Function GetInputRng(rng As Range) As Boolean
Dim hWnd As Long
Dim lt As Single, tp As Single, ht As Single
Dim sDef As String

With Me
lt = .Left
tp = .Top
ht = .Height
..Top = 0
..Left = 0
..Height = 0
End With

If tp < 0 Then tp = 0
If lt < 0 Then lt = 0
On Error Resume Next
sDef = Selection.Address
Set rng = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=8)
On Error GoTo 0
GetInputRng = Not rng Is Nothing

Me.Left = lt
Me.Top = tp
Me.Height = ht

' following only with a modal form
AppActivate Me.Caption
nHWind = FindWindowA("XLMAIN", Application.Caption)
EnableWindow nHWind, 0&

End Function

Unfortunately InputBox's optional coordinates are not relative to the screen
as it says in help, they can be calculated but probably not worth the
effort, something rough as above normally be OK. The API to restore a modal
form to modal when done (you won't see that in help!).

Another FWIW, I don't use type:8 as above but the formula method to avoid a
bug with particular CF's

Regards,
Peter T


"Pflugs" wrote in message
...
I am using an InputBox to obtain a range reference instead of a RefEdit
control (see my other posts for reasons). When I click a button to launch
this InputBox, I hide the main userform. Then when the range is obtained,
I
show the form again.

The problem is that the location of the userform is not saved; that is, I
move it away from window center, hide it, show it, and the window is back
in
the way. How can I adjust its position?

Also, can I use the same method to force the InputBox to appear in the
same
location as the userform?

Thanks,
Pflugs

I implement along similar lines as your 'temp' etc, but can't think why your
inputbox should suddenly disappear without a chance to use as normal
(SendKeys involved perhaps ?).

Forgot to mention need to cater for formula generated both by selecting
cells & manually typed, w/out the = and other quotes that creep in. Try
something like the following (this is stripped down from a much larger
function & options used in a dll, hope no errors have crept in while editing
for this post).

Function GetInputRange2(rng As Excel.Range, _
sPrompt As String, _
sTitle As String, _
Optional sDefault As String, _
Optional X, Optional Y, _
Optional bActivate As Boolean) As Boolean
Dim bGotRng As Boolean
Dim sAddr As String
Dim vReturn

On Error Resume Next
If Len(sDefault) = 0 Then
If TypeName(ActiveSheet) = "Chart" Then
sDefault = " first select a Worksheet"
ElseIf TypeName(Selection) = "Range" Then
sDefault = "=" & Selection.Address
Else
sDefault = " Select Cell(s)"
End If
End If

vReturn = Application.InputBox(sPrompt, sTitle, sDefault, X, Y, Type:=0)

If Len(vReturn) And False < vReturn Then
sAddr = vReturn
' might be an idea to increase the 256 to 1024
If Left$(sAddr, 1) = "=" Then sAddr = Mid$(sAddr, 2, 256)
If Left$(sAddr, 1) = Chr(34) Then sAddr = Mid$(sAddr, 2, 255)
If Right$(sAddr, 1) = Chr(34) Then sAddr = Left$(sAddr, Len(sAddr) -
1)

Set rng = Range(sAddr)

If rng Is Nothing Then
sAddr = Application.ConvertFormula(sAddr, xlR1C1, xlA1)
Set rng = Range(sAddr)
bGotRng = Not rng Is Nothing
Else
bGotRng = True
End If
End If

On Error GoTo errH
If bGotRng And bActivate Then
If Not ActiveWorkbook Is rng.Parent.Parent Then
rng.Parent.Parent.Activate
End If
If Not ActiveSheet Is rng.Parent Then
rng.Parent.Activate
End If
rng.Activate
End If

done:
GetInputRange2 = bGotRng
Exit Function
errH:
bGotRng = False
Resume done

End Function

Sub test2()
Dim rInput As Range

If GetInputRange2(rInput, "Prompt", "Title", "", , , True) Then
MsgBox rInput.Address(, , , True)
Else
MsgBox "not got a range"
End If

End Sub

Try selecting a range in different sheet and/or workbook to test the
bActivate option (may want an additional option to disable events).

Regards,
Peter T

"Pflugs" wrote in message
...
Peter,

Thanks for the hlep. That works well.

Regarding your comment on not using Type:=8, how do you implement the
formula method? Similar to this:?

temp = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=0)
temp = Application.ConvertFormula(temp, xlR1C1, xlA1)
temp = Replace(temp, "=", "")

Also, when I try to use this method, the inputbox disappears when it is
launched. I can't see what I'm entering and may only blindly hit OK or
CANCEL. Any ideas?

Thanks,
Pflugs

"Peter T" wrote:

Hi Pflugs,

You could trap the form's position coordinates and reset when you
reshow.
FWIW I don't totally hide the form but rsize to something small out of
the
way, for ideas -

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function EnableWindow Lib "user32" ( _
ByVal hWnd As Long, ByVal bEnable As Long) As Long

Private Sub UserForm_Click()
Dim r As Range
If GetInputRng(r) Then
MsgBox r.Address
End If
End Sub
Function GetInputRng(rng As Range) As Boolean
Dim hWnd As Long
Dim lt As Single, tp As Single, ht As Single
Dim sDef As String

With Me
lt = .Left
tp = .Top
ht = .Height
..Top = 0
..Left = 0
..Height = 0
End With

If tp < 0 Then tp = 0
If lt < 0 Then lt = 0
On Error Resume Next
sDef = Selection.Address
Set rng = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=8)
On Error GoTo 0
GetInputRng = Not rng Is Nothing

Me.Left = lt
Me.Top = tp
Me.Height = ht

' following only with a modal form
AppActivate Me.Caption
nHWind = FindWindowA("XLMAIN", Application.Caption)
EnableWindow nHWind, 0&

End Function

Unfortunately InputBox's optional coordinates are not relative to the
screen
as it says in help, they can be calculated but probably not worth the
effort, something rough as above normally be OK. The API to restore a
modal
form to modal when done (you won't see that in help!).

Another FWIW, I don't use type:8 as above but the formula method to
avoid a
bug with particular CF's

Regards,
Peter T


"Pflugs" wrote in message
...
I am using an InputBox to obtain a range reference instead of a
RefEdit
control (see my other posts for reasons). When I click a button to
launch
this InputBox, I hide the main userform. Then when the range is
obtained,
I
show the form again.

The problem is that the location of the userform is not saved; that
is, I
move it away from window center, hide it, show it, and the window is
back
in
the way. How can I adjust its position?

Also, can I use the same method to force the InputBox to appear in the
same
location as the userform?

Thanks,
Pflugs

Thank you again, that has been very helpful.

The last minor issue (annoyance) is that my userform isn't being reactivated
after the range is selected. I used the code that you sent in the original
post:

' following only with a modal form
AppActivate .Caption
nHWind = FindWindowA("XLMAIN", Application.Caption)
EnableWindow nHWind, 0&

But, this doesn't seem to be working. I tried using the userform's name,
but that didn't seem to work, either. Any ideas?

Thanks,
Pflugs

"Peter T" wrote:

I implement along similar lines as your 'temp' etc, but can't think why your
inputbox should suddenly disappear without a chance to use as normal
(SendKeys involved perhaps ?).

Forgot to mention need to cater for formula generated both by selecting
cells & manually typed, w/out the = and other quotes that creep in. Try
something like the following (this is stripped down from a much larger
function & options used in a dll, hope no errors have crept in while editing
for this post).

Function GetInputRange2(rng As Excel.Range, _
sPrompt As String, _
sTitle As String, _
Optional sDefault As String, _
Optional X, Optional Y, _
Optional bActivate As Boolean) As Boolean
Dim bGotRng As Boolean
Dim sAddr As String
Dim vReturn

On Error Resume Next
If Len(sDefault) = 0 Then
If TypeName(ActiveSheet) = "Chart" Then
sDefault = " first select a Worksheet"
ElseIf TypeName(Selection) = "Range" Then
sDefault = "=" & Selection.Address
Else
sDefault = " Select Cell(s)"
End If
End If

vReturn = Application.InputBox(sPrompt, sTitle, sDefault, X, Y, Type:=0)

If Len(vReturn) And False < vReturn Then
sAddr = vReturn
' might be an idea to increase the 256 to 1024
If Left$(sAddr, 1) = "=" Then sAddr = Mid$(sAddr, 2, 256)
If Left$(sAddr, 1) = Chr(34) Then sAddr = Mid$(sAddr, 2, 255)
If Right$(sAddr, 1) = Chr(34) Then sAddr = Left$(sAddr, Len(sAddr) -
1)

Set rng = Range(sAddr)

If rng Is Nothing Then
sAddr = Application.ConvertFormula(sAddr, xlR1C1, xlA1)
Set rng = Range(sAddr)
bGotRng = Not rng Is Nothing
Else
bGotRng = True
End If
End If

On Error GoTo errH
If bGotRng And bActivate Then
If Not ActiveWorkbook Is rng.Parent.Parent Then
rng.Parent.Parent.Activate
End If
If Not ActiveSheet Is rng.Parent Then
rng.Parent.Activate
End If
rng.Activate
End If

done:
GetInputRange2 = bGotRng
Exit Function
errH:
bGotRng = False
Resume done

End Function

Sub test2()
Dim rInput As Range

If GetInputRange2(rInput, "Prompt", "Title", "", , , True) Then
MsgBox rInput.Address(, , , True)
Else
MsgBox "not got a range"
End If

End Sub

Try selecting a range in different sheet and/or workbook to test the
bActivate option (may want an additional option to disable events).

Regards,
Peter T

"Pflugs" wrote in message
...
Peter,

Thanks for the hlep. That works well.

Regarding your comment on not using Type:=8, how do you implement the
formula method? Similar to this:?

temp = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=0)
temp = Application.ConvertFormula(temp, xlR1C1, xlA1)
temp = Replace(temp, "=", "")

Also, when I try to use this method, the inputbox disappears when it is
launched. I can't see what I'm entering and may only blindly hit OK or
CANCEL. Any ideas?

Thanks,
Pflugs

"Peter T" wrote:

Hi Pflugs,

You could trap the form's position coordinates and reset when you
reshow.
FWIW I don't totally hide the form but rsize to something small out of
the
way, for ideas -

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function EnableWindow Lib "user32" ( _
ByVal hWnd As Long, ByVal bEnable As Long) As Long

Private Sub UserForm_Click()
Dim r As Range
If GetInputRng(r) Then
MsgBox r.Address
End If
End Sub
Function GetInputRng(rng As Range) As Boolean
Dim hWnd As Long
Dim lt As Single, tp As Single, ht As Single
Dim sDef As String

With Me
lt = .Left
tp = .Top
ht = .Height
..Top = 0
..Left = 0
..Height = 0
End With

If tp < 0 Then tp = 0
If lt < 0 Then lt = 0
On Error Resume Next
sDef = Selection.Address
Set rng = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=8)
On Error GoTo 0
GetInputRng = Not rng Is Nothing

Me.Left = lt
Me.Top = tp
Me.Height = ht

' following only with a modal form
AppActivate Me.Caption
nHWind = FindWindowA("XLMAIN", Application.Caption)
EnableWindow nHWind, 0&

End Function

Unfortunately InputBox's optional coordinates are not relative to the
screen
as it says in help, they can be calculated but probably not worth the
effort, something rough as above normally be OK. The API to restore a
modal
form to modal when done (you won't see that in help!).

Another FWIW, I don't use type:8 as above but the formula method to
avoid a
bug with particular CF's

Regards,
Peter T


"Pflugs" wrote in message
...
I am using an InputBox to obtain a range reference instead of a
RefEdit
control (see my other posts for reasons). When I click a button to
launch
this InputBox, I hide the main userform. Then when the range is
obtained,
I
show the form again.

The problem is that the location of the userform is not saved; that
is, I
move it away from window center, hide it, show it, and the window is
back
in
the way. How can I adjust its position?

Also, can I use the same method to force the InputBox to appear in the
same
location as the userform?

Thanks,
Pflugs

The API is not involved in reactivating the userform, only the AppActivate.
What does the dot preceding .Caption refer to. If it's not 'With Me' remove
the dot, iow it should be the userform's caption.

Regards,
Peter T

"Pflugs" wrote in message
...
Thank you again, that has been very helpful.

The last minor issue (annoyance) is that my userform isn't being
reactivated
after the range is selected. I used the code that you sent in the
original
post:

' following only with a modal form
AppActivate .Caption
nHWind = FindWindowA("XLMAIN", Application.Caption)
EnableWindow nHWind, 0&

But, this doesn't seem to be working. I tried using the userform's name,
but that didn't seem to work, either. Any ideas?

Thanks,
Pflugs

"Peter T" wrote:

I implement along similar lines as your 'temp' etc, but can't think why
your
inputbox should suddenly disappear without a chance to use as normal
(SendKeys involved perhaps ?).

Forgot to mention need to cater for formula generated both by selecting
cells & manually typed, w/out the = and other quotes that creep in. Try
something like the following (this is stripped down from a much larger
function & options used in a dll, hope no errors have crept in while
editing
for this post).

Function GetInputRange2(rng As Excel.Range, _
sPrompt As String, _
sTitle As String, _
Optional sDefault As String, _
Optional X, Optional Y, _
Optional bActivate As Boolean) As Boolean
Dim bGotRng As Boolean
Dim sAddr As String
Dim vReturn

On Error Resume Next
If Len(sDefault) = 0 Then
If TypeName(ActiveSheet) = "Chart" Then
sDefault = " first select a Worksheet"
ElseIf TypeName(Selection) = "Range" Then
sDefault = "=" & Selection.Address
Else
sDefault = " Select Cell(s)"
End If
End If

vReturn = Application.InputBox(sPrompt, sTitle, sDefault, X, Y,
Type:=0)

If Len(vReturn) And False < vReturn Then
sAddr = vReturn
' might be an idea to increase the 256 to 1024
If Left$(sAddr, 1) = "=" Then sAddr = Mid$(sAddr, 2, 256)
If Left$(sAddr, 1) = Chr(34) Then sAddr = Mid$(sAddr, 2, 255)
If Right$(sAddr, 1) = Chr(34) Then sAddr = Left$(sAddr,
Len(sAddr) -
1)

Set rng = Range(sAddr)

If rng Is Nothing Then
sAddr = Application.ConvertFormula(sAddr, xlR1C1, xlA1)
Set rng = Range(sAddr)
bGotRng = Not rng Is Nothing
Else
bGotRng = True
End If
End If

On Error GoTo errH
If bGotRng And bActivate Then
If Not ActiveWorkbook Is rng.Parent.Parent Then
rng.Parent.Parent.Activate
End If
If Not ActiveSheet Is rng.Parent Then
rng.Parent.Activate
End If
rng.Activate
End If

done:
GetInputRange2 = bGotRng
Exit Function
errH:
bGotRng = False
Resume done

End Function

Sub test2()
Dim rInput As Range

If GetInputRange2(rInput, "Prompt", "Title", "", , , True) Then
MsgBox rInput.Address(, , , True)
Else
MsgBox "not got a range"
End If

End Sub

Try selecting a range in different sheet and/or workbook to test the
bActivate option (may want an additional option to disable events).

Regards,
Peter T

"Pflugs" wrote in message
...
Peter,

Thanks for the hlep. That works well.

Regarding your comment on not using Type:=8, how do you implement the
formula method? Similar to this:?

temp = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=0)
temp = Application.ConvertFormula(temp, xlR1C1, xlA1)
temp = Replace(temp, "=", "")

Also, when I try to use this method, the inputbox disappears when it
is
launched. I can't see what I'm entering and may only blindly hit OK
or
CANCEL. Any ideas?

Thanks,
Pflugs

"Peter T" wrote:

Hi Pflugs,

You could trap the form's position coordinates and reset when you
reshow.
FWIW I don't totally hide the form but rsize to something small out
of
the
way, for ideas -

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function EnableWindow Lib "user32" ( _
ByVal hWnd As Long, ByVal bEnable As Long) As Long

Private Sub UserForm_Click()
Dim r As Range
If GetInputRng(r) Then
MsgBox r.Address
End If
End Sub
Function GetInputRng(rng As Range) As Boolean
Dim hWnd As Long
Dim lt As Single, tp As Single, ht As Single
Dim sDef As String

With Me
lt = .Left
tp = .Top
ht = .Height
..Top = 0
..Left = 0
..Height = 0
End With

If tp < 0 Then tp = 0
If lt < 0 Then lt = 0
On Error Resume Next
sDef = Selection.Address
Set rng = Application.InputBox("Prompt", "Title", sDef, lt, tp - 50,
Type:=8)
On Error GoTo 0
GetInputRng = Not rng Is Nothing

Me.Left = lt
Me.Top = tp
Me.Height = ht

' following only with a modal form
AppActivate Me.Caption
nHWind = FindWindowA("XLMAIN", Application.Caption)
EnableWindow nHWind, 0&

End Function

Unfortunately InputBox's optional coordinates are not relative to
the
screen
as it says in help, they can be calculated but probably not worth
the
effort, something rough as above normally be OK. The API to restore
a
modal
form to modal when done (you won't see that in help!).

Another FWIW, I don't use type:8 as above but the formula method to
avoid a
bug with particular CF's

Regards,
Peter T


"Pflugs" wrote in message
...
I am using an InputBox to obtain a range reference instead of a
RefEdit
control (see my other posts for reasons). When I click a button
to
launch
this InputBox, I hide the main userform. Then when the range is
obtained,
I
show the form again.

The problem is that the location of the userform is not saved;
that
is, I
move it away from window center, hide it, show it, and the window
is
back
in
the way. How can I adjust its position?

Also, can I use the same method to force the InputBox to appear in
the
same
location as the userform?

Thanks,
Pflugs