New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Result Change Dependant on Checkbox selection

Macros, VBA, Excel Automation, etc.

Result Change Dependant on Checkbox selection

Postby PurpleDaisy » Thu Feb 16, 2017 9:37 am

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
  • 0

You do not have the required permissions to view the files attached to this post.
PurpleDaisy
Regular
 
Posts: 50
Joined: Jun 11, 2013
Reputation: 3
Excel Version: 2007

Re: Result Change Dependant on Checkbox selection

Postby Sisyphus » Sat Feb 18, 2017 3:18 pm

Hello PurpleDaisy,
I look in about once a year, and fancy finding you at the top of the list. :D
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.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Result Change Dependant on Checkbox selection

Postby PurpleDaisy » Mon Feb 20, 2017 11:34 am

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!!
  • 0

PurpleDaisy
Regular
 
Posts: 50
Joined: Jun 11, 2013
Reputation: 3
Excel Version: 2007


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 71 guests