modifying code to implement for two optionbuttons on userform and two sheets

[tubrak]

I want modifying code for mr.DanteAmore
the code is modified more than one time, this is the last version .
modifying code by add textboxes on userform to search between two dates
so I want when select optionbutton1 ,then should implement code as it's

Option Explicit

Dim sh As Worksheet
Dim a As Variant



Private Sub MY_Text_Change()
  'Call CommandButton4_Click
   Dim i As Long, t As Long
  Dim dbt As Double, cdt As Double, blc As Double
  Dim n As Double, m As Double
  Dim tbx1 As String, tbx2 As String, tbxm As String
  
  With MY_List
    .Clear
    For i = 2 To UBound(a, 1)
      tbxm = MY_Text.Value
      With TextBox1
        If Len(TextBox1.Value) = 10 And IsDate(TextBox1.Value) And _
           Len(TextBox2.Value) = 10 And IsDate(TextBox2.Value) Then
          tbx1 = TextBox1.Value
          tbx2 = TextBox2.Value
        Else
          tbx1 = a(i, 1)
          tbx2 = a(i, 1)
        End If
      End With
      
      If i = 1 Then
        .AddItem
        .List(t, 0) = Format(a(i, 1), "yyyy/mm/dd")
        .List(t, 1) = a(i, 2)
        .List(t, 2) = a(i, 3)
        .List(t, 3) = Format(a(i, 4), "#,##0.00")
        .List(t, 4) = Format(a(i, 5), "#,##0.00")
        t = t + 1
      ElseIf LCase(a(i, 3)) Like "*" & LCase(tbxm) & "*" And _
        a(i, 1) >= CDate(tbx1) And a(i, 1) <= CDate(tbx2) Then
        .AddItem
        .List(t, 0) = Format(a(i, 1), "yyyy/mm/dd")
        .List(t, 1) = a(i, 2)
        .List(t, 2) = a(i, 3)
        .List(t, 3) = Format(a(i, 4), "#,##0.00")
        .List(t, 4) = Format(a(i, 5), "#,##0.00")
       ' .List(t, 5) = Format(a(i, 6), "#,##0.00")
        If i = 1 Then
          If MY_Text.Value <> "" Then .List(t, 5) = "BALANCE"
        Else
          If IsNull(a(i, 4)) Then n = 0 Else n = a(i, 4)
          dbt = dbt + n
          If IsNull(a(i, 5)) Then m = 0 Else m = a(i, 5)
          cdt = cdt + m
          blc = blc + n - m
          If MY_Text.Value <> "" Then .List(t, 5) = Format(blc, "#,##0.00")
        End If
        t = t + 1
      End If
    Next
    Deb_txt.Value = Format(dbt, "#,##0.00")
    Cre_txt.Value = Format(cdt, "#,##0.00")
    Bal_txt.Value = Format(blc, "#,##0.00")
  End With
End Sub

Private Sub TextBox1_Change()
'date From
 ' Call CommandButton4_Click
 Call MY_Text_Change
End Sub

Private Sub TextBox2_Change()
'date To
 ' Call CommandButton4_Click
 Call MY_Text_Change
End Sub

Private Sub UserForm_Initialize()
  Dim LastRow As Long
  Set sh = Sheets("debit")
  a = sh.Range("A1:E" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value
  With MY_List
    .ColumnCount = 6
    .ColumnWidths = "105;120;110;70;120;40"
    
  End With
End Sub

Private Sub MY_List_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = vbKeyEscape Then
    Me.MY_Text = ""
    Me.MY_Text.SetFocus
  ElseIf KeyCode = vbKeyF12 Then
    Unload Me
  End If
End Sub

and if I select optionbutton2 then should implement the same code with will change just two things

1- change line this

blc = blc + n - m

to this

blc = blc - n + m

2- change sheet name from

Set sh = Sheets("debit")

to

Set sh = Sheets("credit")

I believe there is better way repeat the whole codes for new sheet by add a little lines. I hope the experts or owner's code help me
Thanks

 

[dmt32]

perfect !
I hope there is no problem anymore and sorry again

no worry, always a little tricky when you modify another's code but glad updates helped.

Dave