VBE Userform position


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 13th November 2012, 16:36
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 66
Posts: 568
Posts Per Day: 0.24
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (14) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default VBE Userform position
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

Hi everyone,

The problem...
Form positioning in the VBE doesn't work as expected.

There is a thread running on Eileenslounge started by me about this so it's a bit cross posted. I started the thread after googling and getting advice from Jan Karel Pieterse.

This is another Lisas' VBA project problem.

I'm trying to save the position of the userform on exit to an INI file and then position the form in the same place on startup. Also.. those who have looked at the project know there are three "states" for the userform to be in and I'm trying to do the same with that. Aaalso... when changing this state I'd like the top left of the form to stay where it is.

The attached XLSM shows the problem. Starting up the userform on my system and hard coding the TOP an LEFT properties to 10. After setting them and displaying them... at least one of them ain't 10 any more. This happens in the VBE. It doesn't happen if I display the userform in the application.

So...
Any ideas as to how to set the position of the userform in the VBE when it starts up please?

Very many TIAs
Hugs
Lisa
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.



The Code Cage Advertisment
Advertisement
  #2  
Old 13th November 2012, 17:10
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 53
Posts: 11,884
Posts Per Day: 3.48
My Top Tip Count: 11
Thanks to others: 49
Thanked 442 Times in 426 Posts
Reputation: 6670 (194) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


