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

 

[Ali M]

@dmt32 yes I will do that .

 

[Ali M]

@EXCEL MAX you're right but just in textboxes , why creates spaces under commandbuttons and how implement your code based on the code populates in textboxes
how replace of this line?

 TextBox4 = "TextBox4": TextBox5 = "TextBox5": TextBox6 = "TextBox6"
   TextBox10 = "TextBox10": TextBox11 = "TextBox11": TextBox12 = "TextBox12"

 

[EXCEL MAX]

Vertically space between textboxes also need to be same, because code works in that way.
The code makes invisible more rows and to resize userform height,
its not enough to calculate only height of the invisible textbox, but vertically space between textboxes also.
In the original post there was nothing about filling textboxes.
We were suppose that you have data in the textboxes.

 

[dmt32]

@dmt32 yes I will do that .

some very light testing I have these results from your data

if I change some of the values positive form now displays like this

can you confirm this is the result you are looking for?

Dave

 

[Ali M]

@EXCEL MAX

Vertically space between textboxes also need to be same, because code works in that way.

but I don't think to be Vertically different space between textboxes based on the pictures . the problem is commandbuttons below as in pic2

In the original post there was nothing about filling textboxes.

the values in textboxes brings by another code , my question is how can I call my macro to brings values into text boxes before running your code . where put it inside your code?

 

[Ali M]

@dmt32 yes it will be great if you can achieve it

 

[EXCEL MAX]

If you have macro to populate textboxes it's great.
Call that macro below "fill your data" comment
and don't forget to delete my example filling textboxes (4,5,6,10,11,12).
If you have more textboxes on the userform you need to tell me that.

 

[Ali M]

If you have more textboxes on the userform you need to tell me that.

currently no , but in the future this possibilty can be , if you have idea about it how deal with more textboxes will be good.

 

[dmt32]

@dmt32 yes it will be great if you can achieve it

Hi,

To clarify, its important that your Userform is configured like this

The textboxes are placed in a Frame named Frame1

The Texboxes have their default names & are configured

TextBox1 TextBox2 TextBox3

TextBox4 TextBox5 TextBox6

Etc.

The CommandButtons are Placed in a Frame named Frame2

Providing this is the case then

• Make backup of your workbook
• delete any previous code you have for this requirement
• place following code in the userform code page

Public BoxSpace As Double, BoxHeight As Double, BoxWidth As Double
Dim ws          As Worksheet

Private Sub UserForm_Activate()
     DisplayTextBoxes Me
End Sub

Private Sub UserForm_Initialize()
    
    'get textbox height, width & spacing
    With Me.TextBox1
        BoxHeight = .Height
        BoxWidth = .Width
        BoxSpace = Me.TextBox4.Top - .Top - BoxHeight
    End With

    Set ws = ThisWorkbook.Worksheets("DATA")
    
    ConfigureForm Me
    
    GetRecord Me, ws
    
End Sub

Note the variables sitting at the TOP – These MUST sit at very TOP of your forms code page OUTSIDE any procedure

• insert 3 standard modules & name them as follows
• ConfigureForm_Code
• DisplayTextBoxes_Code
• GetRecord_Code

And let me know when this has been done.



Dave

 

[EXCEL MAX]

Exclude that text boxes when you collecting other in the array.
Do you see difference?

'separate textboxes
   For Each vCtrl In Controls
      If TypeName(vCtrl) = "TextBox" And _
         Not vCtrl.Name = "TextBox16" And _
         Not vCtrl.Name = "TextBox17" And _
         Not vCtrl.Name = "TextBox18" Then
         vAT = vAT & Controls(vCtrl.Name).Name & " "
      End If
   Next vCtrl
   vAT = Split(Trim(vAT), " ")