minimize and maximize the userform based on values showing in textboxes

[Ali M]

Hi experts,
I want minimizing and maximizing the userform based on values showing in tools . just show the textboxes on userform contains values when run the userform and ignore textboxes are empty


this is what I got when run the userform

what I want if each three textboxes are empty shouldn't show like this

note: if one or two of for each three textboxes contains value then shouldn't show.

thanks

 

[My Aswer Is This]

Hiding them may be easy but to then move them up would be interesting. And then I guess you would need another script to unhide them and move them back down.
Not sure what this means: You said quote: "based on values showing in tools"

 

[Ali M]

thanks for your suggestion, but in my opinion hiding doesn't solve the problem because it keeps the size userform and just hide them .

I want like the message box . the message box increase & decrease based on data are filled in range . if the range is empty will ignore it . the same thing in the userform

Not sure what this means: You said quote: "based on values showing in tools"

I meant tools the textboxes. as in the picture the textboxes are empty then should move the userform to the up by decrease the height and minmizing the form and if there is many textboxes are filled then should move to the bottom by maximizing the form . and the same thing about width . it depends just the textboxes are filled as in picture .
I know this is not easy , may be the experts provide the help.

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

Even if the textboxes are empty the UserForm has Two Command Buttons what would happen to them? They would not be seen any more. If we minimize the entire userform.

 

[EXCEL MAX]

Hello Ali M,
I suppose you want to make some kind of filter rows on the userform like it's in the worksheet.
If is this what you want, I think the best approach should be to put each three textboxes on the separated frame control
and manipulate with frames.
It's because textboxes don't have ID property and it's hard to manipulate with them.
If you want to hide empty textboxes only one time (when you run the userform) you can try to use this code,
but the thing you should need to take care is to put each three textboxes
on the same "Top" property and make the same distance between each line with textboxes.

Private Sub UserForm_Activate()

   Dim vAT, vCtrl, vCtrl2
   Dim vRow As Long, vHeight As Long
'set constants textboxes with layout in the userform
   Const vSpace = 6
   Const vCtrlHeight = 15

'fill your data
   TextBox4 = "TextBox4": TextBox5 = "TextBox5": TextBox6 = "TextBox6"
   TextBox10 = "TextBox10": TextBox11 = "TextBox11": TextBox12 = "TextBox12"
'separate textboxes
   For Each vCtrl In Controls
      If TypeName(vCtrl) = "TextBox" Then
         vAT = vAT & Controls(vCtrl.Name).Name & " "
      End If
   Next vCtrl
   vAT = Split(Trim(vAT), " ")
'loops through textboxes
   For vCtrl = 0 To UBound(vAT)
      If Controls(vAT(vCtrl)) = "" And _
         Not Controls(vAT(vCtrl)).Visible = False Then
         For vN1 = 0 To UBound(vAT)
            If Controls(vAT(vN1)).Top = Controls(vAT(vCtrl)).Top Then
               Controls(vAT(vN1)).Visible = False
               If Not vHeight > 0 Then _
                  vHeight = vHeight + vCtrlHeight + vSpace
            End If
         Next
         For Each vCtrl2 In Controls
            If Controls(vCtrl2.Name).Top > Controls(vAT(vCtrl)).Top Then
               Controls(vCtrl2.Name).Top = Controls(vCtrl2.Name).Top - _
               Controls(vAT(vCtrl)).Height - vSpace
            End If
         Next vCtrl2
         Height = Height - vHeight
      End If
   Next vCtrl

End Sub

 

[Ali M]

This is beyond my knowledgebase.

never mind ! I know it

the UserForm has Two Command Buttons what would happen to them?

actually I want showing them , just ignore empty extboxes if it's possible , if it's not , then this is not problem .

 

[dmt32]

Hi,

To do what you want one way would be to place your textboxes & commandbuttons each in a frame as shown in the image

You then would write code to size the textbox frame1 that calculates its height based on the height of a TextBox in each line that needs to be visible + the space between

Me.Frame1.Height = Me.TextBox.Height + Space * number lines visible.

To place the commandbuttons you then position Frame2

Me.Farme2.Top = Me.Frame1.Top + Me.Frame1.Height+ Space

To size the height of the form

Me.Height = Me.Frame2.Top + Me.Frame2.Height + space

To make suggestion work you would need to share the code with forum that populates the textboxes but the approach should hopefully, give results like this

Dave

 

[Ali M]

@EXCEL MAX I appreciate your trying !
It seems to hide the textboxes are empty even optionbutton & spinbutton also the textbox1,2,3 contains values doesn't show clearly
it doesn't change form size.
this is what I have based on your code

 

[Ali M]

@dmt32 I will test your suggestion if I failed I will come back soon & post my code .

 

[EXCEL MAX]

We don't understand each other.
Solution with frames is just the best approach and it needs new code to be created.
I didn't know what you doing and I was try to give answer to the original post.
Remove frames and make text boxes like in the original post and try then.