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

problem distributing values on the userfrom

0

Hi experts  

I face  problem  How  can  I  distrube  values  for  COSTING (textbox7) on others textboxes 13,14,15,16,17( BALANCE) .

the  first  step  for  my  project fill QTY,PRICE  then  will  calculate  and  show  in the  BALANCE   and  sum  the  SUBTOTAL  in textbox  6     this  is  ok 

the  second  step when  I  fill  COSTING(textbox7 ) then textbox7 /  textbox 6  and  the  result  should  multiply  for  each textbox  in BALANCE   after  that  should  sum  over previous value. let's take  example  I  suppose textbox1=10 , textbox2=30, calculation in textbox13= 300and  textbox 6 =300 if  I  write  200 in textbox7  then 

first I  want this 200/300=0.66666 ,this  should  be =0.66666X300(textbox13)=200  and  this  result  should  add  over  the  value  in textbox 13(300)  then  the  final value  in text box13=500

the  code  doesn't  work  as  what  I  want  sometimes  gives  wrong values  and  somtimes  doesn't  gives  any  thing  If  I  don't  fill all  of  the  textboxes but  no  in all  of  about  I  need filling  all of  textboxes.

Private Sub TextBox7_AfterUpdate()
Dim tot, tot1, tot2, tot3, tot4 As Double
    Dim exp As Double

    If TextBox1 <> "" And TextBox8 <> "" And TextBox13 <> "" And TextBox7.Value <> "" Then
    If TextBox2 <> "" And TextBox9 <> "" And TextBox14 <> "" And TextBox7.Value <> "" Then
    If TextBox3 <> "" And TextBox10 <> "" And TextBox15 <> "" And TextBox7.Value <> "" Then
    If TextBox4 <> "" And TextBox11 <> "" And TextBox16 <> "" And TextBox7.Value <> "" Then
    If TextBox5 <> "" And TextBox12 <> "" And TextBox17 <> "" And TextBox7.Value <> "" Then

        tot = CDbl(TextBox1.Value) * CDbl(TextBox8.Value)
        tot1 = CDbl(TextBox2.Value) * CDbl(TextBox9.Value)
        tot2 = CDbl(TextBox3.Value) * CDbl(TextBox10.Value)
        tot3 = CDbl(TextBox4.Value) * CDbl(TextBox11.Value)
        tot4 = CDbl(TextBox5.Value) * CDbl(TextBox12.Value)





        If TextBox7 <> "" Then

            exp = CDbl(TextBox7.Value) / CDbl(TextBox6.Value)

           ' If TextBox7 = "" Then Call SUM_VALUES


        TextBox13.Value = tot * (1 + exp)
        TextBox14.Value = tot1 * (1 + exp)
        TextBox15.Value = tot2 * (1 + exp)
        TextBox16.Value = tot3 * (1 + exp)
        TextBox17.Value = tot4 * (1 + exp)
    End If
    End If
    End If
    End If
    End If
    End If



  TextBox7.Value = Format(TextBox7.Value, "#,###.00")
End Sub
Answer
Discuss

Discussion

Hi Abdo.

I'm not clear on what your question means. Do you mean
  1) there could be empty Qty and/or Price text boxes?
  2) any cost value added to Costing (TextBox7) should be distributed proportionately between populated rows (and added to the unit Price?

Also, should your example read "suppose textbox1=10 , textbox8=30, calculation in textbox13= 300"?
John_Ru (rep: 3992) Aug 28, '22 at 7:43 am
Hi John 
  1) there could be empty Qty and/or Price text boxes?
yes  surely

  2) any cost value added to Costing (TextBox7) should be distributed proportionately between populated rows (and added to the unit Price?
yes  
Also, should your example read "suppose textbox1=10 , textbox8=30, calculation in textbox13= 300"?
sorry  about  error . yes  you  right   
Abdo M (rep: 6) Aug 28, '22 at 8:27 am
Add to Discussion

Answers

0
Selected Answer

Abdo

From your question and discussion point, I think your aim is to set prices and quantities first then add a cost (which is distributed between priced items). This seems an odd approach to me but this should help...

The attached file includes a revised Sub TextBox7_AfterUpdate() which will takes a value from TextBox7 (now with a label "New Cost"), distributes it in proprortion to existing Balance figures (only) then transfers that value to a new label ("Label20") which records the distributed cost. You can then add another New Cost if you like.

Here's the revised code, with comments for guidance:

Private Sub TextBox7_AfterUpdate()

Dim exp As Double
If TextBox7 = "" Then Exit Sub

    Application.EnableEvents = False
    ' calculate price uplift
    exp = (CDbl(TextBox7.Value) + CDbl(TextBox6.Value)) / CDbl(TextBox6.Value)
    'loop Balances
    For n = 13 To 17
        With Controls("TextBox" & n)
            ' if Balance>0 then distribute new cost and adjust Price
            If .Value <> "" Then
               .Value = Format(.Value * exp, "#,###.00")
               Controls("TextBox" & n - 5).Value = .Value / Controls("TextBox" & n - 12).Value
            End If
        End With
    Next n

    ' move cost to label
    Label20.Caption = Format(CDbl(Label20.Caption) + TextBox7.Value, "#,###.00")
    TextBox7.Value = ""

    Application.EnableEvents = True

End Sub

(Note that your sub is still there, just commented out).

The trouble with this approach is if the costs aren't neat integers, then the Balances and Prices will start to look fractional.

Hope this helps (or at least points you in the right direction).

Discuss

Discussion

thanks  but  I  don't  see  any  changes  for price and  balance  after  fill textbox7 !!!
Abdo M (rep: 6) Aug 28, '22 at 9:37 am
Sorry- please see revised file. I chnaged the macro at the last moment but the moved test:
If TextBox7 <> "" Then Exit Sub

needed to read:
If TextBox7 = "" Then Exit Sub

It does now and the file should work.
John_Ru (rep: 3992) Aug 28, '22 at 9:50 am
This seems an odd approach to me
I don't know what you have in mind about this approach, but  if  I  write  costing  before  fill  the  others  textboxes  it  will shows error  beacuase the  textbox6 is  empty   there is  no  value  when  fill textbox7 in first , I  tried   this  way ,  so    I've  found  the  best  way   the  texbox7  should  be  the  last filling  to  do  that .
I  like  about  the lable  , good  idea .
thanks  for  your  solution &  time.
Abdo M (rep: 6) Aug 28, '22 at 10:18 am
Thanks for selecting my Answer, Abdo.

I found it an aodd approach because it's more common to determine prices having knowledge of costs and their relationship to quantities (rather than set prices then increase them for the costs). If that approach suits you, okay. 
John_Ru (rep: 3992) Aug 28, '22 at 10:22 am
Add to Discussion


Answer the Question

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