Toggle button to assign / unassign value to a cell

Hello,

I am a novice to excel formulas and VBA coding and have been researching a way to use a toggle button in one cell to assign a value in another cell. I am using the "show code" of the toggle to enter a formula that will assign a price value when "on" and assign a 0 value when "off".

The formula I am using is:

Please Login or Register  to view this content.

When I click the toggle button, it activates a price, but once I click it again it locks the value and does not allow the toggle to operate further. Example, click the toggle button, cell K2 activates a value of $1.00. Click the toggle again, the toggle locks and the value locks to $0.

Am I missing a part of the formula that is causing this error to occur?

Thank you in advance for any ideas or resolution.

The True/False default doesn't fit my application. I need an assigned dollar value down a column of toggles.

Column J is the toggle buttons, Column K is the price = either active or inactive. The end game is to tally the active values to total a package price.

I'll try it again, but I have tried 3 versions of code and each time I get the error where it locks the toggle and the "false" value.

It doesn't seem to be an issue with the formula, perhaps it's an issue with the toggle setting?

If I delete the formula, the toggle works by clicking so it goes on/off and returns the value "True" or "False" in the cell. When I enter the formula, the toggle gets stuck.

Originally Posted by BMF5000

It doesn't seem to be an issue with the formula, perhaps it's an issue with the toggle setting?

If I delete the formula, the toggle works by clicking so it goes on/off and returns the value "True" or "False" in the cell. When I enter the formula, the toggle gets stuck.

From what I understand K2 has a formula in it? If that's the case then that is why, as you're replacing the formula with an integer and erasing the original formula.

J2 cell is the toggle, I am using design mode to add the code to populate cell K2.

Attachment 759180

Oddly, I can use the same code in J2 and tell it to populate K4 and it works. So now I'm really unsure why it's locking the toggle.

I don't have a formula in the K2, only the J2 toggle settings has the code. Sorry for the confusion

Well, I can circumvent the issue by moving the target cell to another column. Not how I want it to look, but at least it works.

Attachment 759181

Attachment 759182

Originally Posted by BMF5000

Well, I can circumvent the issue by moving the target cell to another column. Not how I want it to look, but at least it works.

Attachment 759181

Attachment 759182

Attachments don't work unless you follow the instructions in the yellow banner at the top unfortunately, so I can't see what is attached.

Could you upload a sample of the workbook? To be honest, your question doesn't entirely make sense as you're talking about formulas which aren't really relevant to VBA.

OK Trying to attach the example workbook.

Originally Posted by BMF5000

OK Trying to attach the example workbook.

Now I am even more confused because the attached example works perfectly. Nothing gets locked, everything toggles as it should?

That is where I am at now, I moved the target cell to L instead of K, so it works like I said, it's just not how I wanted to display it.

Now I'm trying to apply that code to each toggle button so that Toggle J2 goes to L2, J3 to L3 and so forth. Having trouble with the code for each toggle.

Please Login or Register  to view this content.

Can I ask, is there a reason you are using Toggle buttons instead of regular buttons? ActiveX controls are yuk, hence why it is a dead format.

Originally Posted by PrizeGotti

Can I ask, is there a reason you are using Toggle buttons instead of regular buttons? ActiveX controls are yuk, hence why it is a dead format.

To be honest it's just what came up through a web search in my attempt to learn how to achieve the desired effect.

It seems you have a much easier way to accomplish this, I'm just not familiar with using "Regular Buttons". I'd like to know how you created that in your sheet 1 example. You have both made a very valid point that I seem to be over-complicating this.

I did solve this issue and thank you both for your help. I did end up just using a simple if statement tied to the toggle button and hid the true/false column.