Hello Everyone,
I have write a macro code for userforms. I have made a calculator wherein there is a dependent drop down Combox1 when selected will give the depending drop down list in Combox2.
And there is a Textbox1 wherein the user enters the Price of a product and clicks on CommandButton1 to compute the data.
I have written the computational codes for CommandButton1 below:
-----------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim answer As Integer
Select Case Me.ComboBox2
Case Is = ""
answer = MsgBox("Please enter the values in the box above")
Case Is = "Books"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Movies"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Music"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Video Games - Games & Accessories"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Video Games - Console"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Non Educational Software"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Educational Software"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Baby Products"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Beauty"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Luxury Beauty"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Gourmet"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Pet Food"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Pet Accessories"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Health and Personal Care"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Nursing and Feeding"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Medical Equipment"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Nutrition"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "HPC - Body Support"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Beauty - Fragrance"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Personal Care Appliances"
TextBox2.Value = TextBox1.Value * 0.09
Case Is = "Apparel"
TextBox2.Value = TextBox1.Value * 0.17
Case Is = "Apparel Accessories, Innerwear and Sleepwear"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Eyewear"
TextBox2.Value = TextBox1.Value * 0.15
Case Is = "Watches"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Luggage"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Handbags"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Shoes"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Fashion Jewellery"
TextBox2.Value = TextBox1.Value * 0.18
Case Is = "Mobile Phones and Tablets"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Electronics - Devices"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Electronics - PC (PCs, Laptops, Printer, Scanner)"
TextBox2.Value = TextBox1.Value * 0.04
Case Is = "Electronics - Storage Devices"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Electronics - Data Cables"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Electronics - Cases/Cover/Skin/Screen guard"
TextBox2.Value = TextBox1.Value * 0.17
Case Is = "Electronics - Kindle accessories"
TextBox2.Value = TextBox1.Value * 0.2
Case Is = "Electronic Devices - Bags and Sleeves"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "PC components (RAM, Motherboards)"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Accessories - Electronics, PC, Mobile Phones, Tablets (excluding Storage Devices and PC Components)"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Warranty Services"
TextBox2.Value = TextBox1.Value * 0.5
Case Is = "Business Industrial & Scientific Supplies (BISS)"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Home - Cushion & Covers"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Home - others"
TextBox2.Value = TextBox1.Value * 0.15
Case Is = "Clocks"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Bed & Bath Linen"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Lawn & Garden"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Indoor Lighting"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Home Improvement"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "*******"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Home - Small Appliances"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Toys"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Sporting Goods"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Automotive-Tyres and Rims"
TextBox2.Value = TextBox1.Value * 0.03
Case Is = "Automotive-Helmets, Lubricants, Parts, Vehicle Care"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Automotive- Accessories"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Automotive- Other subcategories"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Large Appliances"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Musical Instruments"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Office Products"
TextBox2.Value = TextBox1.Value * 0.07
Case Is = "Consumable Physical Gift Card"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Pantry"
TextBox2.Value = TextBox1.Value * 0.13
End Select
TextBox3.Value = (TextBox2.Value * 0.145) + TextBox2.Value
If TextBox1.Value <= 250 Then
TextBox4.Value = TextBox3.Value + 0
ElseIf TextBox1.Value <= 500 Then
TextBox4.Value = TextBox3.Value + 5.725
ElseIf TextBox1.Value > 500 Then
TextBox4.Value = TextBox3.Value + 11.45
End If
TextBox6.Value = TextBox1.Value - TextBox4.Value
End Sub
---------------------------------------------------------------
When the macro is run by an user the calculator window opens and the user has to select the drop down menu and also enter the price and then click on Calculate that is the commandbutton1.
When a user has selected all the drop down list and entered the value in the textbox1 to compute the data, The output is correct and there is no error message.
But when a user just randomly clicks on the commandbutton1 unknowingly or purposefully then there is a message box I have given stating "Please enter the values in the above box"
and when the user clicks on "Ok" button in the message box the error message window pops up stating "Run time error 13 type mismatch" and the whole sheet and the calculator closes.
Please help me in overcoming this error as I am sharing this sheet to users who do no have knowledge about macro or any error codes that pops up and they will not be able to understand the errors or debug it.
And also I do not want the sheet to close or the calculator to close when the error message breaks the calculation.
Below is the Code I have written for Combobox1 and Combobox2 drop down selection:
Private Sub ComboBox1_Change()
Me.ComboBox2 = ""
Select Case Me.ComboBox1
Case "Media"
Me.ComboBox2.RowSource = "Media"
Case "Consumables"
Me.ComboBox2.RowSource = "Consumables"
Case "Softline"
Me.ComboBox2.RowSource = "Softline"
Case "CE_PC"
Me.ComboBox2.RowSource = "CE_PC"
Case "Other Hardlines"
Me.ComboBox2.RowSource = "Other_Hardlines"
End Select
End Sub
-----------------------------------------------------------------------
I am not sure how to attach an image of the userform or the screen shot of it so I have made an effort for you guys to understand how the userform or the calculator I have made looks like.
Price Calculator: (Using UserForms)
------------------------------------------------------------------
Category(Label) [Drop down list(Combobox1)]v
Sub-Category(Label) [Drop down list(Combobox2)]v
Price(Label) [Textbox] /*User enters the price of the product in this textbox*/
Calculate(Label) [CommandButton] /*Here the code for computing is written to calculate the required data*/
Referral Fees(Label) [Textbox] /*Output is given as per the code is commandbutton*/
14.5% Tax(Label) [Textbox] /*Output is given as per the code is commandbutton*/
Closing Balance+
14.5% Tax (Label) [Textbox] /*Output is given as per the code is commandbutton*/
Amount Paid To
Seller(Label) [Textbox] /*Output is given as per the code is commandbutton*/
---------------------------------------------------------------
Kindly help me out in getting this calculator successfully running without any errors. And please guide me the steps I need to change detailed as I am still learning the vba macro.
Thank You all In Advance.
Have a Great Day All.
I have write a macro code for userforms. I have made a calculator wherein there is a dependent drop down Combox1 when selected will give the depending drop down list in Combox2.
And there is a Textbox1 wherein the user enters the Price of a product and clicks on CommandButton1 to compute the data.
I have written the computational codes for CommandButton1 below:
-----------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim answer As Integer
Select Case Me.ComboBox2
Case Is = ""
answer = MsgBox("Please enter the values in the box above")
Case Is = "Books"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Movies"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Music"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Video Games - Games & Accessories"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Video Games - Console"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Non Educational Software"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Educational Software"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Baby Products"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Beauty"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Luxury Beauty"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Gourmet"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Pet Food"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Pet Accessories"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Health and Personal Care"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Nursing and Feeding"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Medical Equipment"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Nutrition"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "HPC - Body Support"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Beauty - Fragrance"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Personal Care Appliances"
TextBox2.Value = TextBox1.Value * 0.09
Case Is = "Apparel"
TextBox2.Value = TextBox1.Value * 0.17
Case Is = "Apparel Accessories, Innerwear and Sleepwear"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Eyewear"
TextBox2.Value = TextBox1.Value * 0.15
Case Is = "Watches"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Luggage"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Handbags"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Shoes"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "Fashion Jewellery"
TextBox2.Value = TextBox1.Value * 0.18
Case Is = "Mobile Phones and Tablets"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Electronics - Devices"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Electronics - PC (PCs, Laptops, Printer, Scanner)"
TextBox2.Value = TextBox1.Value * 0.04
Case Is = "Electronics - Storage Devices"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Electronics - Data Cables"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Electronics - Cases/Cover/Skin/Screen guard"
TextBox2.Value = TextBox1.Value * 0.17
Case Is = "Electronics - Kindle accessories"
TextBox2.Value = TextBox1.Value * 0.2
Case Is = "Electronic Devices - Bags and Sleeves"
TextBox2.Value = TextBox1.Value * 0.13
Case Is = "PC components (RAM, Motherboards)"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Accessories - Electronics, PC, Mobile Phones, Tablets (excluding Storage Devices and PC Components)"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Warranty Services"
TextBox2.Value = TextBox1.Value * 0.5
Case Is = "Business Industrial & Scientific Supplies (BISS)"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Home - Cushion & Covers"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Home - others"
TextBox2.Value = TextBox1.Value * 0.15
Case Is = "Clocks"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Bed & Bath Linen"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Lawn & Garden"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Indoor Lighting"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Home Improvement"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "*******"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Home - Small Appliances"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Toys"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Sporting Goods"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Automotive-Tyres and Rims"
TextBox2.Value = TextBox1.Value * 0.03
Case Is = "Automotive-Helmets, Lubricants, Parts, Vehicle Care"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Automotive- Accessories"
TextBox2.Value = TextBox1.Value * 0.12
Case Is = "Automotive- Other subcategories"
TextBox2.Value = TextBox1.Value * 0.1
Case Is = "Large Appliances"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Musical Instruments"
TextBox2.Value = TextBox1.Value * 0.08
Case Is = "Office Products"
TextBox2.Value = TextBox1.Value * 0.07
Case Is = "Consumable Physical Gift Card"
TextBox2.Value = TextBox1.Value * 0.05
Case Is = "Pantry"
TextBox2.Value = TextBox1.Value * 0.13
End Select
TextBox3.Value = (TextBox2.Value * 0.145) + TextBox2.Value
If TextBox1.Value <= 250 Then
TextBox4.Value = TextBox3.Value + 0
ElseIf TextBox1.Value <= 500 Then
TextBox4.Value = TextBox3.Value + 5.725
ElseIf TextBox1.Value > 500 Then
TextBox4.Value = TextBox3.Value + 11.45
End If
TextBox6.Value = TextBox1.Value - TextBox4.Value
End Sub
---------------------------------------------------------------
When the macro is run by an user the calculator window opens and the user has to select the drop down menu and also enter the price and then click on Calculate that is the commandbutton1.
When a user has selected all the drop down list and entered the value in the textbox1 to compute the data, The output is correct and there is no error message.
But when a user just randomly clicks on the commandbutton1 unknowingly or purposefully then there is a message box I have given stating "Please enter the values in the above box"
and when the user clicks on "Ok" button in the message box the error message window pops up stating "Run time error 13 type mismatch" and the whole sheet and the calculator closes.
Please help me in overcoming this error as I am sharing this sheet to users who do no have knowledge about macro or any error codes that pops up and they will not be able to understand the errors or debug it.
And also I do not want the sheet to close or the calculator to close when the error message breaks the calculation.
Below is the Code I have written for Combobox1 and Combobox2 drop down selection:
Private Sub ComboBox1_Change()
Me.ComboBox2 = ""
Select Case Me.ComboBox1
Case "Media"
Me.ComboBox2.RowSource = "Media"
Case "Consumables"
Me.ComboBox2.RowSource = "Consumables"
Case "Softline"
Me.ComboBox2.RowSource = "Softline"
Case "CE_PC"
Me.ComboBox2.RowSource = "CE_PC"
Case "Other Hardlines"
Me.ComboBox2.RowSource = "Other_Hardlines"
End Select
End Sub
-----------------------------------------------------------------------
I am not sure how to attach an image of the userform or the screen shot of it so I have made an effort for you guys to understand how the userform or the calculator I have made looks like.
Price Calculator: (Using UserForms)
------------------------------------------------------------------
Category(Label) [Drop down list(Combobox1)]v
Sub-Category(Label) [Drop down list(Combobox2)]v
Price(Label) [Textbox] /*User enters the price of the product in this textbox*/
Calculate(Label) [CommandButton] /*Here the code for computing is written to calculate the required data*/
Referral Fees(Label) [Textbox] /*Output is given as per the code is commandbutton*/
14.5% Tax(Label) [Textbox] /*Output is given as per the code is commandbutton*/
Closing Balance+
14.5% Tax (Label) [Textbox] /*Output is given as per the code is commandbutton*/
Amount Paid To
Seller(Label) [Textbox] /*Output is given as per the code is commandbutton*/
---------------------------------------------------------------
Kindly help me out in getting this calculator successfully running without any errors. And please guide me the steps I need to change detailed as I am still learning the vba macro.
Thank You all In Advance.
Have a Great Day All.
