Position dialog box on current sheet

I should add that since I'm working it's not Excel 2016 (that's at home) but instead Excel 2013.

Thank you for your response. I put that in, and I still get the dialog box coming up in the other monitor. I'd like to have it positioned right over the worksheet in which I have selected a cell. Is that something you can figure out? Thanks again.

Tongue in cheek.
Re: "Slow but willing new VBA learner"
You could start by reading the Forum Rules. #2 Is of interest to you.

I'm sorry. I looked at #2 but I don't understand how to comply. Would if I could...

[2. Programming code must be enclosed in code tags to improve readability. (A, Z)

 Your Code

(or use the # button)]

OK, Thank you. I saved that edit, and added to Leith Ross' reputation. Very kind.

I'll know better next time, hopefully (long time between requests).

So, does anybody know how to get that dialog box to appear where it can be easily found, preferably near the active cell in the active worksheet? Thanks in advance.

Since I didn't get a workable solution here, I continued to flounder around, searching online, and finally -- while trying various things in VBA -- got an Excel popup-hint for syntax showing that the 5000, 5000 referred to position. So after experimenting, I get an acceptable result by using 30000, 3000 -- in the correct monitor, on top of everything, and it doesn't throw it into debug. I really must take the time to actually learn VBA properly. Thank you all for your patience.

Thanks, Andy. I studied your website to understand, and downloaded the sample to try. Couldn't get it to work, but all the explanations seem to be there, so I'll come back after I've finished the VBA tutorial I'm taking as workload permits, and betcha it'll work then (I'm the problem, not the sample). Meanwhile, my workaround suffices; I can do it right later, when I'm more sophisticated.

Many thanks to all who helped, or even took time to consider the matter.

Originally Posted by Andy Pope

@DairyQueen, I wish that was my site

I'm not sure it's good for your reputation :-)
Download example from this site and tray to check result if application window is not full screen mode, change scale of sheet view ….

Hi DairyQueen

Here's my code for placing your InputBox exactly where you want it, using the active cell as the location for the top-left-corner of the InputBox.
It works with multiple screens as well as single screens.
It even works if you 'grab' the Excel window and re-size or move the Excel window around your display..

Sub CalcWhat()

'Set zCell = [G8]               '<< define anchor-cell to be used for position of InputBox
'or..
Set zCell = ActiveCell          'use current active cellpointer-cell for location

'The top-left-corner of the primary display is (0,0).
'For Excel position, negative values means left-of or above the primary screen.

'Top-left-corner position of cell [A1] in "points"; ( NOTE: 72points/inch)
'for Excel2016:
x = Application.Left + 14                                   '+14=fine-tune allowance for frame
y = Application.Top + CommandBars("Ribbon").Height + 2      '+2 =fine-tune-allowance for frame

'for Excel2010:
'x = Application.Left + 22                                   '+22=fine-tune allowance for frame
'y = Application.Top + CommandBars("Ribbon").Height - 8      '-8 =fine-tune-allowance for frame

'for Excel2007:
'x = Application.Left + 24                                   '+24=fine-tune allowance for frame
'y = Application.Top + CommandBars("Ribbon").Height - 3      '-3 =fine-tune-allowance for frame


a = zCell.Left              'position of left-edge of chosen cell (in points)
b = zCell.Top               'position of top-edge of chosen cell )in points)

zLeft = (x + a) / 72        'convert to inches; 72points= 1-inch); left-edge of chosen cell
zTop = (y + b) / 72         'convert to inches; 72points= 1-inch); top-edge of chosen cell

zLeft = zLeft * 1440        'now convert inches-to-twips (1440/inch)
zTop = zTop * 1440          'now convert inches-to-twips (1440/inch)

Application.Calculation = xlManual

zHeading = "Calculate What?"

saywhat = ""
saywhat = saywhat & "1 = Calculate A Used Range" & vbCr
saywhat = saywhat & "2 = Calculate This Worksheet" & vbCr
saywhat = saywhat & "3 = Calculate This Workbook" & vbCr
saywhat = saywhat & "4 = Calculate All Workbooks in Memory"
saywhat = saywhat & vbCr & vbCr
saywhat = saywhat & "Input Your Selection Number From Above" & vbCr

saywhat = saywhat & "Then Click OK" & vbCrLf

zDefault = "Input Number Please"

iAnsure = InputBox(saywhat, zHeading, zDefault, zLeft, zTop)

Select Case iAnsure
Case 1 'Range Only
Selection.Calculate
Case 2 'Worksheet Only
ActiveSheet.Calculate
Case 3 'Workbook Only
For Each wks In ActiveWorkbook.Worksheets
wks.Calculate
Next
Case 4 'All Open Workbooks
Application.CalculateFull
End
Case Else
'do nothing
End Select
End Sub

In the attached file, place your cellpointer in the cell where you want to see the InputBox, then click the button to display it (don't forget to choose a calc option or you will be left with calcs set to Manual)

I have tested this on my Excel2016. Move the cellpointer around and keep testing etc etc etc.

For my Excel2010 and Excel2007, I needed to make slight adjustments for fine-tuning the exact cellpointer location .
I left these adjustments in the code, for the benefit of others using those Excel versions.
Note that you cannot use this with Excel2003 (it doesn't have a "Ribbon").

Also, this assumes that you aren't messing around with zoom-display settings, and you are displaying the Excel frame for row-column etc etc etc.

zeddy

zzzeddy, once again,
ZOOM is not calculated
Second display is not included

Hi BMV

this assumes that you aren't messing around with zoom-display settings

So, you tried it on a multi-display then????
Works OK on my multi-monitor systems..

It works with multiple screens as well as single screens.
It even works if you 'grab' the Excel window and re-size or move the Excel window around your display..

For example, if your primary display is in front of you, with a secondary display on your left, you could open an Excel window and drag it to the left screen, then load the file. It will show the InputBox on the same screen as your Excel window. You could then drag your Excel window to your primary screen, and the InputBox would still display on the correct screen etc etc etc

I could fix the system to deal with zoom settings, but I thought I'd leave that to someone else..

zeddy

Thank you; works great on your file; still struggling to get it working on mine. This is due to the fact that I'm only now embarking on the VBA tutorials and lack the background knowledge. Not fooling with zoom so far as I know.

Debug doesn't like the line saying iAnsure = InputBox(waywhat, zHeading, zDefault, zLeft, zTop). I tried running the macro starting with both automatic and manual calculation and -- besides failing -- it seems to revert to manual. Then I put a shortcut key on it and it still doesn't work.

Hope the IT people at work don't drop the tiger net on me for downloading your file.

Hi DairyQueen

iAnsure = InputBox(waywhat, zHeading, zDefault, zLeft, zTop).

Rule1: Check your spellings!

try..
iAnsure = InputBox(saywhat, zHeading, zDefault, zLeft, zTop).

zeddy

Found that if I move the screen onto another monitor, your file doesn't work there. Move it back to monitor upon which first opened, and it's fine

Also, how do I install the button onto my file? It's this basic knowledge that I lack ...

Hi DairyQueen

If you get this working, your IT people will be delighted to know how to exactly position an InputBox exactly where you tell it to..
..bet they couldn't do that.

..tell them next time you'll send the file as an attachment to your work email - then the business virus-scanners would have checked it was OK

zeddy

Hi DairyQueen

I tested the file with my multi-monitor system and whichever screen my Excel opens on, that is where the InputBox is shown.
I even dragged Excel between screens and it still followed with wherever Excel was.

You can insert a shape with Excel2016 by selecting Insert in the top-panel Ribbon, then choosing Shapes in the [Illustrations] section.
For example, pick a rectangle shape, use your mouse with the left-button down as you 'draw' the shape etc etc etc.

You can assign any macro to any 'shape' or 'picture' or 'button' by right-clicking on the shape and choosing Assign Macro from the right-click-menu

zeddy

... And the spelling in the vba is actually ok (never changed it); just couldn't copy and paste from VBA screen so I clumsily recreated it.

Found that if your file is closed, your vba doesn't show up in my file. Think I'll try copying your module into my workbook and see if the button appears... Tried; can't figure out how to do that.

And by "zoom" I hope you don't mean having the window re-sized to be less than full monitor screen? If so, I'm guilty.

You can re-size the Excel window to be less than full-screen, that's OK and mostly how I have it.
Zoom refers to the 'magnification' you can set within Excel, using the top-panel Ribbon View>Zoom and choosing a zoom% there.

Simplest way for you to copy code between workbooks is to use copy-and-paste

zeddy

Note that if you have
Option Explicit
..at the top of your vba module, you must declare all variables used in any subroutine.

Please let me know if this is the case and I'll re-write the code with declared variables.

zeddy

Don't understand that part. Is there a way to send you my file -- you and only you?

It's not proprietary data that anybody could use, it's just my notes on how to process a very complicated report.

If this can be made to work for all open workbooks, I would use it in other files; their size is why we're using manual calculation.

Since actually doing the report doesn't use all of my time, I'm training myself by improving the process document.
When my contract ends here, I'd be chuffed if whoever takes over the report could do so smoothly. I've started an online tutorial in VBA meanwhile.

Many thanks for your patience and persistence.

Hi DairyQueen

..I sent you a PrivateMessage in this Forum.
I don't think you can send attachments in a PM - unless the Forum Experts say you can????

zeddy

Zoom

a = zCell.Left * ActiveWindow.Zoom / 100  'position of left-edge of chosen cell (in points)
b = zCell.Top * ActiveWindow.Zoom / 100   'position of top-edge of chosen cell )in points)

For multy display system (Wind10-Office 2013) I have tested and if application start on the second display input box appeared on the primary.

Hi BMV

Curious. Is your multi-display an 'extended desktop'??? Can you 'drag' an Excel window between the displays????

zeddy

zzzeddy, yes. I can make screenshot tomorrow.
The other negative point is the size of input box is not predefined and if active cell near of right/bottom edge then part of input box can be out of screen. In this case Form is better.