Positioning UserForms, Input & Msg Boxes

[Pflugs]

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

 

[Peter T]

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

 

[Jim Cone]

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?

 

[Peter T]

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

 

[Pflugs]

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]

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]

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]

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

 

[Jim Cone]

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

 

[Peter T]

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]

Peter,
Yes there is also trouble here in River City. <g>
Thanks for providing the links.
Regards,
Jim Cone



"Peter T" <peter_t@discussions>
wrote in message
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