Hi Crystal,
Here is the original message I posted back on Friday (Thursday your time):
Hi Stuart
Below are two functions (and a whole bunch of supporting code) to return you the width of the nav pane and the height of the ribbon (note that this includes the QAT and the title bar!)
Just call GetNavPaneWidth() and GetRibbonHeight()
All the best,
Graham
Copy and paste this code into a new module:
============== Start Code =================
Option Compare Database
Option Explicit
Private Const LOGPIXELSX As Long = 88
Private Const LOGPIXELSY As Long = 90
Private Const nTwipsPerInch As Long = 1440
Private dTwipsPerPixelX As Double
Private dTwipsPerPixelY As Double
Private Type winRECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Private Declare Function GetDC _
Lib "user32" ( _
ByVal hWnd As Long _
) As Long
Private Declare Function ReleaseDC _
Lib "user32" ( _
ByVal hWnd As Long _
, ByVal hDC As Long _
) As Long
Private Declare Function GetDeviceCaps _
Lib "gdi32" ( _
ByVal hDC As Long _
, ByVal nIndex As Long _
) As Long
Private Declare Function GetWindowRect _
Lib "user32" _
( _
ByVal hWnd As Long _
, lpRect As winRECT _
) As Long
Private Declare Function FindWindowEx _
Lib "user32" _
Alias "FindWindowExA" ( _
ByVal hWndParent As Long _
, ByVal hwndChildAfter As Long _
, ByVal lpszClass As String _
, ByVal lpszWindow As String _
) As Long
Private Function TwipsPerPixelX() As Double
If dTwipsPerPixelX = 0 Then GetScreenMetrics
TwipsPerPixelX = dTwipsPerPixelX
End Function
Private Function TwipsPerPixelY() As Double
If dTwipsPerPixelY = 0 Then GetScreenMetrics
TwipsPerPixelY = dTwipsPerPixelY
End Function
Private Sub GetScreenMetrics()
Dim hDC As Long
hDC = GetDC(0)
dTwipsPerPixelX = nTwipsPerInch / GetDeviceCaps(hDC, LOGPIXELSX)
dTwipsPerPixelY = nTwipsPerInch / GetDeviceCaps(hDC, LOGPIXELSY)
hDC = ReleaseDC(0, hDC)
End Sub
Public Function GetRibbonHeight() As Long
' Return height of the Ribbon in twips
' Note that the result includes the QAT and the application title bar
Dim hWnd As Long, xRect As winRECT
hWnd = FindWindowEx(hWndAccessApp, 0, "MsoCommandBarDock", "MsoDockTop")
If hWnd <> 0 Then
hWnd = FindWindowEx(hWnd, 0, "MsoCommandBar", "Ribbon")
End If
If hWnd = 0 Then
MsgBox "Ribbon not found"
Else
If GetWindowRect(hWnd, xRect) = 0 Then
MsgBox "Cannot determine Ribbon dimensions"
Else
With xRect
GetRibbonHeight = (.Bottom - .Top) * TwipsPerPixelY
End With
End If
End If
End Function
Public Function GetNavPaneWidth() As Long
' Return width of the Nav Pane in twips
Dim hWnd As Long, xRect As winRECT, dHeight As Double
hWnd = FindWindowEx(hWndAccessApp, 0, "NetUINativeHWNDHost", "Navigation Pane Host")
If hWnd = 0 Then
MsgBox "Nav Pane not found"
Else
If GetWindowRect(hWnd, xRect) = 0 Then
MsgBox "Cannot determine Nav Pane dimensions"
Else
With xRect
GetNavPaneWidth = (.Right - .Left) * TwipsPerPixelX
End With
End If
End If
End Function
================ End Code =========================
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 August 2014 19:58
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights
... and other messages that went to spam?
thx ~ Crystal
On Tuesday, August 26, 2014 1:56 AM, Crystal <strive4peace2008@yahoo.com> wrote:
My spam box gets too many messages each day to go though them ...
Jeff, perhaps you can post Graham's code since you have it?
thanks
Warm Regards,
Crystal
Remote Programming & Training -- connect to me, let's build it together
Through sharing, we will all get better
~ have an awesome day ~
On Monday, August 25, 2014 7:08 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Graham, I never saw the code either ... I'd like to though!
Warm Regards,
Crystal
Remote Programming & Training -- connect to me, let's build it together
Through sharing, we will all get better
~ have an awesome day ~
On Monday, August 25, 2014 4:07 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Jeff
It must be because of that particularly obscene phrase, "Private Declare Function" :-)
Cheers,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 August 2014 09:24
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights
I didn't originally but I do have 3 from you and 1 from Stuart in my Spam folder. They include the code you mentioned. Not sure why they went to spam since the rest of the email from you two has been in my inbox.
Cheers,
Jeff
From: "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, August 25, 2014 2:16 PM
Subject: RE: [MS_AccessPros] widths and heights
I'm getting a bit paranoid now :-)
Did anyone else see the code I posted on this thread?
Did you look at the forum online, Stuart, or only in your email? If you receive messages by email, have you checked your spam folder?
Cheers,Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 August 2014 07:47
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights
You're apparently right. I'd love to see it.....
Stuart
On Saturday, August 23, 2014 8:09 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Crystal
I don't think Stuart is seeing any of my posts. The only ones he replies to are ones that you have quoted :-/
I wonder if his email has blacklisted me as a spammer or something :-(
Anyway, could you please repost my message from a few days ago with the API code? I spent a couple of hours on it so it would be a shame to see it wasted :-)
Cheers,
G
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Sunday, 24 August 2014 07:58
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights
Hi Stuart,
Allen Browne has a great reference page with code for reading and setting database properties.
http://www.allenbrowne.com/ser-69.html
How to ensure Access 2007 users cannot modify the database schema (field structure), forms and reports in databases created in previous versions.
Preview by Yahoo
hope this helps ~
Warm Regards,
Crystal
*
(: have an awesome day :)
*
On Friday, August 22, 2014 7:49 PM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
I am now hopelessly confused. I don't see any code in any of the messages previously posted.
Stuart
On Friday, August 22, 2014 6:52 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Stuart,
the Database Analyzer for Microsoft Access picks up some of the properties -- you can run it and then check the a_DBs table, which has some of the CurrentDb and file properties. Bill Mosca contributed code to this too. The Design view of the a_DBs table tells the property name. A lot of the information has been discovered by enumerating through properties.
Analyzer for Microsoft Access on CodePlex (open source)
http://analyzer.codeplex.com/
The Analyzer for Microsoft Access documents and analyzes what is in your Access databases. It is useful for anyone using or building Microsoft Access databases.
Preview by Yahoo
Warm Regards,
Crystal
Remote Programming & Training -- connect to me, let's build it together
Through sharing, we will all get better
~ have an awesome day ~
On Friday, August 22, 2014 3:40 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Stuart
Did you not see the code I posted for you? It's message #8 in this thread.
https://groups.yahoo.com/neo/groups/MS_Access_Professionals/conversations/topics/108692
All the best,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, 23 August 2014 08:49
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights
Crystal, this works really well! Only problem is, I am in a multi-user environment, and each user may have his/her own preference for where the form will be.
I also wonder if you could tell me where I could get documentation on currentDB properties. Access help doesn't seem to know about them.
Thanks, Stuart
On Friday, August 22, 2014 3:35 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Stuart,
Graham had the same problems with the code to check Nav Pane stuff ... maybe the property names changed ... or maybe the property values are read when Access opens and they don't change.
Window location:
save where you want the window to be and how big you want it to be in the form DESIGN VIEW. Set form property 'Close Button' to No. Put a close button on your form that closes withOUTsaving. Make sure you always save your changes in Design view before testing.
DoCmd.Close acform, me.name, acSaveNo 'closes the form code that code is behind without saving changes (data of course is saved).
> "by the width of the navigation pane"
yes, this is how Access works -- you are only setting where you want a window to be in the area to the right of the nav pane
Warm Regards,
Crystal
Remote Programming & Training -- connect to me, let's build it together
Visual Basic for Applications language reference for Office 2013
http://msdn.microsoft.com/en-us/library/office/gg264383%28v=office.15%29.aspx
Last modified: November 20, 2012 Applies to: Office 2013 | VBA Welcome to the Visual Basic for Applications (VBA) language reference for Office 2013.
Preview by Yahoo
Through sharing, we will all get better
~ have an awesome day ~
On Friday, August 22, 2014 9:12 AM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Crystal, let me be more specific. The routine you suggest for navpane width returns a number, but the results are puzzling. The number, assuming it is supposed to be Twips, is much too small. If I choose a navpane about 2 inches wide, it returns a number of about 250. And it doesn't change if you resize the navpane. You have to exit Access and come back in to see the size recalculated.
Perhaps I am on the wrong track. What I am trying to do is open a moveable window in Access and have the program remember the location of the window so that the next time it is opened it opens in the same place. It seems that storing the location of the window by using the form's WindowLeft and WindowTop properties stores values that exclude the ribbon and the Navigation pane, while reopening the form with the stored values with MoveSize includes them. So the form always reopens left of where I wanted it by the width of the navigation pane if the navigation pane is visible. Analogous problem for the ribbon.
By the way code that was recommended for ribbon height is
Application.CommandBars.Item("Ribbon").Height
but that doesn't seem to work well either.
stuart
On Friday, August 22, 2014 9:59 AM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
I think my reply got lost somewhere. I have tried the NavPane properties, but they don't work for me. No matter what I do to the width of the pane, it always returns the same number, 209. And it always says the pane is hidden even when it isn't. Am I doing something wrong?
Stuart
On Thursday, August 21, 2014 10:12 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Graham,
uh, you're welcome? that's too bad ... bug? bad documentation? ~ on a happy note, I added more to the Code Documenter and just uploaded a new version :) Just ran it on the Analyzer ... now I'm running the web page report to post with the code :) ... well we'll see how fast it loads! (whether it will be one or more pages) I like using the jumps and the Back button and having everything all together but some people have asked for the code to be separated.
Warm Regards,
Crystal
Remote Programming & Training -- connect to me, let's build it together
Code Documenter for Access
http://www.accessmvp.com/strive4peace/CodeDocumenter.htm
Through sharing, we will all get better
~ have an awesome day ~
Download Crystal's Code Documenter Please send me your comments and ideas! This also documents MDB files. Watch for updates. Access 2007+ version Crystals_CodeDocumenter_120204_2p_BETA_ACCDE_TXT.zip
Preview by Yahoo
On Thursday, August 21, 2014 5:40 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Crystal
Thank you! I just learned about some more properties :-)
However … the results stay the same, whether I show/hide the nav pain, or change its width, or show/hide the search bar :-/
-- Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 22 August 2014 11:29
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights
Sub NavPane()
With CurrentDb
Debug.Print "Show Navigation Pane Search Bar = "; .Properties("Show Navigation Pane Search Bar")
Debug.Print "NavPane Closed: "; .Properties("NavPane Closed")
Debug.Print "NavPane Width: "; .Properties("NavPane Width")
End With
End Sub
someone else can jump in on the ribbon part of your question ...
Warm Regards,
Crystal
Remote Programming & Training -- connect to me, let's build it together
Code Documenter for Access
http://www.accessmvp.com/strive4peace/CodeDocumenter.htm
Through sharing, we will all get better
~ have an awesome day ~
Download Crystal's Code Documenter Please send me your comments and ideas! This also documents MDB files. Watch for updates. Access 2007+ version Crystals_CodeDocumenter_120204_2p_BETA_ACCDE_TXT.zip
Preview by Yahoo
On Thursday, August 21, 2014 1:11 PM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Is there any way (Access 2007) to determine the width of the navigation pane? Or the height of the ribbon?
Thanks, Stuart
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (25) |
Tidak ada komentar:
Posting Komentar