I need procedure to add it the codes to increase & decrease form & listbox size based on showed data in listbox when run the userform or when fill textbox1 item based on column 4 ,show data in listbox , whenever the data increase in listbox then should increase the useform & listbox1 sizes and whenever the data decrease in listbox then should decrease the useform & listbox1 sizes
shape of form
this is the whole codes
thanks
shape of form
this is the whole codes
VBA Code:
Dim a As Variant
Dim MySum As Double, MySum1 As Double
Private Sub OptionButton1_Click()
Dim lindex As Long
Dim strAdd As String
If OptionButton1.Value = True Then
If Not Len(TextBox2) = 0 And Not Len(TextBox3) = 0 Then
strAdd = OptionButton1.Caption & " "
For lindex = 0 To ListBox1.ListCount - 1
ListBox1.List(lindex, 8) = strAdd & Format(MySum, "#,##0.00")
ListBox1.List(lindex, 9) = strAdd & Format(MySum1, "#,##0.00")
Next
TextBox2 = strAdd & Format(MySum, "#,##0.00")
TextBox3 = strAdd & Format(MySum1, "#,##0.00")
End If
End If
End Sub
Private Sub OptionButton2_Click()
Dim lindex As Long
Dim strAdd As String
If OptionButton2.Value = True Then
If Not Len(TextBox2) = 0 And Not Len(TextBox3) = 0 Then
strAdd = OptionButton2.Caption & " "
For lindex = 0 To ListBox1.ListCount - 1
ListBox1.List(lindex, 8) = strAdd & Format(MySum, "#,##0.00")
ListBox1.List(lindex, 9) = strAdd & Format(MySum1, "#,##0.00")
Next
TextBox2 = strAdd & Format(MySum, "#,##0.00")
TextBox3 = strAdd & Format(MySum1, "#,##0.00")
End If
End If
End Sub
Private Sub OptionButton3_Click()
Dim lindex As Long
Dim strAdd As String
If OptionButton3.Value = True Then
If Not Len(TextBox2) = 0 And Not Len(TextBox3) = 0 Then
strAdd = OptionButton3.Caption & " "
For lindex = 0 To ListBox1.ListCount - 1
ListBox1.List(lindex, 8) = strAdd & Format(MySum, "#,##0.00")
ListBox1.List(lindex, 9) = strAdd & Format(MySum1, "#,##0.00")
Next
TextBox2 = strAdd & Format(MySum, "#,##0.00")
TextBox3 = strAdd & Format(MySum1, "#,##0.00")
End If
End If
End Sub
Function FilterData()
Dim i As Long, ii As Long, n As Long
Me.ListBox1.List = a
If Me.TextBox1 = "" Then Exit Function
With Me.ListBox1
.Clear
For i = 0 To UBound(a, 1)
If UCase$(a(i, 3)) Like UCase$(Me.TextBox1) & "*" Then
.AddItem
.List(n, 0) = n + 1
For ii = 1 To UBound(a, 2)
.List(n, ii) = a(i, ii)
Next
n = n + 1
End If
Next
End With
Dim r As Long
''Dim MySum, MySum1 As Double 'moved and corrected
MySum = 0
MySum1 = 0
With ListBox1
For r = 0 To .ListCount - 1
MySum = MySum + .List(r, 7)
MySum1 = MySum1 + .List(r, 9)
Next r
End With
TextBox2.Value = Format(MySum, "#,##0.00")
TextBox3.Value = Format(MySum1, "#,##0.00")
End Function
Private Sub TextBox1_AfterUpdate()
FilterData
End Sub
Private Sub TextBox2_AfterUpdate()
FilterData
End Sub
Private Sub TextBox3_AfterUpdate()
FilterData
End Sub
Private Sub UserForm_Initialize()
Dim lindex&
Dim rngDB As Range, rng As Range
Dim i, myFormat(1) As String
Dim sWidth As String
Dim vR() As Variant
Dim n As Integer
Dim myMax As Single
Set rngDB = Range("A2:J20")
For Each rng In rngDB
n = n + 1
ReDim Preserve vR(1 To n)
vR(n) = rng.EntireColumn.Width
Next rng
myMax = WorksheetFunction.Max(vR)
For i = 1 To n
vR(i) = myMax
Next i
With Sheets("purchase").Cells(1).CurrentRegion
myFormat(0) = .Cells(2, 8).NumberFormatLocal
myFormat(1) = .Cells(2, 9).NumberFormatLocal
Set rng = .Offset(1).Resize(.Rows.Count - 1)
a = .Cells(1).CurrentRegion.Value
End With
sWidth = Join(vR, ";")
Debug.Print sWidth
With ListBox1
.ColumnCount = 10
.ColumnWidths = sWidth '<~~ 63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63
.List = rng.Value
.BorderStyle = fmBorderStyleSingle
For lindex = 0 To .ListCount - 1
'.List(lindex, 0) = (Format((.List(lindex, 0)), "dd/mm/yyyy")) ' BL = dates
.List(lindex, 0) = lindex + 1
.List(lindex, 7) = Format$(.List(lindex, 7), myFormat(0))
.List(lindex, 8) = Format$(.List(lindex, 8), myFormat(1))
.List(lindex, 9) = Format$(.List(lindex, 9), myFormat(1))
Next
a = .List
'<--- this line
End With
End Sub
