Left & Top arguments in Application.InputBox Method

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,034
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
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

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

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

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

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

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
5,271
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Jon,
Thanks for providing this information. You have solved a 5-year old mystery for me! :)
 
Upvote 0

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi,
This thread helped me with a very annoying problem. I see now why my Input Box positioning was not “ working “....
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.......
( 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
.....
x = Cells(1, n).Left
y = Cells(n, 1).Top
A = Application.InputBox("Hello", Left:=x, Top:=y, Type:=1)
....

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

Forum statistics

Threads
1,187,001
Messages
5,961,049
Members
438,516
Latest member
Fintrics

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
Top