Result Change Dependant on Checkbox selection

[PurpleDaisy]

Good day Excel Kings / Queens & Others

It has been too long!! I hope you are all well and as awesome as always.

So I am obviously here because i need your help and i just hope this request is as simple as i think it should be.

What i have is a spread sheet that i manually input flight and fare details into that i have recieved from my travel agent. These are then allocated a set admin fee depending on the nature of travel (domesitc = 11, international=21, Europe=18).

I would like to use a checkbox (selected from ActiveX controls) in each column allocated to the set fee's so when the administrator selects the checkbox it automatically adds this value to the Fare & Taxes and displays the result in the column labled "Cost of Orignal".

For example - My fare costs 1, my taxes cost 1 and these are domestic fares = 11. In the Cost of Origianl the value = 13.

What is the simplist way to create a condition where by the domesitc = 11, international=21, Europe=18 values are associated with their checkbox's and automatically added to the "Cost of Orignal" total?

It has been that long I don't know where to start. A few pointers would go a long way

Thank you as always!!

Purple Daisy

[Sisyphus]

Hello PurpleDaisy,
I look in about once a year, and fancy finding you at the top of the list.
Congratulations on your new job!

To the best of my knowledge you need code to refer to a value of an ActiveX control. But the control has a Linked Cell property which transfers its value to a designated cell which you can then refer to same as any other cell. In my example, I linked the checkbox to G1. Now I can write in another cell, =IF(G1=True, 11, 0) or =IF(G1, A3, B3)

You might hide the linked cell behind the checkbox itself. You might also recall that you can make the cell's content invisible by formatting it with ",,,". But perhaps you should consider using a drop down instead of a checkbox, meaning data valiation instead of ActiveX. Then the result formula might be something like =IF(G1=""Domestic",11, IF(G1=Europe",18, 21)) There is a writeup i did long ago about changing dropdowns based on drop down selections. Some of that stuff might be useful in this context. I think it's in the Excel tips section of this forum.

The point is that I'm not a great fan of ActveX controls unless I want to control them using VBA. If you want to do everything with worksheet functions, not using them should give you more flexibility for the same effort. But, of course, there is no checkbox other than ActveX. So, all depends upon the circumstances of your worksheet design.

Good luck.

[PurpleDaisy]

Hello Sisyphus,

How nice to have you back on the Forum. I for one MISSED you

I think the idea of using a drop down option with Data validation would make the spreadsheet much more flexible. Especially as it will be used company wide by the sounds of things. I will get cracking on that and of course check out your older post on data validaiton.

I am all for keeping things as simple as possible!

Thanks again for checking in

Purple Daisy!!