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]

If you're wanting to have a smaller Userform but have a lot of controls, have you tried using a Multipage on your userform. Look in the Toolbox for multipage.
Then only put the controls you need on each Multipage Page
So, on Project1 put just the controls you need for that multipage page
A Multipage is sort of like a workbook with numerous sheets
The controls you always need to see put them on the userform but not on the Multipage Page

 

[Ali M]

@dmt32 this is my code

Private Sub UserForm_Initialize()

Dim i As Long, lastrow As Long, ws As Worksheet
Set ws = Sheets("DATA")
lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
If ws.Cells(i, "c") < 0 Then
TextBox1.Value = Range("a" & i).Value
TextBox2.Value = Range("b" & i).Value
TextBox3.Value = Range("c" & i).Value
TextBox4.Value = Range("a" & i).Value
TextBox5.Value = Range("b" & i).Value
TextBox6.Value = Range("c" & i).Value
TextBox7.Value = Range("a" & i).Value
TextBox8.Value = Range("b" & i).Value
TextBox9.Value = Range("c" & i).Value
TextBox10.Value = Range("a" & i).Value
TextBox11.Value = Range("b" & i).Value
TextBox12.Value = Range("c" & i).Value
End If
Next
End Sub

 

[Ali M]

@EXCEL MAX thanks , but it just hide empty textboxes .it left spaces for empty textboxes has been hidden .without decrease the form size by move to up or move to the bottom based on filled textboxes .

 

[dmt32]

@dmt32 this is my code

This is helpful but code only populates 12 textboxes whereas your image in #post1 shows 15 textboxes?
Also, are you able to post some sample data using MrExcel Addin XL2BB - Excel Range to BBCode


Dave

 

[Ali M]

sorry Dave ! you'r right should be 15 textboxes . I was in hurry .

 

[dmt32]

sorry Dave ! you'r right should be 15 textboxes . I was in hurry .

can you post your complete code (you have each row of textboxes accessing the same ranges - is this correct?) & sample data if possible

Dave

 

[Ali M]

(you have each row of textboxes accessing the same ranges - is this correct?)

surely wrong , I will find way to fix it .
the data in sheet

CAKL.xlsm
	A	B	C
1	ITEM	CODE	QTY
2	1	CLA12	-100.00
3	2	MS-11	200.00
4	3	CLA13	-200.00
5	4	MS-13	200.00
6	5	MS-14	200.00
7	6	MS-15	200.00
8	7	MS-16	200.00
9	8	MS-17	200.00
10	9	MS-18	-200.00
11	10	MS-19	200.00
12	11	MS-20	-100.00
13	12	MS-21	-200.00
DATA

the userform when run it should be like this based on condition in sheet DATA

the whole code

Private Sub UserForm_Initialize()

Dim i As Long, lastrow As Long, ws As Worksheet
Set ws = Sheets("DATA")
lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
If ws.Cells(i, "c") < 0 Then
TextBox1.Value = Range("a" & i).Value
TextBox2.Value = Range("b" & i).Value
TextBox3.Value = Range("c" & i).Value
TextBox4.Value = Range("a" & i).Value
TextBox5.Value = Range("b" & i).Value
TextBox6.Value = Range("c" & i).Value
TextBox7.Value = Range("a" & i).Value
TextBox8.Value = Range("b" & i).Value
TextBox9.Value = Range("c" & i).Value
TextBox10.Value = Range("a" & i).Value
TextBox11.Value = Range("b" & i).Value
TextBox12.Value = Range("c" & i).Value
TextBox13.Value = Range("a" & i).Value
TextBox14.Value = Range("b" & i).Value
TextBox15.Value = Range("c" & i).Value

End If
Next
End Sub

 

[EXCEL MAX]

Form new image I see textboxes are not aligned on the top.
My code Is based on same textbox "Top" property in Each line.
Suppose you should need to take care is it each three textboxes
on the same "Top" property and make the same distance between each line with textboxes.
Align textboxes carefully and properly and try code again.

 

[Ali M]

@EXCEL MAX for clarification the new picture for my orginal code how works as Dave ask me for

you have each row of textboxes accessing the same ranges - is this correct?

about your guiding me I will test it again and come back soon

 

[dmt32]

surely wrong , I will find way to fix it .

Have you placed the TextBoxes In a Frame named Frame1 & the CommandButtons in a Frame named Frame2 as shown in #post 7?

Dave