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

 

[dmt32]

all of things have updated .

Hi,

Place each of these codes unaltered in their respective standard modules

Do not make any changes to code(s) unless directed otherwise.

Sub DisplayTextBoxes(ByVal Form As Object)
    
    Dim MinHeight   As Double
    Dim FrameHeight As Double
    
    'ensure 1st line is always displayed
    MinHeight = Form.BoxHeight + (Form.BoxSpace * 2)
    
    'get frame height
    FrameHeight = (Form.BoxHeight + Form.BoxSpace) * Form.LineCount

    'size frame heights
    Form.Frame1.Height = IIf(FrameHeight = 0, MinHeight, FrameHeight + Form.BoxSpace)
    Form.Frame2.Top = Form.Frame1.Top + Form.Frame1.Height + Form.BoxSpace
    
    'size form height
    Form.Height = Form.Frame2.Top + (Form.Frame2.Height * 1.5)
    
End Sub

Sub GetRecord(ByVal Form As Object, ByVal ws As Object)

    Dim LastRow         As Long, lngRow As Long
    Dim r               As Long, c As Long
    Dim LineComplete    As Boolean
    
    LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    
    For lngRow = 2 To LastRow
        If ws.Cells(lngRow, "c").Value < 0 Then
            LineComplete = False
            For r = 1 To Form.TextBoxCount Step 3
                For c = 0 To 2
                    With Form.Controls("TextBox" & r + c)
                        If Len(.Value) = 0 Then .Value = ws.Cells(lngRow, 1 + c).Text: LineComplete = True
                    End With
                Next c
                If LineComplete Then Form.LineCount = Form.LineCount + 1: Exit For
            Next r
        End If
nextrow:
    Next lngRow
End Sub

Sub ConfigureForm(ByVal Form As Object)

    Dim BoxTop          As Double, BoxLeft As Double
    Dim FrameWidth      As Double
    Dim r               As Long, c As Long, i As Long
    
    '----------------------------------------------------------------------------------------------------------
    '                                                  SETTINGS
    '----------------------------------------------------------------------------------------------------------
    
    'frame left posistion
    Const FrameLeft     As Long = 30
    'space on form to the right of frame
    Const SpaceToRight  As Long = 130

    'no textboxes in frame 1
    Form.TextBoxCount = 15
    'textbox height
    Form.BoxHeight = 20
    'textbox width
    Form.BoxWidth = 90
    'space between textboxes
    Form.BoxSpace = 6
    
    '----------------------------------------------------------------------------------------------------------
    
    BoxTop = Form.BoxSpace
    For r = 1 To Form.TextBoxCount Step 3
        BoxLeft = Form.BoxSpace
        For c = 0 To 2
            With Form.Controls("TextBox" & r + c)
                .Top = BoxTop
                .Left = BoxLeft
                .Height = Form.BoxHeight
                .Width = Form.BoxWidth
            End With
            BoxLeft = BoxLeft + Form.BoxSpace + Form.BoxWidth
        Next c
        BoxTop = BoxTop + Form.BoxHeight + Form.BoxSpace
    Next r
    
    'size frame width
    FrameWidth = BoxLeft + Form.BoxSpace
    
    'apply frame settings
    For i = 1 To 2
        With Form.Controls("Frame" & i)
            .Caption = ""
            .Left = FrameLeft
            .Width = FrameWidth
            .SpecialEffect = fmSpecialEffectFlat
        End With
    Next i
    
    'size form width
    Form.Width = FrameLeft + FrameWidth + SpaceToRight
    
End Sub

The two codes,

• DisplayTextBoxes
• GetRecord

Are relatively short and the main codes that should do what you want.

I have also included another code

ConfigureForm

which makes solution look tad busy but this code not only sizes & places textboxes in the frame, it allows you (in settings area) to adjust their size & increase number if needed to suit your project need. Do though be mindful that any additional textboxes added (in a row of 3), the naming convention must follow in order.

I would also mention that if it’s your intention to add many more textboxes then suggest rethink the current approach and look at adding textboxes on the fly & scrolling them in the frame rather than sizing the userform to accommodate.



Hope helpful



Dave

 

[Ali M]

magnificiant !
actually I would understand two explanations but keep in your mind theses are minor
first of all this case also occured with @EXCEL MAX 's code . despite when design the form there is no space for commandbuttons below . when run the userform it will create spaces commandbuttons below but not big .
second as you siad

the current approach and look at adding textboxes on the fly & scrolling them in the frame rather than sizing the userform to accommodate.

I'm not sure to understand this sentence .
normally when I add new textboxs inside the frame1 I have to resize the userform to accommodate new textboxes on userform

 

[dmt32]

magnificiant !
actually I would understand two explanations but keep in your mind theses are minor
first of all this case also occured with @EXCEL MAX 's code . despite when design the form there is no space for commandbuttons below . when run the userform it will create spaces commandbuttons below but not big .
second as you siad

I'm not sure to understand this sentence .
normally when I add new textboxs inside the frame1 I have to resize the userform to accommodate new textboxes on userform

Frame2 (commandbuttons) should be automatically placed by code just under Frame1 & userform height sized accordingly - It should not matter where you place Frame2 at design time, the code should sort it all out - All works in examples I have shown.

Adding controls on the "fly" simply means adding them programmatically i.e. by code & not by you at design time. You can add as many as needed & if exceed the height of the frame on the form, a scrollbar appears to allow scrolling.

Anyway, if current approach works for you best leave it at that & glad we were able to assist.

Dave

 

[EXCEL MAX]

Place of the control in the list of controls is relative, so calling them without name can make mistake when coding.
Look in the panel properties for each textbox control you want to ignore the first property called "(Name)",
and ignore this controls in the code by this name.
You need to ignore only unnecessary textboxes, not other controls.
The "Const vSpace = 6" and "Const vCtrlHeight = 15" in the code means that your textboxes have this layout.

I'm also glad that you found a solution for your problem.
So when you get free time explore some possibilities of this code.

 

[Ali M]

@EXCEL MAX I create new form and make equal spaces among the textboxes . it works perfectly , also I want to inform you the problem of spaces in commandbutton below is gone because I change value this Const vCtrlHeight = 18 or 22 as what I need
all of things are good
thanks for you & Dave

 

[EXCEL MAX]

Thanks for feedback.
Sometimes coding seems not hard as is hard to explaine.
I'm glad that you did't gave up.
I didn't test it but I belive Dave's solution works well.
Hope you have learn lot of new stuff.