Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Resize form along with buttons

0

Dear Experts,

When the screen size/resolution is different, the following code just fits the size of a form in the screen but can't change button's size in the same ratio. As a result, some parts can't accomodate in the screen.


Private Sub UserForm_Activate()
Application.WindowState = xlMaximized
Me.Height = Application.Height
Me.Width = Application.Width
End Sub

I couldn't catch the idea shared in different sites. Could you help me in simple words, please?

Thank you!

Answer
Discuss

Answers

1
Selected Answer

This is a tall order for VBA. I think they use Java on web pages. However, if you want to do it with VBA you would need to establish a "normal" for the form, say 500pts for the form's height. From that "normal" you calculate a ratio. Say, if Application.Height = 650 then the ratio?would be?650/500 = 1.3 which is equal to 130%.

Now you need to go through every height of the form itself and every control in it and the font size in each control and multiply with the ratio you found. You can use a different ratio for the width but efficiency would dictate that you use the same. Remember to also change the Top and Left properties.

Actually it sounds more awesome than it is. Spend a little time to create a loop and it can be done in a jiffy. Use Me.Controls("Controlname") to identify controls in the loop. Create an array of the control names and use it as shown below.

Dim Arr() As String
Dim Radio As Single
Dim i As Long

Ratio = Application.Height/500
Arr = Split("Tbx1,Tbx2,Cbx1,Cbx2,Lbl1,Lbl2", ",")
' and then
For i = 0 to UBound(Arr)
With Me.Controls(Arr(i))
.Height = .Height * Ratio
.Top = .Top * Ratio
.Font.Size = .Font.Size * Ratio
End With
Next i
Discuss

Discussion

Sorry to admit that the process may be over the head of novice learners but I'm really grateful to your help and suggestions. Thanks a lot!
Chhabi Acharya (rep: 111) Jul 16, '18 at 12:08 pm
Thanks for the points. Let me know if you want to take smaller steps to reach your target.
Variatus (rep: 4889) Jul 16, '18 at 9:52 pm
Of course, that would be great!
Chhabi Acharya (rep: 111) Jul 16, '18 at 11:19 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login