RE: Input Box Screen Position (VBA)

Topic

New Reply

I have written a VBA program which requires input into an InputBox. I would like the box to pop-up elsewhere on the screen eg top right hand corner. Is there an instruction I can include to position the box? Many thanks.

Farmer

Reply | Quote

Replies

I do not believe that there is any way to control the position of the InputBox. If you need to control the position, you will need to use a userform and set the Top and Left properties (possibly in the Initilize Event Routine).

Reply | Quote

I do not believe that there is any way to control the position of the InputBox. If you need to control the position, you will need to use a userform and set the Top and Left properties (possibly in the Initilize Event Routine).

Reply | Quote

You don’t mention which Office version you are using. In Office 2000 and Office 2002, VBA is based on VB6. The online help shows the syntax for InputBox:

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Optional arguments are in square brackets. The XPos and YPos coordinates are relative to the upper left corner of the screen and they are measured in twips; 1 inch = 1440 twips.

Example:

Dim strValue As String
strValue = InputBox(Prompt:=”Enter your name”, XPos:=1440, YPos:=720)

Reply | Quote

Hans, many thanks – it did the trick! I should have mentiond that I am using Office 2000.

Farmer

Reply | Quote

Note that if you want to position InputBox in upper right hand corner of screen, you need to take into account the screen resolution. The number of twips specified for the xpos argument will vary based on the screen’s horizontal resolution, which is measured in pixels. This example will position an InputBox in upper left hand corner, then upper right corner of screen, regardless of screen resolution (left hand corner is a no-brainer, set both position parameters to 0):

Public Sub TestInputBox()

Dim lngXPos As Long

‘ InputBox = appx 3.75 in wide (any resolution)=(5400 twips):
Const INPUTBOX_WIDTH As Long = 5400

‘ Multiply horizontal resolution (X) by Twips per Pixel, minus width of InputBox:
lngXPos = GetScreenX * TwipsPerPixelX – INPUTBOX_WIDTH

‘ Upper left hand corner:
InputBox “This is a test.”, “TEST”, , 0, 0
‘ Upper right hand corner:
InputBox “This is a test.”, “TEST”, , lngXPos, 0

End Sub

This sub relies on several Windows API functions to get the screen resolution and pixel size in twips (which can vary). The following API declarations are required:

Option Explicit

Private Declare Function GetSystemMetrics Lib “user32” (ByVal nIndex As Long) As Long

‘ System Metrics constants:
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1
‘ ————————————————————————————-
Declare Function GetDC Lib “user32” (ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib “user32” (ByVal hWnd As Long, ByVal hDC As Long) As Long
Declare Function GetDeviceCaps Lib “gdi32” (ByVal hDC As Long, ByVal nIndex As Long) As Long

Const HWND_DESKTOP As Long = 0
Const LOGPIXELSX As Long = 88
Const LOGPIXELSY As Long = 90
‘ ————————————————————————————-
‘ User defined:
Public Const TWIPS_PER_INCH As Long = 1440

User-defined functions to get horizontal & vertical screen resolution:

Public Function GetScreenX()
‘ Get screen resolution (width) in pixels:
GetScreenX = GetSystemMetrics(SM_CXSCREEN)
End Function

Public Function GetScreenY()
‘ Get screen resolution (height) in pixels:
GetScreenY = GetSystemMetrics(SM_CYSCREEN)
End Function

User-defined functions to determine pixel size in twips:

Public Function TwipsPerPixelX() As Single

‘ Determine width of pixel in twips:
Dim lngDC As Long
lngDC = GetDC(HWND_DESKTOP)
TwipsPerPixelX = TWIPS_PER_INCH / GetDeviceCaps(lngDC, LOGPIXELSX)
ReleaseDC HWND_DESKTOP, lngDC

End Function

Public Function TwipsPerPixelY() As Single

‘ Determine height of pixel in twips:
Dim lngDC As Long
lngDC = GetDC(HWND_DESKTOP)
TwipsPerPixelY = TWIPS_PER_INCH / GetDeviceCaps(lngDC, LOGPIXELSY)
ReleaseDC HWND_DESKTOP, lngDC

End Function

On my PC the standard InputBox was appx 3.75 inches (5400 twips) wide, while the pixel size was 15 x 15 twips, regardless of resolution. If necessary adjust the INPUTBOX_WIDTH constant value.

HTH

Reply | Quote

Note that if you want to position InputBox in upper right hand corner of screen, you need to take into account the screen resolution. The number of twips specified for the xpos argument will vary based on the screen’s horizontal resolution, which is measured in pixels. This example will position an InputBox in upper left hand corner, then upper right corner of screen, regardless of screen resolution (left hand corner is a no-brainer, set both position parameters to 0):

Public Sub TestInputBox()

Dim lngXPos As Long

‘ InputBox = appx 3.75 in wide (any resolution)=(5400 twips):
Const INPUTBOX_WIDTH As Long = 5400

‘ Multiply horizontal resolution (X) by Twips per Pixel, minus width of InputBox:
lngXPos = GetScreenX * TwipsPerPixelX – INPUTBOX_WIDTH

‘ Upper left hand corner:
InputBox “This is a test.”, “TEST”, , 0, 0
‘ Upper right hand corner:
InputBox “This is a test.”, “TEST”, , lngXPos, 0

End Sub

This sub relies on several Windows API functions to get the screen resolution and pixel size in twips (which can vary). The following API declarations are required:

Option Explicit

Private Declare Function GetSystemMetrics Lib “user32” (ByVal nIndex As Long) As Long

‘ System Metrics constants:
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1
‘ ————————————————————————————-
Declare Function GetDC Lib “user32” (ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib “user32” (ByVal hWnd As Long, ByVal hDC As Long) As Long
Declare Function GetDeviceCaps Lib “gdi32” (ByVal hDC As Long, ByVal nIndex As Long) As Long

Const HWND_DESKTOP As Long = 0
Const LOGPIXELSX As Long = 88
Const LOGPIXELSY As Long = 90
‘ ————————————————————————————-
‘ User defined:
Public Const TWIPS_PER_INCH As Long = 1440

User-defined functions to get horizontal & vertical screen resolution:

Public Function GetScreenX()
‘ Get screen resolution (width) in pixels:
GetScreenX = GetSystemMetrics(SM_CXSCREEN)
End Function

Public Function GetScreenY()
‘ Get screen resolution (height) in pixels:
GetScreenY = GetSystemMetrics(SM_CYSCREEN)
End Function

User-defined functions to determine pixel size in twips:

Public Function TwipsPerPixelX() As Single

‘ Determine width of pixel in twips:
Dim lngDC As Long
lngDC = GetDC(HWND_DESKTOP)
TwipsPerPixelX = TWIPS_PER_INCH / GetDeviceCaps(lngDC, LOGPIXELSX)
ReleaseDC HWND_DESKTOP, lngDC

End Function

Public Function TwipsPerPixelY() As Single

‘ Determine height of pixel in twips:
Dim lngDC As Long
lngDC = GetDC(HWND_DESKTOP)
TwipsPerPixelY = TWIPS_PER_INCH / GetDeviceCaps(lngDC, LOGPIXELSY)
ReleaseDC HWND_DESKTOP, lngDC

End Function

On my PC the standard InputBox was appx 3.75 inches (5400 twips) wide, while the pixel size was 15 x 15 twips, regardless of resolution. If necessary adjust the INPUTBOX_WIDTH constant value.

HTH

Reply | Quote

Hans, many thanks – it did the trick! I should have mentiond that I am using Office 2000.

Farmer

Reply | Quote

You don’t mention which Office version you are using. In Office 2000 and Office 2002, VBA is based on VB6. The online help shows the syntax for InputBox:

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Optional arguments are in square brackets. The XPos and YPos coordinates are relative to the upper left corner of the screen and they are measured in twips; 1 inch = 1440 twips.

Example:

Dim strValue As String
strValue = InputBox(Prompt:=”Enter your name”, XPos:=1440, YPos:=720)

Reply | Quote