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

T

tubrak

Board Regular
Joined
May 30, 2021
Messages
216
Office Version
  1. 2019
Platform
  1. Windows
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
VBA Code: 
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
VBA Code: 
blc = blc + n - m
to this
Rich (BB code): 
blc = blc - n + m
2- change sheet name from
VBA Code: 
Set sh = Sheets("debit")
to
Rich (BB code): 
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Hi,
try update to your code shown & see if will do what you want

Rich (BB code): 
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
 
  Dim IsDebit As Boolean
  IsDebit = Me.OptionButton1.Value
  
  Set sh = ThisWorkbook.Worksheets(IIf(IsDebit, "Debit", "Credit"))
 
  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 = IIf(IsDebit, blc + n - m, 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

Dave
 
Upvote 0
Hi Dave !
I will test it, but I have notice : what about Private Sub UserForm_Initialize() to specify the sheet?
thanks
 
Upvote 0
tubrak said:
Hi Dave !
I will test it, but I have notice : what about Private Sub UserForm_Initialize() to specify the sheet?
thanks
Click to expand...

I did not look at that part of code - leave it as is & see how testing goes.

Dave
 
Upvote 0
Awesome Dave !
I'm surprised from there is no problem despite of keep the same sheet in Private Sub UserForm_Initialize() !
just I want updating the result when move from optionbutton1 to optionbutton2 and the MY_Text_Change is filled.
I have to clear MY_Text_Change to see the updating, I don't want it .
thanks for your time .
 
Upvote 0
never mind , I got it
VBA Code: 
Private Sub OptionButton1_Click()
Call MY_Text_Change
End Sub

Private Sub OptionButton2_Click()
Call MY_Text_Change
End Sub
thanks very much for your help .;)
 
Upvote 0
Glad suggestion helps & appreciate your feedback

Dave
 
Upvote 0
sorry Dave! it doesn't implement for two sheets . it just implement for CUSTOMERS sheet , that's because of indicates in Private Sub UserForm_Initialize()
I try repeating for your procedure, but it doesn't work!:cry:

Rich (BB code): 
Private Sub UserForm_Initialize()
  Dim IsDebit As Boolean
  IsDebit = Me.OptionButton1.Value
  
  Set sh = ThisWorkbook.Worksheets(IIf(IsDebit, "CUSTOMERS", "CLIENT"))
  'Set sh = Sheets("CUSTOMERS")
  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
 
Upvote 0
Try updating as follows

Rich (BB code): 
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
  
  Dim IsDebit As Boolean
  IsDebit = Me.OptionButton1.Value
  
  Set sh = ThisWorkbook.Worksheets(IIf(IsDebit, "Debit", "Credit"))
  
  a = sh.Range("A1:E" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value

'rest of code

Rich (BB code): 
Private Sub UserForm_Initialize()
  Me.OptionButton1.Value = True
  With My_List
    .ColumnCount = 6
    .ColumnWidths = "105;120;110;70;120;40"
  End With
End Sub

Dave
 
Upvote 1
Solution
perfect ! (y)
I hope there is no problem anymore and sorry again 🙏🙏
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
modifying code by add textboxes on userform to search between two dates
Replies
4
Views
557
tubrak
T
S
  • Question
Change date format inside textbox userform
Replies
1
Views
87
Akuini
Akuini
A
  • Solved
Exclude columns in listbox on userform except first two columns & last column
2
Replies
16
Views
347
abdelfattah
A
C
  • Question
Changing the Rowsource of a userform Listbox with each change in the Listbox selection
Replies
0
Views
251
chazrab
C
A
  • Question
possible calculation in listbox for debit , credit when search based on textbox ?
Replies
0
Views
288
abdelfattah
A

Forum statistics

Threads
1,215,024
Messages
6,122,729
Members
449,093
Latest member
Mnur

Share this page

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