Getting an ActiveX command button to do a spreadsheet function
 

I have installed an ActiveX command button on a spreadsheet and I want it to
perfom a formula function.
I want the press of the button to calculate a formula.
The Formula is to be "=A1/A2" with the result being in a 3 decimal point %.
E.g. =A1/A2 where A1=100 and A2=200 then I want my result to be listed in A4
as a 2 decimal % (In this case the answer should be 50%)

Getting an ActiveX command button to do a spreadsheet function
 

Private Sub CommandButton1_Click()
ActiveCell.FormulaR1C1 = "=R[-3]C/R[-2]C"
Range("A4").Select
Selection.NumberFormat = "0.000%"
End Sub

"Givvie" wrote:

I have installed an ActiveX command button on a spreadsheet and I want it to
perfom a formula function.
I want the press of the button to calculate a formula.
The Formula is to be "=A1/A2" with the result being in a 3 decimal point %.
E.g. =A1/A2 where A1=100 and A2=200 then I want my result to be listed in A4
as a 2 decimal % (In this case the answer should be 50%)

Getting an ActiveX command button to do a spreadsheet function
 

Hi Gav123
Firstly thanks for your response. I unforunately havent got a clue what you
just wrote (I fumbled my way through the command buton!)
I tried to copy your formual and paste it into MVB.

But here's what my problem is. (I thought by adding a command button I might
be able to acheive what I want?) Maybe its a long winded way to do it...Is
there a simpler option??

I have values in cells E29 and F29 and I have a formula in G29 that is
"=E29/F29" formatted in a 3 decimal % format.
My problem is;
If I change a figue in anthoer cell elsewhere on the spreadsheet it would
change one of the figures in either E29 or F29. (thats what i want it to do)
However, the resultant calculation in G29 would not change unless I deleted
the formula (as stated above) and re entered it.
1. - Is there another formula I should be using in G29?
2. - Can I also acheive this result using the Command button and have that
result in F26?

"Gav123" wrote:

Private Sub CommandButton1_Click()
ActiveCell.FormulaR1C1 = "=R[-3]C/R[-2]C"
Range("A4").Select
Selection.NumberFormat = "0.000%"
End Sub

"Givvie" wrote:

I have installed an ActiveX command button on a spreadsheet and I want it to
perfom a formula function.
I want the press of the button to calculate a formula.
The Formula is to be "=A1/A2" with the result being in a 3 decimal point %.
E.g. =A1/A2 where A1=100 and A2=200 then I want my result to be listed in A4
as a 2 decimal % (In this case the answer should be 50%)

Getting an ActiveX command button to do a spreadsheet function
 

Ok this sounds like you haven't got your calculations set to automatic..to
check/change this..

Select ToolsOptionsCalculation and click the Automic radio button.

Your formula is sound and hopefully this should work..

Regards,

Gav

"Givvie" wrote:

Hi Gav123
Firstly thanks for your response. I unforunately havent got a clue what you
just wrote (I fumbled my way through the command buton!)
I tried to copy your formual and paste it into MVB.

But here's what my problem is. (I thought by adding a command button I might
be able to acheive what I want?) Maybe its a long winded way to do it...Is
there a simpler option??

I have values in cells E29 and F29 and I have a formula in G29 that is
"=E29/F29" formatted in a 3 decimal % format.
My problem is;
If I change a figue in anthoer cell elsewhere on the spreadsheet it would
change one of the figures in either E29 or F29. (thats what i want it to do)
However, the resultant calculation in G29 would not change unless I deleted
the formula (as stated above) and re entered it.
1. - Is there another formula I should be using in G29?
2. - Can I also acheive this result using the Command button and have that
result in F26?

"Gav123" wrote:

Private Sub CommandButton1_Click()
ActiveCell.FormulaR1C1 = "=R[-3]C/R[-2]C"
Range("A4").Select
Selection.NumberFormat = "0.000%"
End Sub

"Givvie" wrote:

I have installed an ActiveX command button on a spreadsheet and I want it to
perfom a formula function.
I want the press of the button to calculate a formula.
The Formula is to be "=A1/A2" with the result being in a 3 decimal point %.
E.g. =A1/A2 where A1=100 and A2=200 then I want my result to be listed in A4
as a 2 decimal % (In this case the answer should be 50%)

Getting an ActiveX command button to do a spreadsheet function
 

Forgot to say that you won't need a command button to achieve this, if the
Automatic calculation solution works...

Regards,

Gav.

"Givvie" wrote:

Hi Gav123
Firstly thanks for your response. I unforunately havent got a clue what you
just wrote (I fumbled my way through the command buton!)
I tried to copy your formual and paste it into MVB.

But here's what my problem is. (I thought by adding a command button I might
be able to acheive what I want?) Maybe its a long winded way to do it...Is
there a simpler option??

I have values in cells E29 and F29 and I have a formula in G29 that is
"=E29/F29" formatted in a 3 decimal % format.
My problem is;
If I change a figue in anthoer cell elsewhere on the spreadsheet it would
change one of the figures in either E29 or F29. (thats what i want it to do)
However, the resultant calculation in G29 would not change unless I deleted
the formula (as stated above) and re entered it.
1. - Is there another formula I should be using in G29?
2. - Can I also acheive this result using the Command button and have that
result in F26?

"Gav123" wrote:

Private Sub CommandButton1_Click()
ActiveCell.FormulaR1C1 = "=R[-3]C/R[-2]C"
Range("A4").Select
Selection.NumberFormat = "0.000%"
End Sub

"Givvie" wrote:

I have installed an ActiveX command button on a spreadsheet and I want it to
perfom a formula function.
I want the press of the button to calculate a formula.
The Formula is to be "=A1/A2" with the result being in a 3 decimal point %.
E.g. =A1/A2 where A1=100 and A2=200 then I want my result to be listed in A4
as a 2 decimal % (In this case the answer should be 50%)