So this is where you found out about writing settings to an .ini file Save Settings as a thought can you not set a screen reference point during workbook_open event and use that variable for your form positioning (i have to say i haven't studied the issue at all).
__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!





  #3  
Old 13th November 2012, 18:05
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 66
Posts: 568
Posts Per Day: 0.24
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (14) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


ummmm.... well, *one* of the places I've scrounged from anyway... LOL.

I've coded things in my INI code to retain any comments at the end of lines when writing values back and I use the comments in some cases to get lists of options. Code is in the add in...

I prefer using an INI file to writing to the registry. IMHO stuffing the registry without permission or nescesity is downright rude! Anyway... If it's good enough for MZTools it's good enough for me.

I have in the past used the VBA part of of the registry for settings thiough. There's a key set aside for "users" and a set of built in functions to read and write.

It's actually in the back of my mind to add a tab to edit the INI settings eventually.

The behaviour of the userform *in the VBE* is quite strange. It's gotten to where if I swap...
Me.Top = 10
Me.Left = 10
to...
Me.Left = 10
Me.Top = 10
... I get different results! But I have a sneaking suspicion about where that comes from. It doesn't happen in the sample XLSM and that's a big clue.

But to set a property and then read it as being something else is flummoxing me.

If you or anyone can throw some light I'd be greatly relieved Simon. It would also save me from going to the hairdresser for some new hair!!!

Hope you're doing okay...

Hugs
Lisa



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #4  
Old 13th November 2012, 19:21
snb's Avatar
snb snb is offline
Forum VIP
MS Office Version: > Office 97
MS Office Skill Level: Interested
 
Join Date: Feb 2011
Location: Europe
Posts: 1,393
Posts Per Day: 0.56
My Top Tip Count: 0
Thanks to others: 1
Thanked 314 Times in 307 Posts
Reputation: 5076 (70) snb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond repute
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


I don't see your problem...

See the attachment for a simple approach
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.



  #5  
Old 13th November 2012, 20:44
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 53
Posts: 11,884
Posts Per Day: 3.48
My Top Tip Count: 11
Thanks to others: 49
Thanked 442 Times in 426 Posts
Reputation: 6670 (194) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


Quote:
Originally Posted by snb View Post

I don't see your problem...

See the attachment for a simple approach

snb, thats not Lisa's issue, it's the positioning when the form is called while in the vbe, one of the forms that pops up IN the vbe isn't retaining it's position which is why she was writing to the ini file
__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!





  #6  
Old 13th November 2012, 21:59
snb's Avatar
snb snb is offline
Forum VIP
MS Office Version: > Office 97
MS Office Skill Level: Interested
 
Join Date: Feb 2011
Location: Europe
Posts: 1,393
Posts Per Day: 0.56
My Top Tip Count: 0
Thanks to others: 1
Thanked 314 Times in 307 Posts
Reputation: 5076 (70) snb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond repute
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


It keeps rather stable using in
Private Sub CommandButton1_Click()

VBA CODE:
 
Me.Top = 10 
Me.Left = 10 
 
instead of 
 'Me.StartUpPosition = 0
 'Me.Top = ilTop
 'Me.Left = ilLeft



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #7  
Old 14th November 2012, 14:02
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 66
Posts: 568
Posts Per Day: 0.24
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (14) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


Hi SNB,

But .... it's not "stable" in the VBE... :-(

Lisa



  #8  
Old 14th November 2012, 15:14
Rory's Avatar
Rory Rory is offline
Solution Centre Professional
MS Office Version: 2003 onwards
MS Office Skill Level: Competent technician
 
Join Date: Feb 2010
Location: Ubique
Age: 27
Posts: 871
Posts Per Day: 0.31
My Top Tip Count: 2
Thanks to others: 0
Thanked 112 Times in 109 Posts
Reputation: 1963 (35) Rory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant future
2 Top Tips
Northern_Ireland
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


For me it's stable after the first button click if you use a literal value for top and left, so if your activate event calls the button_click code, it seems to be static thereafter. It seems as though the initial top and left are relative to the top left of the whole VBE but after that it's relative to the main window below the command bars.
__________________
Rory
Microsoft MVP - Excel

Always listen to advice, even if you don't take it.



  #9  
Old 14th November 2012, 16:00
LisaGreen's Avatar
LisaGreen LisaGreen is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use VBA
 
Join Date: Jun 2011
Location: Houten, Nederlands
Age: 66
Posts: 568
Posts Per Day: 0.24
My Top Tip Count: 1
Thanks to others: 9
Thanked 18 Times in 16 Posts
Reputation: 270 (14) LisaGreen is a jewel in the roughLisaGreen is a jewel in the roughLisaGreen is a jewel in the rough
1 Top Tips
Great_Britain
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


Hey Rory,

From Jan Karel Pieterse...
Eileen's Lounge • View topic - VBA Userform Position

UserForm1.Left = (dCurLeft-22.5) - Application.VBE.MainWindow.Left * 0.75
UserForm1.Top = (dCurTop-12) - Application.VBE.MainWindow.Top * 1.5


... and from HansV...
Sorry... Lost the link but it's in that thread.

' ***** Adjust position of form *****
Me.Top = ilTop - 1.5 * Application.VBE.MainWindow.Top - 45
Me.Left = ilLeft - 0.75 * Application.VBE.MainWindow.Left - 6


What do you mean by "literal value" please Rory?

Hugs
Lisa



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #10  
Old 14th November 2012, 16:11
Rory's Avatar
Rory Rory is offline
Solution Centre Professional
MS Office Version: 2003 onwards
MS Office Skill Level: Competent technician
 
Join Date: Feb 2010
Location: Ubique
Age: 27
Posts: 871
Posts Per Day: 0.31
My Top Tip Count: 2
Thanks to others: 0
Thanked 112 Times in 109 Posts
Reputation: 1963 (35) Rory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant futureRory has a brilliant future
2 Top Tips
Northern_Ireland
Default Re: VBE Userform position

------ Register to get rid of these "In Post" ads! ------


I mean simply using the same values you used initially - e.g. as snb showed
VBA CODE:
me.top = 10 
although that will stop the user from dragging the form around
__________________
Rory
Microsoft MVP - Excel

Always listen to advice, even if you don't take it.



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
position, userform, vbe

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 04:40.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.21159 seconds
  • Memory Usage 6,695KB
  • Queries Executed 16 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (5)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (2)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (10)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (2)pagenav_pagelink
  • (10)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (2)postbit_attachment
  • (10)postbit_ctoptip
  • (10)postbit_onlinestatus
  • (78)postbit_reputation
  • (10)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (3)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • error_fetch
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete