VBA to convert cell value based on drop down selection (part 2)

[whoosh]

Hi

Managed to get my queries answered and code to work with guru's help here.
But after testing it out, it does not work smoothly according to plan.

Attached is a sample workbook to illustrate.

Column A is for the user to key in a "Value", and Column B is a drop-down list ("sqft", "sqm"), for the user to indicate the unit of the "Value" in column A.
The code works so far in converting the "Value" from "sqft" to "sqm" and back.

The problems

• When user keyed in the "Value" in column A, then select the unit from the drop-down list. What the code does is to multiply/ divide the "Value" with 10.76. Hence, the "Value" will be incorrect.

Example: If i keyed in 5000 into Column A1 followed by selecting "sqft" from the drop-down list. The final value in Column A1 will be 53819.55 which is wrong

• When user select the unit (e.g. sqft) more than once from the drop-down list. The code will again keep multiplying/ dividing the "Value" with 10.76.

Example: If i keyed in 5000 into Column A1 and select sqft from the drop-down list twice. The final value in Column A1 will be 579308.84 which is incorrect. Its should remain as 5000.

• If i were to delete a row (e.g. Row 2), it will give me a Run-time error "13": Type mismatch

Need help from gurus here to rectify these areas.

All help appreciated


Thanks!

[sktneer]

Which version you are using? Excel 2003? If not, please update your profile to reflect the correct excel version you are using.

Replace the existing code on Sheet Module with the following codes and see if your issues get resolved.

Please Login or Register  to view this content.

[whoosh]

Thanks again sktneer!

Yes it works in general except point 1.
I.e. If user wants to input the value - 1000 sqft. User keyed 1000 into Cell A1 then select "sqft" from the srop-down list (which is initially "blank"). the answer will be incorrect. Similarly, if user wants to input 500 sqm, the code takes it and divide by 10.76 when "sqm" is selected from the drop-down list.

When user keyed in the "Value" in column A, then select the unit from the drop-down list. What the code does is to multiply/ divide the "Value" with 10.76. Hence, the "Value" will be incorrect.

Example: If i keyed in 5000 into Column A1 followed by selecting "sqft" from the drop-down list. The final value in Column A1 will be 53819.55 which is wrong

Appreciate your time and help rendered.

[sktneer]

You spoke about the wrong value but didn't speak about the expected correct value.

Anyways, try this and let me know if you are getting the desired output now...

Please Login or Register  to view this content.

[whoosh]

Let me try it out and revert.

Thanks!