Add a % increase to a cell with a entered value

[jmuench]

Hi,

I am trying to figure out a way to maybe use a check box to initiate a % increase on a cell that would have a typed in value. For instance A1 had a entered value of 300, and then if I was to check a checkbox it would apply a % increase that is specified in N1.

Is there a way to do this? without having any formula in A1 as these values are entered in manually

 

[Chewyhairball]

Hi

Do you want it to revert back if the box is unchecked?

Rory

 

[richh]

Hi jmuench,

Welcome to the forum! Yes this can be done. You'll need to write a pretty simple piece of code. Insert in a checkbox onto your worksheet. Then, while in developer mode, choose to edit the code. Insert the following code:

Private Sub CheckBox1_Click()
    Cells(1, 1) = Cells(1, 1) + Cells(1, 1) * Cells(1, "N")
End Sub

 

[jmuench]

Hi

Do you want it to revert back if the box is unchecked?

Rory

yes ideally

 

[jmuench]

Hi jmuench,

Welcome to the forum! Yes this can be done. You'll need to write a pretty simple piece of code. Insert in a checkbox onto your worksheet. Then, while in developer mode, choose to edit the code. Insert the following code:

Private Sub CheckBox1_Click()
    Cells(1, 1) = Cells(1, 1) + Cells(1, 1) * Cells(1, "N")
End Sub

Thanks richh, is this done by right-clicking the checkbox and then select apply macro? In your code if for instance the entered cell is B4, and the percent I want it to calculate as the increase is in AH4 what is edited in the code you supplied?

 

[richh]

Yeah, there should be an option to see or edit code once you've right clicked the checkbox. If you can't click on it correctly, go to the Developer Ribbon and go into Developer Mode.

The code I provided is not dynamic in any way. You asked for a code that would increase A1's value based on N1's percentage value, so that's what I gave you.

If you've decided to move your data entry points around, you're going to need to figure out how to alter the code a bit. See how it says "Cells.(1,1)" ? the first 1 before the comma is your row number. the 1 after the comma is the column number. if you're moved your expected cell references around, do some kindergarden math and figure out what row number and what column number you wish to alter and what row number and column number you wish to reference.

Here's an updated version of the code to increase/decrease the value based upon checking/unchecking the box:

Private Sub CheckBox1_Click()
    If CheckBox1 = True
        Cells(1, 1) = Cells(1, 1) + Cells(1, 1) * Cells(1, "N")
    Else
        Cells(1, 1) = Cells(1,1) / 1+ Cells(1, "N")
    End if
End Sub