Use a checkbox to change calculations
 

Hello,
I want to use a checkbox on a worksheet to change the calculation of all the
rows in a column.
Example:
Column A
1
2
3
4
5
If I check the check box I want to mutiply each number by 10. If I uncheck
it I want to devide each number by 10.
I am a novice at programing in Excel so any help will be great.
Thank You

Use a checkbox to change calculations
 

Hi

Insert a checkbox from the Command Toolbox menu, and right click on
the checkbox. Select "View code". Inset the code below into the code
sheet.
Exit design mode. Enter "10" in B1 and test it:


Private Sub CheckBox1_Change()
TargetCol = "A"
Range("B1").Copy

If Me.CheckBox1.Value = True Then
Columns(TargetCol).PasteSpecial Paste:=xlAll,
Operation:=xlMultiply
Else
Columns(TargetCol).PasteSpecial Paste:=xlAll, Operation:=xlDivide
End If
End Sub

Regards,
Per


On 9 Dec., 15:29, Cmenkedi wrote:
Hello,
I want to use a checkbox on a worksheet to change the calculation of all the
rows in a column.
Example:
Column A
1
2
3
4
5
If I check the check box I want to mutiply each number by 10. If I uncheck
it I want to devide each number by 10.
I am a novice at programing in Excel so any help will be great.
Thank You

Use a checkbox to change calculations
 

I'm having problems with the line:
If Me.CheckBox1.Value = True Then
When I compiled it, I received an error that says:
Invalid use of Me keyword.

Also I don't want to enter in the number in the spreadsheet, I want it to be
a constant that cannot be changed.

Thank you.

"Per Jessen" wrote:

Hi

Insert a checkbox from the Command Toolbox menu, and right click on
the checkbox. Select "View code". Inset the code below into the code
sheet.
Exit design mode. Enter "10" in B1 and test it:


Private Sub CheckBox1_Change()
TargetCol = "A"
Range("B1").Copy

If Me.CheckBox1.Value = True Then
Columns(TargetCol).PasteSpecial Paste:=xlAll,
Operation:=xlMultiply
Else
Columns(TargetCol).PasteSpecial Paste:=xlAll, Operation:=xlDivide
End If
End Sub

Regards,
Per


On 9 Dec., 15:29, Cmenkedi wrote:
Hello,
I want to use a checkbox on a worksheet to change the calculation of all the
rows in a column.
Example:
Column A
1
2
3
4
5
If I check the check box I want to mutiply each number by 10. If I uncheck
it I want to devide each number by 10.
I am a novice at programing in Excel so any help will be great.
Thank You

Use a checkbox to change calculations
 

I got it to compile but when I try to run it, it tells me "Object Required".
Thanks


"Cmenkedi" wrote:

I'm having problems with the line:
If Me.CheckBox1.Value = True Then
When I compiled it, I received an error that says:
Invalid use of Me keyword.

Also I don't want to enter in the number in the spreadsheet, I want it to be
a constant that cannot be changed.

Thank you.

"Per Jessen" wrote:

Hi

Insert a checkbox from the Command Toolbox menu, and right click on
the checkbox. Select "View code". Inset the code below into the code
sheet.
Exit design mode. Enter "10" in B1 and test it:


Private Sub CheckBox1_Change()
TargetCol = "A"
Range("B1").Copy

If Me.CheckBox1.Value = True Then
Columns(TargetCol).PasteSpecial Paste:=xlAll,
Operation:=xlMultiply
Else
Columns(TargetCol).PasteSpecial Paste:=xlAll, Operation:=xlDivide
End If
End Sub

Regards,
Per


On 9 Dec., 15:29, Cmenkedi wrote:
Hello,
I want to use a checkbox on a worksheet to change the calculation of all the
rows in a column.
Example:
Column A
1
2
3
4
5
If I check the check box I want to mutiply each number by 10. If I uncheck
it I want to devide each number by 10.
I am a novice at programing in Excel so any help will be great.
Thank You