Im trying hard to find a solution to move a Frame to a defined place in my vba Userform. I actually want to hide/unhide objects which works well but as soon as an object is hidden the space is left BLANK. I want to make sure that there's no blank space on my userform and i would like to replace the blank space with .
I know the exact position on my userform where it should be placed.
I googled and found object has .Move property which can be used to do this. I tried this but my object disappears rather than moving.
I have an alternative of creating 3 types of forms and use the when needed but i want to move it to the right place and then auto-adjust the form (i have applied the macro already for auto-adjusting).
Please help me here.
My Macro code below :-
Private Sub UserForm_Activate()
If Sheets("Form").Range("F11").Value = 1 Then
Me.Frame2.Visible = False
Me.Frame1.Move Left:=0, Top:=1, Width:=0, Height:=0
ElseIf Sheets("Form").Range("E11").Value = 1 Then
Me.Frame1.Visible = False
Me.Frame2.Move Left:=0, Top:=0, Width:=0, Height:=0
ElseIf Sheets("Form").Range("E11").Value = 1 And Sheets("Form").Range("F11").Value = 1 Then
Me.Frame1.Visible = True
Me.Frame2.Visible = True
End If
Call CheckSize
End Sub
Private Sub CheckSize()
Dim h, w
Dim c As Control
h = 0: w = 0
For Each c In Me.Controls
If c.Visible Then
If c.Top + c.Height > h Then h = c.Top + c.Height
If c.Left + c.Width > w Then w = c.Left + c.Width
End If
Next c
If h > 0 And w > 0 Then
With Me
.Width = w + 40
.Height = h + 40
End With
End If
End Sub
I know the exact position on my userform where it should be placed.
I googled and found object has .Move property which can be used to do this. I tried this but my object disappears rather than moving.
I have an alternative of creating 3 types of forms and use the when needed but i want to move it to the right place and then auto-adjust the form (i have applied the macro already for auto-adjusting).
Please help me here.
My Macro code below :-
Private Sub UserForm_Activate()
If Sheets("Form").Range("F11").Value = 1 Then
Me.Frame2.Visible = False
Me.Frame1.Move Left:=0, Top:=1, Width:=0, Height:=0
ElseIf Sheets("Form").Range("E11").Value = 1 Then
Me.Frame1.Visible = False
Me.Frame2.Move Left:=0, Top:=0, Width:=0, Height:=0
ElseIf Sheets("Form").Range("E11").Value = 1 And Sheets("Form").Range("F11").Value = 1 Then
Me.Frame1.Visible = True
Me.Frame2.Visible = True
End If
Call CheckSize
End Sub
Private Sub CheckSize()
Dim h, w
Dim c As Control
h = 0: w = 0
For Each c In Me.Controls
If c.Visible Then
If c.Top + c.Height > h Then h = c.Top + c.Height
If c.Left + c.Width > w Then w = c.Left + c.Width
End If
Next c
If h > 0 And w > 0 Then
With Me
.Width = w + 40
.Height = h + 40
End With
End If
End Sub