Left & Top arguments in Application.InputBox Method

J

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,218
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I wrote a few lines of code that I thought would demonstrate how the left and top arguments affect the position of the InputBox. To my surprise, the box does not change position as the code executes. The code is below - what am I missing?

Sub PositionInputBoxTest()
Do Until n = 11
n = n + 1
x = Cells(1, n).Left
y = Cells(n, 1).Top
A = Application.InputBox("Hello", Left:=x, Top:=y, Type:=1)
Loop
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Actually, your code does work. You are using an input box which means each instance of your loop fires when you click on the cancel box or the OK box after putting in a number. Each time, the box will shift to the right as your code specifies.
 
Upvote 0
If you want to see a box move on the sheet, add a rectangle (Rectangle 1) and a command button. Put your modified code inthe command button sub.

Code: 
Private Sub CommandButton1_Click()
Do Until n = 11
n = n + 1
x = Cells(1, n).Left
y = Cells(n, 1).Top

With ActiveSheet.Shapes("Rectangle 1")
.Left = x
.Top = y

End With
Loop
End Sub
 
Upvote 0
hilyete said:
Actually, your code does work. You are using an input box which means each instance of your loop fires when you click on the cancel box or the OK box after putting in a number. Each time, the box will shift to the right as your code specifies.
Click to expand...

As I said in the initial post, I do not observe any change in position of the box each time the loop fires. If this works for you, why not for me?
 
Upvote 0
That's interesting Joe.. I don't know why it wouldn't work on yours. The code is straight forward. I'm using 2003. It works fine on mine.
 
Upvote 0
hilyete said:
That's interesting Joe.. I don't know why it wouldn't work on yours. The code is straight forward. I'm using 2003. It works fine on mine.
Click to expand...

I'm using Excel 2007, but there's virtually no difference between 2003 and 2007 from a VBA perspective. Anyway, thanks for letting me know the problem is not with my code. I'll just add it to my list of Excel anomalies.
 
Upvote 0
Application.InputBox followed the Top and Left instructions up to Excel 2003, but in 2007 it started ignoring them, and still does in Excel 2010 and 2013.

Somebody posted a Windows API approach to position the InputBox here: Excel InputBox position works in 2003, but not 2007

I haven't tested it, but I repeat it here:

Code: 
Private Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" (ByVal idHook As Long, _
ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
 
Private Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
ByVal x As Long, ByVal y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long
 
'~~> Handle to the Hook procedure
Private hHook As Long

'~~> Hook type
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
 
'~~> SetWindowPos Flags
Private Const SWP_NOSIZE = &H1      '<~~ Retains the current size
Private Const SWP_NOZORDER = &H4    '<~~ Retains the current Z order

Dim InputboxTop As Long, InputboxLeft As Long

Sub TestInputBox()
    Dim stringToFind As String, MiddleRow As Long, MiddleCol As Long
    
    hHook = SetWindowsHookEx(WH_CBT, _
    AddressOf MsgBoxHookProc, 0, GetCurrentThreadId)
    
    '~~> Get the center cell (keeping the excel menus in mind)
    MiddleRow = ActiveWindow.VisibleRange.Rows.Count / 1.2
    '~~> Get the center column
    MiddleCol = ActiveWindow.VisibleRange.Columns.Count / 2
    
    InputboxTop = Cells(MiddleRow, MiddleCol).Top
    InputboxLeft = Cells(MiddleRow, MiddleCol).Left
    
    '~~> Show the InputBox. I have just used "Sample" Change that...
    stringToFind = Application.InputBox("Sample", _
    "Sample", "Sample", InputboxLeft, InputboxTop, , , 2)
End Sub
 
