Results 1 to 7 of 7

Thread: Resizeable VBA UserForms (and Unicode Form Captions)

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    269

    Resizeable VBA UserForms (and Unicode Form Captions)

    If you have done much VBA programming you know that it is frustrating to use UserForms on PC’s that have different display resolutions and multiple monitors. In addition, although the VBA Forms package will display Unicode characters, you can’t enter them in the forms editor so it is difficult to get them to display.

    This easy-to-use system takes care of all of that. There is a small amount of code that needs to be added to each form you want to be resizable and there is a Class module to include with your document. (I generally don’t like Class modules because they are clumsy to use but in situations where we have some code that can be re-used a lot then the hassle of setting them up and using them outweighs their complexity and since you may want to apply the re-sizing capability to many forms this seemed like a good candidate to use a class module.) One of the strange things about VBA forms compared to VB forms is that you can only change the form size and location from within the code for the form. It would have been nice to be able to access the form position data from within the class module and then make the changes to the form that we want to re-size the form but this isn’t possible. I put as much of the code as possible in the class module but some had to go

    The system works for all 32 and 64-bit Windows VBA.

    BTW, I believe strongly in commenting code. One of the negatives about VBA code is that the comments drag around with each document so I will do most of the commenting in this document which is your main reference for resizable forms.


    Class Module

    There is a class module called clResizer that needs to be imported into your project. In the VBA IDE press Ctrl-M (or do File | Insert File) and find this file. There is nothing you need to do to this file beyond importing it.

    Code for Each Resizable Form

    In a form that you want to be resizable you should put the following code (cut and paste if necessary):

    In the declaration section which is below the Option Explicit statement (which you definitely should be using):

    Code:
    Private frmResize As New clResizer
    Public UniCaption As String
    Private Executing As Boolean
    Private Activated As Boolean
    Below this and any other declarations you may have for your form, put the following code:

    Code:
    Private Sub UserForm_Resize()
    Dim NewHeight As Single, NewZoom As Integer
    If Not Executing Then
       If frmResize.FormResized(Me.Left, Me.Top, Me.Width, Me.Height, NewHeight, NewZoom) Then
          Executing = True
          Me.Height = NewHeight
          Me.Zoom = NewZoom
          Executing = False
          End If
       End If
    End Sub
    Private Sub FormSize(Left As Single, Top As Single, Width As Single, Height As Single)
    Executing = True
    Me.Left = Left: Me.Top = Top: Me.Width = Width: Me.Height = Height
    Executing = False
    UserForm_Resize
    End Sub
     
    
     
    Public Sub CenterForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    Dim L As Single, T As Single, W As Single, H As Single
    frmResize.CFCalc WidthPerCent, HeightPerCent, Limit2Screen, L, T, W, H
    FormSize L, T, W, H
    End Sub
     
    
     
    Public Sub MoveForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    Dim L As Single, T As Single
    frmResize.MoveForm WidthPerCent, HeightPerCent, Limit2Screen, L, T, Me.Width, Me.Height
    Me.Move L, T
    End Sub
    Then either include the following routines or take the code and put it in your versions of the same routines:

    Code:
    Private Sub UserForm_Initialize()
    frmResize.NewForm Me, Me.Left, Me.Top, Me.Width, Me.Height, Me.Caption, Me.Zoom
    End Sub
     
    
     
    Private Sub UserForm_Activate()
    If Not Activated Then
       ' dipslay the Unicode caption if not blank and makes form resizable
       Call frmResize.Activate(UniCaption)
       CenterForm 60, 60, True ' make the form fill up 60% of the screen
     
       Activated = True
       End If
    End Sub
    That’s all there is to setting this up. When you show this Userform it will be resizable and as you change the size of the form by using the standard Windows technique of mouse-dragging the corners or sides the form will change in size and all of the controls in the form will also change size (that’s the best part!).

    From the Activate code above you can also see that the default is to center the form on the screen and have the form be 60% of the size of the screen. You can change this to whatever you want and you can have it start up anywhere on the screen including wherever it was the last time the form was used.


    Displaying a Unicode Caption

    It is frustrating trying to display a Unicode caption in the form title especially since VBA and Excel and Word etc. easily display Unicode characters. I have created a public variable in the form call UniCaption. If you set this variable to any string then when the form is displayed UniCaption will be the form caption instead of whatever was used previously as the caption.

    Note – It is very important that your form has at least some caption set in the editor. I use the form caption to ‘find” the handle to it inside of Windows. To be safe just make its value be something unique from your other VBA forms that potentially could be loaded at the same time. This normally isn’t a problem since most userforms in VBA are modal which means nothing else happens until that form goes away.

    Suppose you have a form named fmTestMe. Suppose you want the string “あいうえお ovat japanilaisia hiragana-merkkej.” to be displayed as the caption of fmTestMe. If it never changed you could put the following line in the Activate sub of the form:

    Code:
    UniCaption = ChrW$(&H3042) & ChrW$(&H3044) & ChrW$(&H3046) & ChrW$(&H3048) & _
    ChrW$(&H304A) & " ovat japanilaisia hiragana-merkkej."
    But suppose you wanted to have this value change over time (maybe a customer name or something) then you could easily reference a cell if you were using Excel. Suppose you had cell B20 designated to hold the text for the caption then you could have a line somewhere in your code before you do the show:

    Code:
    fmTestMe.UniCaption = Sheets("Sheet1").Range("B20").Value
    fmTestMe.Show

    Setting the Form’s Initial Position

    Below are some techniques for setting the initial position of the form. My recommendation is to initially put the form on the same screen as the VBA host (Excel, Word, etc.) and since it is resizable and moveable the user will put it wherever it works best and then we will save and restore that size and position for future re-use.

    Because of the way forms work, once it is displayed the programmer has little control over the position of the form. Programatically we can respond to the Resize event (which we already do) but that is largely driven by the user who is resizing the form. I suppose you could catch this re-sizing event and do something different but I don’t know what. There is no easy way to catch a Move event and the whole purpose of this system is to let the user move the form and re-size as he/she sees fit. So this means that in general we would want to move and or re-size the form just before it is being displayed.

    You can put code in the Initialize event for the form but keep in mind that at this point we have not yet had Windows make the form resizable so any attempt to resize the form will not work. Also, if you try domething like the following it won’t work either:

    Code:
    fmTestMe.CenterForm(50, 50, True)
    fmTestMe.Show
    Anything in a normal module before the Show command basically causes the Initialize event to fire and our code will be executing before the Windows call to enable resizing. The resizing code is called in the Activation event which is after Initialize and just before the form is displayed.

    The only way I know of to get code to affect the form after the Show statement is if the form has been hidden instead of unloaded.

    My recommendation is to decide what you want to do regarding the form size and location and put the code to do this in the Activate event procedure in the code for the Form. You have two routines in the Form code for form location that enable you set the size and position to be centered or anywhere on the screen and with little effort you can derive many others.

    Code:
    Sub CenterForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    This enables you to center and optionally resize the form.


    • To size the form based on the available screen width and height
      • WidthPerCent and HeightPerCent are the %'s of the screen width and height respectively
      • To make a form fill up half of the screen regardless of the screen size and resolution you would specify the following in the form’s Activate procedure:



    Code:
    CenterForm 50, 50, True

    • Note – As long as Zoomable is True (default), the setting for HeightPerCent is ignored because the code determines the required height to keep the height/width ratio constant.


    • To size the form based on the original size of the form
      • WidthPerCent and HeightPerCent are based on the original form size but negative
      • To make a form be twice the size of the original form you would specify the following in the Activate procedure of your form:



    Code:
    CenterForm -200, -200, True  ' for 200% but negative

    • Note – As long as Zoomable is True, the HeightPerCent parameter is ignored.


    • If limit2Screen is True then the form size is adjusted as necessary to keep it all onscreen.


    Code:
    Sub MoveForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    This enables you to move the form and optionally keep it onscreen.


    • To move the form based on the available screen width and height
      • WidthPerCent and HeightPerCent are the %'s of the screen width and height respectively
      • To make a form’s upper left corner go to the middle of the screen regardless of the screen size and resolution you would specify the following in the form’s Activate procedure:



    Code:
    MoveForm 50, 50, True

    • Note – As long as Zoomable is True (default), the setting for HeightPerCent is ignored because the code determines the required height to keep the height/width ratio constant.



    • To move the form to the specific left and top coordinates
      • WidthPerCent and HeightPerCent are the specific form position values for Left and Top
      • To make a form go to the top left of the screen you would specify this in the Activate procedure:



    Code:
    MoveForm 0, 0, True

    • Note – As long as Zoomable is True, the HeightPerCent parameter is ignored.


    • If limit2Screen is True then the form size is adjusted as necessary to keep it all onscreen.



    Form Aspect Ratio

    If a form is resized, it is normally desirable to have eerything on the form change size with the form itself. When this happens we all want the ratio of the form height to width to stay the same so that we don’t get strange looking text or graphics. I make use of a VBA form parameter called Zoom to manage this but in order for it to work well we need to keep the aspect ratio the same. When you re-size a form I have the code continuously check this ratio and keep it the same. Run the sample and drag the lower right of the form to change size and notice that it won’t allow you to make the form tall and skinny or short and fat. It also won’t let you change size by dragging the sides of the form (that is allowed in Windows but I don’t let it happen because if you try to change the height or width without the other since that would change the height/width ratio. You can only change the form size by dragging one of the corners.


    Zooming

    Resizing a form is generally not of much use if all of the controls on the form do not change. I use a VBA Forms parameter called Zoom to resize form components to fit the form as it is re-sized. When it is re-sized either by your code or by the user dragging a corner of the form, everything on the form looks the same except it is larger or smaller like the re-sized form.

    There may be an odd time or two when you want to change the form size but not use the Zoom feature. For example you could have some controls that start out below the bottom of the displayed form so that the user can’t see them but then later the bottom of the form could be dropped to expose the additional controls. For this possibility we maintain a Boolean variable in the class module called Zoomable and if it is set to False then when the form is resized the controls don’t get resized.

    In the sample project we put a check box on the example user form that lets you turn this on or off with this code:

    Code:
    Private Sub cboxKeepRatio_Click()
    frmResize.Zoomable = CBool(cboxKeepRatio.Value)
    End Sub
    In the form code we refer to the class module clResize as frmResize and that class module contains the Public variable Zoomable which we toggle between True and False based on the user clicking on the check box.


    Continued below...
    Attached Files Attached Files
    Last edited by MountainMan; Dec 6th, 2016 at 11:18 AM. Reason: Removed an unused reference from .xls sample

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    269

    Re: Resizeable VBA UserForms (and Unicode Form Captions)

    Saving and Restoring the Form Position

    A relatively easy thing to do is to save the form’s size and position at the end of the form’s life and then restore that the next time the form is used. You need to decide where to store the form data. There are 4 pieces of data you need to save and later retrieve, the form left, top, width and height values which are all Single data types in my code.

    Saving Form Size and Position Data

    The only place to get the values to save is at the end of the QueryClose event handler in the Form code. Most people don’t ever use this sub and those who do generally use it to prevent a user from closing the form by clicking on the “x” in the top right of the form. We will use it for something else (it can still be used to keep the user from closing the form if desired).

    QueryClose has 2 parameters, one with incoming data and one return data. The incoming data is the CloseMode which is an integer that tells us why someone wants to close the form as follows:

    VBA Enum Value Meaning
    vbFormControlMenu 0 User clicked x or Cancel
    vbFormCode 1 Unload statement is trying to remove the form from memory
    vbAppWindows 2 Windows is shutting down
    vbAppTaskManager 3 Windows Task Manager is trying to shut down the application.


    You can decide what to do based on who is trying to close the form. If you want to not allow the form to be unloaded you would set the parameter Cancel = True. In general, you will not do this for any of the 4 possible reasons but sometimes you would want to for reasons beyond the scope of this document. For example, if you don't want the user to be able to close the form you would put this statement in QueryClose:

    Code:
    if CloseMode = vbFormControlMenu then Cancel = True
    We only want to save the form size and position data when the Form is really about to be Unloaded which occurs only if Cancel is not set True.

    You likely want to get this data back at a future date and it may even be after the program has shut down. You can’t use variables in your program to hold this data since it goes away when your program ends. Thus we need something that persists between runs of your code. You can write the data to a file, you can write the data to the Windows registry or if you are using Excel you can write the data to cells in a worksheet or even put them in special named ranges. In the sample spreadsheet included with this I have stored the data into a range of 4 cells on a worksheet.

    It is unusual for VBA programmers to use the registry much and it can be intimidating but VBA offers you the SaveSetting and GetSetting statements which enable you to fairly easily save settings and retrieve them later. Within the VBA IDE editor, look these up in Help.

    Writing the values to a file doesn’t make sense by itself but if there are other data from your program that needs to be saved at the end of the program’s execution then perhaps it is good to store the data in program variables that are then written to a file at program termination with other data.


    Restoring Form Size and Position

    The place to restore the saved data is in the form’s Activate procedure (don’t do it in Initialize because the form is not resizable at that time). Don’t forget to have a check on the data to make sure it is not 0 (i.e., you haven’t yet save the size/position data) or else the code will fail.

    In the example spreadsheet I retrieve the data from the worksheet where I stored it earlier in the QueryClose procedure. I also check to ensure that there actually is some data before I use it. I put the data right next to the button on Sheet1 so you can see the data. In a normal application you likely don’t want your users to see or change this information so you would likely put it on a hidden sheet.


    Sample

    There is a sample Excel spreadsheet included that has a button to click on which brings up this form:

    Name:  Sample.jpg
Views: 11880
Size:  26.0 KB

    You can drag the corners or edges of this form to resize the form and if the Zoom checkbox is checked all of the controls are resized as the form resizes. Note that the title (caption) is also in Unicode. The spreadsheet uses the class module clResizer.cls as described in this document and the code in the form is very similar to what is discussed in this document.

    Excel and Word handle Unicode very well but the VBA editors do not. Sometimes you need to take a Unicode string and generate a string that can be used within the VBA editor. The second page in the workbookdoes this for you. It is fairly self-explanatory but you put the Unicode string in column A and the string is returned as a VBA-ready string in column C and Column D (column d has hex numbers for those who like that instead of regular numbers).


    Class Module clResizer

    Using this module is very simple from the programmer’s standpoint. Normally we have to use “Set” to get the class module going and “Unload” to end the module and free up variable memory. Generally you should not use the New keyword in the Dim statement for the class module because you can never get rid of it. However that’s just what we want here. We want to get an instance of the class module when the form gets initialized and we want it to go away when the form goes away. So in the form module we have the line:

    Code:
    Private frmResize As New clResizer
    and we don’t have to think about it further. It is there for access by our form for the whole time the form exists and we can use it at will and not worry about getting rid of it later because it goes away when the form does.

    This class module does all of the calculations for the screens, form and positioning. There are several function calls to the Windows API for things that VBA does not provide such as primary monitor size. It is much better to have all of this in one class module shared by all resizable forms instead of putting the code into each form.

    Windows API Calls (shown for Win64 but very similar for Win32 except the LongPtr values are Long. Also GetWindowLongPtr is the 64-bit call for the 32-bit GetWindowLong and SetWindowLongPtr is the 64-bit call for the 32-bit SetWindowLong.

    Code:
    Private Declare PtrSafe Function GetActiveWindow Lib "user32.dll" () As LongPtr
    
    Private Declare PtrSafe Function GetWindowLongW Lib "user32.dll" Alias "GetWindowLongPtrW" ( _
    ByVal hWnd As LongPtr, _
    ByVal nIndex As Long) _
    As LongPtr
    
    Private Declare PtrSafe Function SetWindowLongW Lib "user32.dll" Alias "SetWindowLongPtrW" ( _
    ByVal hWnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As LongPtr) _
    As LongPtr
    
    Private Declare PtrSafe Function SetLastError Lib "kernel32.dll" (ByVal dwErrCode As Long) As Long
        NOTE – Not used now…
    
    Private Declare PtrSafe Function DefWindowProcW Lib "user32" ( _
    ByVal hWnd As LongPtr, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    ByVal lParam As Long) _
    As LongPtr
    
    Private Declare PtrSafe Function FindWindowW Lib "user32" ( _
    ByVal lpClassName As LongPtr, _
    ByVal lpWindowName As LongPtr) _
    As LongPtr
    
    Private Declare PtrSafe Function GetSystemMetrics Lib "user32.dll" (ByVal nIndex As Long) As Long
    
    Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
    
    Private Declare PtrSafe Function ReleaseDC Lib "user32" ( _
    ByVal hWnd As LongPtr, _
    ByVal hDC As LongPtr) _
    As Long
    
    Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" ( _
    ByVal hDC As LongPtr, _
    ByVal nIndex As Long) _
    As Long

    Visual Basic 6

    VB6 has a much more robust forms package. It has the capability of making at design time (set the form’s BorderStyle to 2) but it lacks the Zoom feature of VBA so in order to have controls on the form change size with the changing size of the form we have to change the control sizes ourselves. A system very similar to the VBA Form Resizer has been developed and is available for VB6 forms.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Resizeable VBA UserForms (and Unicode Form Captions)

    this seems a very useful concept, i have not looked through it completely yet, but looks like it should work well

    It would have been nice to be able to access the form position data from within the class module and then make the changes to the form that we want to re-size the form but this isn’t possible.
    i am not sure this is correct, i would believe you can change the form properties from the class module and also respond to form events if desired

    probably would need to change from dim as new cls, to set a new cls instance for each form

    edit:
    also i have a missing reference to timo's date control or some such thing, which i had to remove
    Last edited by westconn1; Dec 6th, 2016 at 05:50 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    269

    Re: Resizeable VBA UserForms (and Unicode Form Captions)

    Each form does get its own instance of the class module.

    I tried a number of ways to be able to access the form's size and position data bit none worked. If you check the properties in the class module available for MyUserForm you will see most of the form's properties but unfortunately Top, Left, Width and Height are not listed so you can't even see the present values much less change them. I would love to be able to get the sizing and moving code into the class module and out of the code for each form but I haven't been able to figure it out and I haven't found anything by anyone else online that shows how this can be done.

  5. #5
    New Member
    Join Date
    Feb 2024
    Posts
    1

    Re: Resizeable VBA UserForms (and Unicode Form Captions)

    I know its an old post but do you still have the files referenced? The zip files are not working for me.


    Quote Originally Posted by MountainMan View Post
    If you have done much VBA programming you know that it is frustrating to use UserForms on PC’s that have different display resolutions and multiple monitors. In addition, although the VBA Forms package will display Unicode characters, you can’t enter them in the forms editor so it is difficult to get them to display.

    This easy-to-use system takes care of all of that. There is a small amount of code that needs to be added to each form you want to be resizable and there is a Class module to include with your document. (I generally don’t like Class modules because they are clumsy to use but in situations where we have some code that can be re-used a lot then the hassle of setting them up and using them outweighs their complexity and since you may want to apply the re-sizing capability to many forms this seemed like a good candidate to use a class module.) One of the strange things about VBA forms compared to VB forms is that you can only change the form size and location from within the code for the form. It would have been nice to be able to access the form position data from within the class module and then make the changes to the form that we want to re-size the form but this isn’t possible. I put as much of the code as possible in the class module but some had to go

    The system works for all 32 and 64-bit Windows VBA.

    BTW, I believe strongly in commenting code. One of the negatives about VBA code is that the comments drag around with each document so I will do most of the commenting in this document which is your main reference for resizable forms.


    Class Module

    There is a class module called clResizer that needs to be imported into your project. In the VBA IDE press Ctrl-M (or do File | Insert File) and find this file. There is nothing you need to do to this file beyond importing it.

    Code for Each Resizable Form

    In a form that you want to be resizable you should put the following code (cut and paste if necessary):

    In the declaration section which is below the Option Explicit statement (which you definitely should be using):

    Code:
    Private frmResize As New clResizer
    Public UniCaption As String
    Private Executing As Boolean
    Private Activated As Boolean
    Below this and any other declarations you may have for your form, put the following code:

    Code:
    Private Sub UserForm_Resize()
    Dim NewHeight As Single, NewZoom As Integer
    If Not Executing Then
       If frmResize.FormResized(Me.Left, Me.Top, Me.Width, Me.Height, NewHeight, NewZoom) Then
          Executing = True
          Me.Height = NewHeight
          Me.Zoom = NewZoom
          Executing = False
          End If
       End If
    End Sub
    Private Sub FormSize(Left As Single, Top As Single, Width As Single, Height As Single)
    Executing = True
    Me.Left = Left: Me.Top = Top: Me.Width = Width: Me.Height = Height
    Executing = False
    UserForm_Resize
    End Sub
     
    
     
    Public Sub CenterForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    Dim L As Single, T As Single, W As Single, H As Single
    frmResize.CFCalc WidthPerCent, HeightPerCent, Limit2Screen, L, T, W, H
    FormSize L, T, W, H
    End Sub
     
    
     
    Public Sub MoveForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    Dim L As Single, T As Single
    frmResize.MoveForm WidthPerCent, HeightPerCent, Limit2Screen, L, T, Me.Width, Me.Height
    Me.Move L, T
    End Sub
    Then either include the following routines or take the code and put it in your versions of the same routines:

    Code:
    Private Sub UserForm_Initialize()
    frmResize.NewForm Me, Me.Left, Me.Top, Me.Width, Me.Height, Me.Caption, Me.Zoom
    End Sub
     
    
     
    Private Sub UserForm_Activate()
    If Not Activated Then
       ' dipslay the Unicode caption if not blank and makes form resizable
       Call frmResize.Activate(UniCaption)
       CenterForm 60, 60, True ' make the form fill up 60% of the screen
     
       Activated = True
       End If
    End Sub
    That’s all there is to setting this up. When you show this Userform it will be resizable and as you change the size of the form by using the standard Windows technique of mouse-dragging the corners or sides the form will change in size and all of the controls in the form will also change size (that’s the best part!).

    From the Activate code above you can also see that the default is to center the form on the screen and have the form be 60% of the size of the screen. You can change this to whatever you want and you can have it start up anywhere on the screen including wherever it was the last time the form was used.


    Displaying a Unicode Caption

    It is frustrating trying to display a Unicode caption in the form title especially since VBA and Excel and Word etc. easily display Unicode characters. I have created a public variable in the form call UniCaption. If you set this variable to any string then when the form is displayed UniCaption will be the form caption instead of whatever was used previously as the caption.

    Note – It is very important that your form has at least some caption set in the editor. I use the form caption to ‘find” the handle to it inside of Windows. To be safe just make its value be something unique from your other VBA forms that potentially could be loaded at the same time. This normally isn’t a problem since most userforms in VBA are modal which means nothing else happens until that form goes away.

    Suppose you have a form named fmTestMe. Suppose you want the string “あいうえお ovat japanilaisia hiragana-merkkej.” to be displayed as the caption of fmTestMe. If it never changed you could put the following line in the Activate sub of the form:

    Code:
    UniCaption = ChrW$(&H3042) & ChrW$(&H3044) & ChrW$(&H3046) & ChrW$(&H3048) & _
    ChrW$(&H304A) & " ovat japanilaisia hiragana-merkkej."
    But suppose you wanted to have this value change over time (maybe a customer name or something) then you could easily reference a cell if you were using Excel. Suppose you had cell B20 designated to hold the text for the caption then you could have a line somewhere in your code before you do the show:

    Code:
    fmTestMe.UniCaption = Sheets("Sheet1").Range("B20").Value
    fmTestMe.Show

    Setting the Form’s Initial Position

    Below are some techniques for setting the initial position of the form. My recommendation is to initially put the form on the same screen as the VBA host (Excel, Word, etc.) and since it is resizable and moveable the user will put it wherever it works best and then we will save and restore that size and position for future re-use.

    Because of the way forms work, once it is displayed the programmer has little control over the position of the form. Programatically we can respond to the Resize event (which we already do) but that is largely driven by the user who is resizing the form. I suppose you could catch this re-sizing event and do something different but I don’t know what. There is no easy way to catch a Move event and the whole purpose of this system is to let the user move the form and re-size as he/she sees fit. So this means that in general we would want to move and or re-size the form just before it is being displayed.

    You can put code in the Initialize event for the form but keep in mind that at this point we have not yet had Windows make the form resizable so any attempt to resize the form will not work. Also, if you try domething like the following it won’t work either:

    Code:
    fmTestMe.CenterForm(50, 50, True)
    fmTestMe.Show
    Anything in a normal module before the Show command basically causes the Initialize event to fire and our code will be executing before the Windows call to enable resizing. The resizing code is called in the Activation event which is after Initialize and just before the form is displayed.

    The only way I know of to get code to affect the form after the Show statement is if the form has been hidden instead of unloaded.

    My recommendation is to decide what you want to do regarding the form size and location and put the code to do this in the Activate event procedure in the code for the Form. You have two routines in the Form code for form location that enable you set the size and position to be centered or anywhere on the screen and with little effort you can derive many others.

    Code:
    Sub CenterForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    This enables you to center and optionally resize the form.


    • To size the form based on the available screen width and height
      • WidthPerCent and HeightPerCent are the %'s of the screen width and height respectively
      • To make a form fill up half of the screen regardless of the screen size and resolution you would specify the following in the form’s Activate procedure:



    Code:
    CenterForm 50, 50, True

    • Note – As long as Zoomable is True (default), the setting for HeightPerCent is ignored because the code determines the required height to keep the height/width ratio constant.


    • To size the form based on the original size of the form
      • WidthPerCent and HeightPerCent are based on the original form size but negative
      • To make a form be twice the size of the original form you would specify the following in the Activate procedure of your form:



    Code:
    CenterForm -200, -200, True  ' for 200% but negative

    • Note – As long as Zoomable is True, the HeightPerCent parameter is ignored.


    • If limit2Screen is True then the form size is adjusted as necessary to keep it all onscreen.


    Code:
    Sub MoveForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
    This enables you to move the form and optionally keep it onscreen.


    • To move the form based on the available screen width and height
      • WidthPerCent and HeightPerCent are the %'s of the screen width and height respectively
      • To make a form’s upper left corner go to the middle of the screen regardless of the screen size and resolution you would specify the following in the form’s Activate procedure:



    Code:
    MoveForm 50, 50, True

    • Note – As long as Zoomable is True (default), the setting for HeightPerCent is ignored because the code determines the required height to keep the height/width ratio constant.



    • To move the form to the specific left and top coordinates
      • WidthPerCent and HeightPerCent are the specific form position values for Left and Top
      • To make a form go to the top left of the screen you would specify this in the Activate procedure:



    Code:
    MoveForm 0, 0, True

    • Note – As long as Zoomable is True, the HeightPerCent parameter is ignored.


    • If limit2Screen is True then the form size is adjusted as necessary to keep it all onscreen.



    Form Aspect Ratio

    If a form is resized, it is normally desirable to have eerything on the form change size with the form itself. When this happens we all want the ratio of the form height to width to stay the same so that we don’t get strange looking text or graphics. I make use of a VBA form parameter called Zoom to manage this but in order for it to work well we need to keep the aspect ratio the same. When you re-size a form I have the code continuously check this ratio and keep it the same. Run the sample and drag the lower right of the form to change size and notice that it won’t allow you to make the form tall and skinny or short and fat. It also won’t let you change size by dragging the sides of the form (that is allowed in Windows but I don’t let it happen because if you try to change the height or width without the other since that would change the height/width ratio. You can only change the form size by dragging one of the corners.


    Zooming

    Resizing a form is generally not of much use if all of the controls on the form do not change. I use a VBA Forms parameter called Zoom to resize form components to fit the form as it is re-sized. When it is re-sized either by your code or by the user dragging a corner of the form, everything on the form looks the same except it is larger or smaller like the re-sized form.

    There may be an odd time or two when you want to change the form size but not use the Zoom feature. For example you could have some controls that start out below the bottom of the displayed form so that the user can’t see them but then later the bottom of the form could be dropped to expose the additional controls. For this possibility we maintain a Boolean variable in the class module called Zoomable and if it is set to False then when the form is resized the controls don’t get resized.

    In the sample project we put a check box on the example user form that lets you turn this on or off with this code:

    Code:
    Private Sub cboxKeepRatio_Click()
    frmResize.Zoomable = CBool(cboxKeepRatio.Value)
    End Sub
    In the form code we refer to the class module clResize as frmResize and that class module contains the Public variable Zoomable which we toggle between True and False based on the user clicking on the check box.


    Continued below...

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,398

    Re: Resizeable VBA UserForms (and Unicode Form Captions)

    ALL .zip files on the forum are currently corrupted, unfortunately. This may be fixed at some point.

    https://www.vbforums.com/showthread....60#post5633060

  7. #7

    Re: Resizeable VBA UserForms (and Unicode Form Captions)

    Have you considered exploring other methods to access and modify the form's size and position data from within the class module, and have you found any alternative solutions online for achieving this?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width