Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

calculation customers accounts by userform

0

Hello Guys !

I  try  to  calculatie  customer accounts  based  on  userfrom . I  put   two  formulas  for  each customers  .  the  first  formula  will  add from  the  first  time  when add new  name and  the  second  formual will  be  directly  under  first  formula . so  my  goal   copy  data  from userform to  two  sheets .   to  understand  how  could  be .  when I  select optionbutton3  will change  sheet name  in label3  then  should  copy  to  sheet  based on LABEL3   and  when  fill textbox1,2   should  copy  to  column D,E  and  combobox1  to  column B  , optonbuttons1,2  names copy  to  column C  .  after select optionbutton3 or  optionbutton4 to specify   which  sheet  should  copy  to  it I will select  optionbutton1 and  fill textbox1 and  textbox2 is  empty  will copy  textbox1  to  column D and when  select  optionbutton2 and  fill textbox2 and  textbox1 is  empty  will copy  textbox2  to  column E .sometimes  when I  select optionbutton1 or  optionbutton2  I  will fill textbox1, textbox2 at  the  same  time  should  copy  to  column D,E  in  the  same  row  .

last  thing  based  on  the  orginal  code  when  add  the  name   and  the  name  is  already  existed  then  will add after the  last row  to  give  me  the  final  result (this  means  should  put  data for  the  same  name  under  each  other  of   them )

Private Sub CommandButton1_Click()
Dim Valu As Double, Fnd As Range, rw As Long, nr As Long, col As Long
col = IIf(OptionButton1 = True, 4, 5)
With Sheets("" & Label3 & "")
    nr = .Cells(.Rows.Count, 6).End(xlUp).Row + 1
    Set Fnd = .Columns(2).Find(ComboBox1, .Columns(2).Cells(1), , , , xlPrevious)
    If Fnd Is Nothing Then
        Valu = 0
        rw = nr
    Else
        Valu = .Range("F" & Fnd.Row).Value
        rw = Fnd.Row + 1
    End If
    .Rows(rw).Insert: If rw = 2 Then .Rows(rw).Interior.Color = xlNone: .Rows(rw).Font.Bold = False
    If col = 5 Then
        .Range("B" & rw).Resize(, 5) = Array(ComboBox1, "not paid", TextBox1.Value, "", Valu + Val(TextBox1.Value))
    Else
        .Range("B" & rw).Resize(, 5) = Array(ComboBox1, "paid", "", TextBox2.Value, Valu - Val(TextBox2.Value))
    End If
    With .Cells(1).CurrentRegion
        .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = Date
    End With
    End With
End Sub



Private Sub OptionButton3_Click()
If OptionButton3.Value = True And OptionButton4.Value = False Then
Label3.Caption = "SS"

End If
End Sub

Private Sub OptionButton4_Click()
If OptionButton4.Value = True And OptionButton3.Value = False Then
Label3.Caption = "TT"
End If
End Sub


Answer
Discuss

Discussion

Hi Alaa

Sorry but I'm not clear what your question is. What needs fixing in your code (or what happens or doesn't happen at present)?

As I just said to Leap on another question,  I have little time in the next 3 days so may not nbe able to help this time.
John_Ru (rep: 6142) Oct 26, '22 at 5:17 pm
Hi John
my  problem  when fill textbox1,textbox2   just copy one  of  them  and  ignore  the  other to columns D,E in sheet .
just  test  it  , you  will  see  the  problem .
select name  from combobox1 and  fill  textbox1,2  and  select optionbutton 1,3 
the  same  thing , select  optionbutton2,4 and  fill  the  others   .remember  the  problem is   just for two  textboxes  together.  when fill them  should  copy  in  the  same  row  .
I  hope to   gave  you  clue  to  follow  this  problem.
Alaa (rep: 28) Oct 27, '22 at 2:35 am
Add to Discussion

Answers

0
Selected Answer

Alaa

In the attached file, I modifed your code as in bold below:

Private Sub CommandButton1_Click()
Dim Valu As Double, Fnd As Range, rw As Long, nr As Long, col As Long
col = IIf(OptionButton1 = True, 4, 5)

' check that option buttons were picked
If Me.Label3.Caption = "SHEETS" Then
    MsgBox "Please pick " & Me.OptionButton3.Caption & " and/or " & Me.OptionButton4.Caption
    Exit Sub
End If

With Sheets("" & Label3 & "")
    nr = .Cells(.Rows.Count, 6).End(xlUp).Row + 1
    Set Fnd = .Columns(2).Find(ComboBox1, .Columns(2).Cells(1), , , , xlPrevious)
    If Fnd Is Nothing Then
        Valu = 0
        rw = nr
    Else
        Valu = .Range("F" & Fnd.Row).Value
        rw = Fnd.Row + 1
    End If
    .Rows(rw).Insert: If rw = 2 Then .Rows(rw).Interior.Color = xlNone: .Rows(rw).Font.Bold = False
    If col = 5 Then
        .Range("B" & rw).Resize(, 5) = Array(ComboBox1, "not paid", TextBox1.Value, TextBox2.Value, Valu + Val(TextBox1.Value) - Val(TextBox2.Value))
    Else
        .Range("B" & rw).Resize(, 5) = Array(ComboBox1, "paid", TextBox1.Value, TextBox2.Value, Valu + Val(TextBox1.Value) - Val(TextBox2.Value))
    End If
    With .Cells(1).CurrentRegion
        .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = Date
    End With
    End With
End Sub

The first change adds a test to ensure a sheet was selected (via OptionButtons 3 or 4).

The second bold change adds the Credit (Textbox1) and Debit (TextBox2) values (and modifies the balance) in the same way whether it is paid or not paid, using the line:

.Range("B" & rw).Resize(, 5) = Array(ComboBox1, "not paid",  _
TextBox1.Value, TextBox2.Value, Valu + Val(TextBox1.Value) - Val(TextBox2.Value))

I also tweaked your UserForm so that the Case option are clser to the SHEETS label (which is changed by them)

I'm afraid this answer is a bit rushed but I hope it helps.

Discuss

Discussion

great  work John !
there  is  no  rushing . 
every  thing  works  excellently
thanks  very  much  for  your  time & assistance
Alaa (rep: 28) Oct 27, '22 at 8:09 am
Glad it worked. Thanks for selecting my Answer, Alaa.
John_Ru (rep: 6142) Oct 27, '22 at 8:14 am
Just modified my answer to correct some typos.
John_Ru (rep: 6142) Oct 27, '22 at 8:16 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login