Private Function MsgBoxHookProc(ByVal lMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
    
    If lMsg = HCBT_ACTIVATE Then
        '~~> Change position
        SetWindowPos wParam, 0, InputboxLeft, InputboxTop, _
        0, 0, SWP_NOSIZE + SWP_NOZORDER
 
        '~~> Release the Hook
        UnhookWindowsHookEx hHook
    End If
 
    MsgBoxHookProc = False
End Function
 
Upvote 0
Jon Peltier said:
Application.InputBox followed the Top and Left instructions up to Excel 2003, but in 2007 it started ignoring them, and still does in Excel 2010 and 2013.

Somebody posted a Windows API approach to position the InputBox here: Excel InputBox position works in 2003, but not 2007

I haven't tested it, but I repeat it here:

Code: 
Private Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" (ByVal idHook As Long, _
ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
 
Private Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
ByVal x As Long, ByVal y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long
 
'~~> Handle to the Hook procedure
Private hHook As Long

'~~> Hook type
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
 
'~~> SetWindowPos Flags
Private Const SWP_NOSIZE = &H1      '<~~ Retains the current size
Private Const SWP_NOZORDER = &H4    '<~~ Retains the current Z order

Dim InputboxTop As Long, InputboxLeft As Long

Sub TestInputBox()
    Dim stringToFind As String, MiddleRow As Long, MiddleCol As Long
    
    hHook = SetWindowsHookEx(WH_CBT, _
    AddressOf MsgBoxHookProc, 0, GetCurrentThreadId)
    
    '~~> Get the center cell (keeping the excel menus in mind)
    MiddleRow = ActiveWindow.VisibleRange.Rows.Count / 1.2
    '~~> Get the center column
    MiddleCol = ActiveWindow.VisibleRange.Columns.Count / 2
    
    InputboxTop = Cells(MiddleRow, MiddleCol).Top
    InputboxLeft = Cells(MiddleRow, MiddleCol).Left
    
    '~~> Show the InputBox. I have just used "Sample" Change that...
    stringToFind = Application.InputBox("Sample", _
    "Sample", "Sample", InputboxLeft, InputboxTop, , , 2)
End Sub
 
Private Function MsgBoxHookProc(ByVal lMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
    
    If lMsg = HCBT_ACTIVATE Then
        '~~> Change position
        SetWindowPos wParam, 0, InputboxLeft, InputboxTop, _
        0, 0, SWP_NOSIZE + SWP_NOZORDER
 
        '~~> Release the Hook
        UnhookWindowsHookEx hHook
    End If
 
    MsgBoxHookProc = False
End Function
Click to expand...
Jon,
Thanks for providing this information. You have solved a 5-year old mystery for me! :)
 
Upvote 0
Hi,
This thread helped me with a very annoying problem. I see now why my Input Box positioning was not “ working “....
Jon Peltier said:
Application.InputBox followed the Top and Left instructions up to Excel 2003, but in 2007 it started ignoring them, and still does in Excel 2010 and 2013.......
Click to expand...
( Currently I am using XL2007 ).. and maybe i can add a little that could be useful for others hitting this Thread in a search:

I was using / needing wanting / to take in a range using this type of command:
Code: 
[color=blue]Dim[/color] RngIn [color=blue]As[/color] Range
[color=blue]Set[/color] RngIn = Application.InputBox(prompt:="Select ref Range", Title:="Ref RangeIn", Default:="A17388", Left:=posLeft, Top:=posTop, Type:=8) 
[color=blue]Dim[/color] strRange [color=blue]As[/color] [color=blue]String[/color]

As reported in this Thread the “Top” and “Left” Arguments are not appearing to “Function” In the Application InputBox Method for XL 2007 +

But I did note that the ( unqualified ) InputBox Function appears to “Function” in its “Top” and “Left” Arguments.
This will “work” ( It only supports a string, and does not allow me to take in a Range Object. Also i do not quite follow the logic to the positioning yet, - Maybe that must be experimented a bit every time! )
Code: 
[color=blue]Let[/color] strRange = InputBox("Type in ref Range", "string of RangeIn", "A17388", posLeft, posTop) [color=darkgreen]'InputBox with no object qualifier calls the InputBox Function   http://stackoverflow.com/questions/13275692/application-inputbox-left-and-top-not-working-in-excel-vba-why[/color]
[color=blue]Set[/color] RngIn = ws.Range("" & strRange & "") [color=darkgreen]'Remember to qualify Range with Worksheet for this String Input Option[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

Here a complete test Code to try:
Code: 
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] InputBoxText1() '
Rem 1) [color=darkgreen]'Workbook Infoinfo. CHANGE TO SUIT YOUR Workbook and sheet[/color]
[color=blue]Dim[/color] WB [color=blue]As[/color] Workbook: [color=blue]Set[/color] WB = Workbooks("ProAktuellex8600x2.xlsm")
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = WB.Worksheets("Sheet1")
ws.Cells(1, 1).Select [color=darkgreen]'Found in practice that this seemingly unecerssary step sometimes illiminates strange inconsistant errors[/color]
Rem 2) [color=darkgreen]'Select Range from.[/color]
[color=blue]Dim[/color] posTop [color=blue]As[/color] [color=blue]Long[/color], posLeft [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Let[/color] posTop = ws.Cells(10, 10).Top: [color=blue]Let[/color] posLeft = ws.Cells(10, 10).Left [color=darkgreen]'Cell( 1, 1 ).Top/.Left gives 0.[/color]
[color=darkgreen]'http://www.mrexcel.com/forum/excel-questions/447043-left-top-arguments-application-inputbox-method.html   'Application InputBox Method Left Top arguments do not work xl2007 +   Jon Peltier[/color]
[color=darkgreen]'Application.InputBox followed the Top and Left instructions up to Excel 2003, but in 2007 it started ignoring them, and still does in Excel 2010 and 2013.[/color]
[color=blue]Dim[/color] RngIn [color=blue]As[/color] Range
[color=blue]Set[/color] RngIn = Application.InputBox(prompt:="Select ref Range", Title:="Ref RangeIn", Default:="A17388", Left:=posLeft, Top:=posTop, Type:=8)
[color=blue]Dim[/color] strRange [color=blue]As[/color] [color=blue]String[/color]
[color=darkgreen]'http://stackoverflow.com/questions/13275692/application-inputbox-left-and-top-not-working-in-excel-vba-why[/color]
[color=blue]Let[/color] strRange = InputBox("Type in ref Range", "string of RangeIn", "A17388", posLeft, posTop) [color=darkgreen]'InputBox with no object qualifier calls the InputBox Function   http://stackoverflow.com/questions/13275692/application-inputbox-left-and-top-not-working-in-excel-vba-why[/color]
[color=blue]Set[/color] RngIn = ws.Range("" & strRange & "") [color=darkgreen]'Remember to qualify Range with Worksheet for this String Input Option[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

Alan

_...........................................................................
P.s.
Oh and BTW – this was a nice “positional idea Tip also
JoeMo said:
.....
x = Cells(1, n).Left
y = Cells(n, 1).Top
A = Application.InputBox("Hello", Left:=x, Top:=y, Type:=1)
....
Click to expand...

P.P.s.
Unfortunately i do not understand the codes given in Post #7 and could not get them to “work”. Running the
Sub TestInputBox()
seems to give a displayed Input Box in the same position, regardless of if I change the positional Arguments in the Application,InputBox test Line . So it appears not to give any “Functioning” positioning?. Anyone coincidentally tried this and know how/ if it should “work”? - I do note that the code as given will not run at all using Option Explicit, and the “Thing” GetCurrentThreadId does not appear to be defined.
Using the code in the reference given in Post # 7 here,
[RESOLVED] Excel InputBox position works in 2003, but not 2007-VBForums
does appear to work. The code is very similar, but a bit different to that given in Post #7 here.

I have done a modified version to tie up with my example which appears to work at “my End”. To Demo this code, copy all codes complete to a module, then Run
Sub InputBoxSiddharthRoutAlan()
_ .. and experiment a bit with different “posTop” and “posLeft” and run again etc.

Code: 
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=darkgreen]' Siddharth Rout   http://www.vbforums.com/showthread.php?617519-RESOLVED-Excel-InputBox-position-works-in-2003-but-not-2007[/color]
[color=darkgreen]'...."....What I have done is I have 'Hooked' the input box and then changed it's position...."  ??[/color]
[color=blue]Private[/color] [color=blue]Declare[/color] [color=blue]Function[/color] UnhookWindowsHookEx [color=blue]Lib[/color] "user32" ([color=blue]ByVal[/color] hHook [color=blue]As[/color] [color=blue]Long[/color]) [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Private[/color] [color=blue]Declare[/color] [color=blue]Function[/color] GetCurrentThreadId [color=blue]Lib[/color] "kernel32" () [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Private[/color] [color=blue]Declare[/color] [color=blue]Function[/color] SetWindowsHookEx [color=blue]Lib[/color] "user32" Alias "SetWindowsHookExA" ([color=blue]ByVal[/color] idHook [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] lpfn [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] hmod [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] dwThreadId [color=blue]As[/color] [color=blue]Long[/color]) [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Private[/color] [color=blue]Declare[/color] [color=blue]Function[/color] SetWindowPos [color=blue]Lib[/color] "user32" ([color=blue]ByVal[/color] hwnd [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] hWndInsertAfter [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] x [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] y [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] cx [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] cy [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] wFlags [color=blue]As[/color] [color=blue]Long[/color]) [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Private[/color] hHook [color=blue]As[/color] Long [color=darkgreen]' Handle to the Hook procedure[/color]
[color=blue]Private[/color] [color=blue]Const[/color] WH_CBT = 5 [color=darkgreen]'Hook type[/color]
[color=blue]Private[/color] [color=blue]Const[/color] HCBT_ACTIVATE = 5 'Hook type
[color=blue]Private[/color] [color=blue]Const[/color] SWP_NOSIZE = &H1      [color=darkgreen]'SetWindowPos Flags-  Retains the current size[/color]
[color=blue]Private[/color] [color=blue]Const[/color] SWP_NOZORDER = &H4    [color=darkgreen]'SetWindowPos Flags- Retains the current Z order[/color]
[color=blue]Dim[/color] posTop [color=blue]As[/color] Long, posLeft [color=blue]As[/color] Long
[color=darkgreen]'[/color]
[color=blue]Private[/color] [color=blue]Function[/color] MsgBoxHookProc([color=blue]ByVal[/color] lMsg [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] wParam [color=blue]As[/color] [color=blue]Long[/color], [color=blue]ByVal[/color] lParam [color=blue]As[/color] [color=blue]Long[/color]) [color=blue]As[/color] Long
    [color=blue]If[/color] lMsg = HCBT_ACTIVATE [color=blue]Then[/color]
    SetWindowPos wParam, 0, posLeft, posTop, 0, 0, SWP_NOSIZE + SWP_NOZORDER [color=darkgreen]'Change position[/color]
    UnhookWindowsHookEx hHook [color=darkgreen]'Release the Hook[/color]
    [color=blue]End[/color] [color=blue]If[/color]
 MsgBoxHookProc = [color=blue]False[/color]
[color=blue]End[/color] [color=blue]Function[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] InputBoxSiddharthRoutAlan() '
Rem 1) [color=darkgreen]'Workbook Info. CHANGE TO SUIT YOUR Workbook and sheet'[/color]
[color=blue]Dim[/color] WB [color=blue]As[/color] Workbook: [color=blue]Set[/color] WB = Workbooks("ProAktuellex8600x2.xlsm")
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = WB.Worksheets("Sheet1")
Rem 2) [color=darkgreen]'Select Range from.[/color]
[color=blue]Let[/color] hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, 0, GetCurrentThreadId)
posTop = 100: posLeft = 10 [color=darkgreen]'EXPERIMENT a bit with different Pos![/color]
[color=blue]Dim[/color] RngIn [color=blue]As[/color] Range
[color=blue]Set[/color] RngIn = Application.InputBox(prompt:="Select ref Range", Title:="Ref RangeIn", Default:="A17388", Left:=posLeft, Top:=posTop, Type:=8)
[color=blue]End[/color] [color=blue]Sub[/color]


I confess i cannot understand the code well enough to explain it so will not use it ( yet ) myself. ( I shall probably stay with the InputBox Function for now ). But it could be useful for someone.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Solved
VBA: Dynamically Create Active X Frame and Yes / No Radio Button
2
Replies
13
Views
172
Domenic
D
M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
274
myall_blues
myall_blues
Y
  • Question Question
Using Offset to select negative cell range using variables
Replies
1
Views
389
Yamezz
Y
T
  • Solved
Search for & Count Blank Cells of a certain color based on Y/N dropdown menus
2 3
Replies
21
Views
767
myall_blues
myall_blues
edge37
  • Solved
Toggle hide/unhide columns without affecting tables or text boxes on top
Replies
4
Views
73
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top