Calculate Shipment Cost Userform Excel VBA

You can calculate shipments costs using a Userform with VBA code which works as an interactive way to display calculated data quickly and easily. The Userform was created based on the following query:
‘Thank you! It works great!!!
I want to learn how to code so bad. It is amazing to watch things “come to life”.
I want to tell you what my challenge is now and get your permission (for help) before sending anything to you.
Please know that I try a lot of steps before asking for help, but again, I just don’t understand enough yet.
On the same user form you just helped me with I have some conditions that need to be met to get the actual cost of shipping.
There is the condition of what city the packages will be shipped to because of being within or outside of the territory, but also the weight plays a factor.
I have been trying to work with an If then statement all day but to no avail.
Textbox5 populates with whether it is “Within Territory” or “Outside of Territory”.
TextBox1 the user will type in the weight of the package.
TextBox4 Populates with the Cost of shipment.
My issue is making it figure out the conditions.
Example: If textbox5 = outside of territory then the price per 100lbs will change from what it is when it is within territory.
I have tried:
If TextBox5.Text = “Outside Of Territory” and TextBox1.Text <=153.75 Then

TextBox4.Text = TextBox1 * 10/100*1.3.

I am dividing by 100 because of the per 100lbs charge and the 1.3 is for the 30% fuel surcharge. From Within Territory there are 3 different rates = up to 177.5lbs. is a flat rate of $15.00. Then from 177.6 up to 999lbs. is $6.5,from 1000lbs. up to 1,999 lbs. is $6.00, and from 2000lbs. up to a Max Weight of 9,000lbs. is $5.5 For Outside of Territory= up to 153.75lbs. = the Min of $20.0, then the rates change at the same weight frequency as above and the rates are 10.00, 9.25, 8.00. can you at least point me in the right direction of how I could make this work? Thanks for any help you are willing to give to an aspiring coder! Ron H’

Here’s the Excel VBA code for the command button:

Private Sub CommandButton1_Click()

TextBox2.Value = Application.VLookup(Me.ListBox1.Text, Sheets(“Sheet1”).Range(“A:B”), 2, False)

If TextBox1.Value > 9000 Then
MsgBox “Cannot Accept Freight Above 9000 lbs”
Unload Me

ElseIf TextBox1.Value > 1999 And TextBox2.Text = “WITHIN TERRITORY” Then
TextBox3.Value = (TextBox1.Value / 100) * 5.5

ElseIf TextBox1.Value > 999 And TextBox2.Text = “WITHIN TERRITORY” Then
TextBox3.Value = (TextBox1.Value / 100) * 6

ElseIf TextBox1.Value > 177.5 And TextBox2.Text = “WITHIN TERRITORY” Then
TextBox3.Value = (TextBox1.Value / 100) * 6.5

ElseIf TextBox1.Value <= 177.5 And TextBox2.Text = “WITHIN TERRITORY” Then TextBox3.Value = 15

ElseIf TextBox1.Value > 1999 And TextBox2.Text = “OUTSIDE OF TERRITORY” Then
TextBox3.Value = ((TextBox1.Value / 100) * 8) * 1.3

ElseIf TextBox1.Value > 999 And TextBox2.Text = “OUTSIDE OF TERRITORY” Then
TextBox3.Value = ((TextBox1.Value / 100) * 9.25) * 1.3

ElseIf TextBox1.Value > 153.75 And TextBox2.Text = “OUTSIDE OF TERRITORY” Then
TextBox3.Value = ((TextBox1.Value / 100) * 10) * 1.3

ElseIf TextBox1.Value <= 153.75 And TextBox2.Text = “OUTSIDE OF TERRITORY” Then
TextBox3.Value = 20

End If

End Sub

Watch the video below:


One thought on “Calculate Shipment Cost Userform Excel VBA

  1. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

Leave a Reply

Your email address will not be published. Required fields are marked *