How to open a form to a better place?

Cyberseeker · #1 04-06-2017, 12:25 PM

Here is a macro that opens a form which appears in the center of my computer screen.

Code:

Sub OpenHarvestForm()
 
 Application.WindowState = xlMinimized
 frmHarvest.Show
 Application.WindowState = xlMaximized
 ActiveWorkbook.Sheets("GrpRecords").Activate
 
End Sub

My question is, can I add something to this code, so that my form automatically pops up in the bottom-right side of the window?

Logit · #2 04-06-2017, 01:41 PM

http://http://www.vbaexpress.com/kb/....php?kb_id=382

http://stackoverflow.com/questions/2...tartupposition

Cyberseeker · #3 04-06-2017, 03:45 PM

Hi Logit,

Can't get through to your first link. The stack article is related but I cant write it into my code. I tried (shown here in green) but it never worked.

Code:

Sub OpenHarvestForm()
 
 Application.WindowState = xlMinimized
 frmHarvest.Show
 Application.WindowState = xlMaximized
 Application.Right
 Application.Bottom
 ActiveWorkbook.Sheets("GrpRecords").Activate
 
End Sub

Can you suggest a line or two?

NoSparks · #4 04-06-2017, 04:23 PM

I don't think that's the place to put it.
Here's what I did for my 1600x900 screen.

In frmHarvest properties change StartUpPosition to 0 - Manual
In the forms code module add

Code:

Private Sub UserForm_Activate()
    Me.Top = Application.Height - frmHarvest.Height - 50
    Me.Left = Application.Width - frmHarvest.Width - 40
End Sub

play with the -50 and -40 for adjustment.

Logit · #5 04-06-2017, 04:50 PM

Code:

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = Application.Top + Application.Height - Me.Height
    Me.Left = Application.Left + Application.Width - Me.Width
End Sub

NoSparks · #6 04-06-2017, 05:09 PM

But UserForm_Initialize is used in the Submit button code as a reset of form textbox and combo values.
If the user has dragged the form to a different location they may not want it to relocate.

Cyberseeker · #7 04-06-2017, 05:22 PM

Not having much luck here.

It's OK. This was only a cosmetic thing anyway. I'll give it a miss.

NoSparks · #8 04-06-2017, 05:32 PM

Is this what you're after ?

Cyberseeker · #9 04-06-2017, 07:33 PM

It looks good NS. but it creates a different issue. I had the sheet minimise but with the form remaining on top. But I dont want you to spend too much time on me being fussy. No doubt Ill have other questions more important.

Thanks.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MS word 2007, capital letter after number (1. place turn to 1. Place)	enzo321	Word	1	10-23-2016 06:19 AM
Paste from one page to another doesnt place in the same place	MrShhh	Visio	0	09-21-2016 05:47 AM
editing a number/text at one place and changes taking place wherever it appears	anurag.butoliya	Word	1	06-14-2014 06:27 PM
Creating Form, inserting text box - getting things to stay in place?	SHC	Word	4	08-15-2013 09:18 AM
when open doc formatting all over the place	Anne	Word	1	11-30-2011 02:34 PM