Excel : VBA : Userform in full screen mode

[RAKESH230583]

Dear All,

I have created one userform which has the width of 1022.25 additionally in order to run it in full screen mode. I had used the below quoted VBA CODE.

Quote

Private Sub UserForm_Activate()
'****************************************************************
'Opening userform in full screen
'****************************************************************
Application.WindowState = xlMaximized
Me.Height = Application.Height - 10
Me.Width = Application.Width - 10

End Sub
Unquote

Additionally, I had used the below quoted VBA Code to minimize and maximize the userform with the help of ToogleButton1.

Quote

Private Sub ToggleButton1_Click()
'****************************************************************
'Minimize the Userform
'****************************************************************
If Me.ToggleButton1.Value = True Then

Me.ToggleButton1.Caption = "Maximize Form"

Me.Height = 60
Me.Width = 200
Me.Top = 450
Me.Left = 10

Me.ToggleButton1.Left = 777.6
Me.ToggleButton1.Top = 10
Me.Width = Application.Width - 10
Me.ToggleButton1.BackColor = &H8000000F
Me.ToggleButton1.BackStyle = fmBackStyleOpaque
Me.ToggleButton1.ForeColor = &H80000012

Me.Save_Details.Top = 10
Me.Email.Top = 10
Me.Discard.Top = 10
Me.Label_16.Visible = False

Else

Me.ToggleButton1.Caption = "Minimize Form"

Me.Height = Application.Height - 10
Me.Width = Application.Width - 10
Me.Top = 0
Me.Left = 0

Me.ToggleButton1.Left = 777.6
Me.ToggleButton1.Top = 486
Me.Width = Application.Width - 10
Me.Label_16.Visible = True
Me.Save_Details.Top = 486
Me.Email.Top = 486
Me.Discard.Top = 486

End If
End Sub
Unquote.

Now, Thing is that – When I am using the excel file is working fine for me– But if someone else from my team uses the same excel in their Workstation – Some parts (from right side) of userform gets cropped and users are not able to view it. I believe that this is because screen size of all users workstation vary from each other.

I Don't want to restructure my current userform – as spent lot of time in it – But is there any way out where everyone (with different screen size) able to view the complete userform with in full screen mode.

Please feel free to ask in case any additional information is required.

Thanks in advance for your support.

 

[My Aswer Is This]

This is not really a answer to your question. But I'm wondering why you would need a UserForm so big.

Have you ever tried using MultiPages on your UserForm?

Using Multipages can be like having 5 10 or more UserForms all on the same Userform.

Each Multipage can be dedicated to just certain categories of your data.

 

[RAKESH230583]

Hi - I Thought to capture all in single view - but now its already done and as mentioned that I don't have that much of time to restructure all from scrap.

If I can get the correct VBA Code then I belive that my problem will be solved without changing anything.

 

[Johndinho]

I've borrowed this code from elsewhere in the forums

Use "Zoom to Selection"

Enter this as the last element before each of the minimise/maximise code finishes

Dim zArea As String
zArea = "A1ENTER YOUR FAR RIGHT HAND CELL REFERENCE)"
Range(zArea).Select
ActiveWindow.Zoom = True

 

[RAKESH230583]

Its not working -
Can anyone please help me out with the exact code to be used to solve the issue.

 

[RAKESH230583]

Do we have any Code to assist on above - Or is there any code which can zoom the userform for autofit to screen window.