Excel for mac resize window11/2/2022 ![]() ![]() LstListBoxBottom = Me.Height - lstListBox.Top - lstListBox.Height 'Get the bottom right anchor position of the objects to be resized 'Call the Window API to enable resizing Call ResizeWindowSettings(Me, True) Secondly, we store the position of the objects in the private variables created above. #Excel for mac resize window code#Now let’s set up what happens when the UserForm initializes.įirstly, resizing is enabled by calling the code created in the Windows API section above. Private lstListBoxBottom As Double Private lstListBoxRight As Double Private cmdCloseBottom As Double Private cmdCloseRight As Double ![]() To capture the initial bottom and right positions of the ListBox and Button, we set up some Private variables to hold the values. The code below must be included within the UserForm’s code module to function correctly. Provided we keep the same distance from the bottom and right it will appear that these items are moving in sync with the UserForm. To enable this to happen we need to know the position of these objects from the bottom and right of the UserForm. lstListBox should change in size, but not in position while the cmdClose will change in position but not in size. To illustrate the process, I’ve created a UserForm which looks like this:īoth of these elements should change when then UserForm resizes. Just replace myUserForm with the name of your form, or use Me if within the UserForm’s code module. To turn off resizing, use the following Call ResizeWindowSettings(myUserForm, False) Anytime we want to turn on resizing for a UserForm, use the following: Call ResizeWindowSettings(myUserForm, True) The two code segments above create a reusable procedure which we can use to toggle the UserForm’s resize setting on or off. 'Recreate the UserForm window with the new style SetWindowLong windowHandle, GWL_STYLE, windowStyle WindowStyle = windowStyle + (WS_THICKFRAME) WindowStyle = windowStyle And (Not WS_THICKFRAME) 'Determine the style to apply based If show = False Then ![]() WindowStyle = GetWindowLong(windowHandle, GWL_STYLE) WindowHandle = FindWindowA(vbNullString, frm.Caption) Sub ResizeWindowSettings(frm As Object, show As Boolean)ĭim windowStyle As Long Dim windowHandle As Long 'Get the references to window and style position within the Windows memory This following code must be included within the same module as the code above but does not need to be directly below it. Lib "user32" ( ByVal hWnd As Long) As Long Public Declare Function FindWindowA _ # Else Public Declare Function GetWindowLong _īyVal hWnd As Long, ByVal nIndex As Long) As Long Public Declare Function SetWindowLong _īyVal dwNewLong As Long) As Long Public Declare Function DrawMenuBar _ Lib "user32" ( ByVal lpClassName As String, _ Lib "user32" ( ByVal hWnd As Long) As Long Public Declare PtrSafe Function FindWindowA _ # If VBA7 Then Public Declare PtrSafe Function GetWindowLong _īyVal hWnd As Long, ByVal nIndex As Long) As Long Public Declare PtrSafe Function SetWindowLong _īyVal hWnd As Long, ByVal nIndex As Long, _īyVal dwNewLong As Long) As Long Public Declare PtrSafe Function DrawMenuBar _ It must be included at the top of the module before any functions or subprocedures, but below the Option Explicit statement (if there is one). Setting up the Windows API codeĬopy the following code into a new standard module.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |