Hi there,

I have used this newsgroup a number of times now, and
people have always been very helpful.

My next question, :)

I have a macro which opens up a Data / Form dialogue box
on a different sheet to where the macro is called from.

The macro is a user input form and I have used a
different sheet so I can display instructions for the
user to refer to.

When the dialogue box opens, its initial position seems
random. I need it to display in the top-center of the
sheet so I can wrap guidance around it.

I can position the box myself, and then when I close it
and call it again from the macro, it remembers that
position, but it is the initial call when the spreadsheet
is opened which fails to position it where I need it.

I could give the users guidance how to position it but
would rather not.

Is there any way I can force an absolute display position
when I call it?

Many thanks in advance.

Mark Flaxman

Ohh dear no replies.

Looks like I cannot set the position of dialogue boxes
and I will have to think again.



-----Original Message-----

Hi there,

I have used this newsgroup a number of times now, and
people have always been very helpful.

My next question, :)

I have a macro which opens up a Data / Form dialogue box
on a different sheet to where the macro is called from.

The macro is a user input form and I have used a
different sheet so I can display instructions for the
user to refer to.

When the dialogue box opens, its initial position seems
random. I need it to display in the top-center of the
sheet so I can wrap guidance around it.

I can position the box myself, and then when I close it
and call it again from the macro, it remembers that
position, but it is the initial call when the
spreadsheet
is opened which fails to position it where I need it.

I could give the users guidance how to position it but
would rather not.

Is there any way I can force an absolute display
position
when I call it?

Many thanks in advance.

Mark Flaxman

.

Mark,

Are you looking to position a UserForm, a Dialog Box, or a Data Form? If
it's a UserForm, you could do something like this:

Private Sub UserForm_Activate()
With Application
Move (.UsableWidth / 2) - (Width / 2), _
.Height - .UsableHeight
End With
End Sub

This won't get it exactly where you want, as it depends on how tall the
user's menu bars are. But it should give you a good start. If you want the
code to "remember" where the user last left it (even after
closing/reopening), you could use an INI file, the registry, or store the
top and left values in a hidden sheet or name.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mark Flaxman wrote:
Ohh dear no replies.

Looks like I cannot set the position of dialogue boxes
and I will have to think again.



-----Original Message-----

Hi there,

I have used this newsgroup a number of times now, and
people have always been very helpful.

My next question, :)

I have a macro which opens up a Data / Form dialogue box
on a different sheet to where the macro is called from.

The macro is a user input form and I have used a
different sheet so I can display instructions for the
user to refer to.

When the dialogue box opens, its initial position seems
random. I need it to display in the top-center of the
sheet so I can wrap guidance around it.

I can position the box myself, and then when I close it
and call it again from the macro, it remembers that
position, but it is the initial call when the spreadsheet
is opened which fails to position it where I need it.

I could give the users guidance how to position it but
would rather not.

Is there any way I can force an absolute display position
when I call it?

Many thanks in advance.

Mark Flaxman

.

Sorry m8, I had almost given up hope.

It is a DataForm called up from a macro using this VBA
code:

VBA code for OpenDataForm

Sub OpenDataForm()

Dim ws As Worksheet
Set ws = Worksheets("Record")

SendKeys "%w"
ws.Range("Database").Worksheet.ShowDataForm
End Sub


Where "Database" is a named range using a dynamic range
of cells. The user will input 8 lines of information
which will be entered into the next empty row in the
range.

Scroll bars are turned off, as are Sheet Tabs, and column
and row headers, and when the user saves the spreadsheet
prior to exiting, (called from a macro), the spreadsheet
maximises, so that, when it is opened by another user, it
opens full screen. That way, the GUI dimensions remain
static.

So, could I use your code for a DataForm instead of a
UserForm?

If so, can you explain your code? My VBA isn't good.

Where do I enter the values?

Many thanks for replying.

Mark Flaxman