Change code from inside sheet to userform directly

KalilMe

Active Member
Joined
Mar 5, 2021
Messages
343
Office Version
  1. 2016
Platform
  1. Windows
Hi
I want changing this code or new code by using user form instead of using C2,E2 . I want using textbox1,textbox2 to fill dates and instead of using G3 I want using combobox1 contains sheets names .
so I want filling dates in textbox1 ,textbox2 and select sheet name from combobox1 , then should be brings date, invoice and total from the lastrow contains TOTAL word is existed in column A with insert TOTAL row to sum the whole amounts are existed in TOTAL column as in listbox1 .
if the cells textbox1 ,textbox2 , combobox1 are empty then should doesn't show anything in listbox1 , but if the combobox1 is just selected sheet name and textbox1,textbox2 are empty then should brings the all of data with insert TOTAL row to sum the whole amounts are existed in TOTAL in listbox1 .
to understand how populate data in listbox on userform then just check this link to understand how data shows inside sheet.
sort data based on two dates and sheet name from sheet to another
the code
VBA Code:
Sub test_2()

Dim ws As Worksheet, ShName$, DateFrom&, DateTo&
Set ws = Sheets("SH2")
ShName = ws.[G2]
DateFrom = ws.[C2]
DateTo = ws.[E2]

If ShName = "" And DateFrom = 0 And DateTo = 0 Then
    ws.[A4].CurrentRegion.Offset(1).Clear
    Exit Sub
End If

With Sheets(ShName).[A1].CurrentRegion.Columns(1)
    If DateFrom = 0 Or DateTo = 0 Then
        a = Filter(.Parent.Evaluate(Replace("transpose(if(@=""Total"",row(@),0))", "@", .Address)), 0, 0)
    Else
        a = Filter(.Parent.Evaluate(Replace("transpose(if(((@)=""Total"")*(offset(@,,1)>=" & DateFrom & ")*(offset(@,,1)<=" & DateTo & "),row(@),0))", "@", .Address)), 0, 0)
    End If
    b = .Parent.[A1].CurrentRegion.Value
End With
    
With ws
    .[A4].CurrentRegion.Offset(1).Clear
    With .Range("A" & Rows.Count).End(3)(2)
        .Offset(, 1).Resize(UBound(a) + 1, 3) = Application.Index(b, Application.Transpose(a), [{2,4,10}])
        .Resize(UBound(a) + 1) = Evaluate("row(1:" & UBound(a) + 1 & ")")
    End With
    With .Range("A" & Rows.Count).End(3)(2)
        .Value = "Total"
        .Offset(, 3) = "=sum(d5:d" & .Row - 1 & ")"
        With .CurrentRegion
            .Borders.LineStyle = 1
            .HorizontalAlignment = xlCenter
            .Columns(4).NumberFormat = "#,0.00"
        End With
    End With
End With

End Sub
and as I said the data could be 7000 rows for each sheet . that's why use sorting with array way to doesn't make code slowness.
if this is not clear I will post pictures by userform.
thanks.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top