Using pop up boxes to supply a variable within a macro
 

Hi

I have a piece of code as part of a macro which subtracts 1 from a value
in cell D2

ActiveCell.FormulaR1C1 = "=RC[1]-1"

Sometimes , I need to vary this number. Rather than open the macro and
change the code each time , is it possible to program a popup to request
the value of the variable , and once executed proceed with the rest of
the macro?

Any help appreciated.



Best Wishes

Using pop up boxes to supply a variable within a macro
 

On 27 Jan, 05:16, Colin Hayes wrote:
Hi

I have a piece of code as part of a macro which subtracts 1 from a value
in cell D2

ActiveCell.FormulaR1C1 = "=RC[1]-1"

Sometimes , I need to vary this number. Rather than open the macro and
change the code each time , is it possible to program a popup to request
the value of the variable , and once executed proceed with the rest of
the macro?

Any help appreciated.



This might work. Put it at the top of your code:-

Dim intNumber as integer
intNumber = inputbox("Please enter the number you wish to subtract
by","Enter number")

This will then store the number you wish to subtract by into the
variable intNumber. The next bit of your code would then look like
this:-

ActiveCell.FormulaR1C1="=RC[1]-"&intNumber

And that should pretty much do the trick. Hope this helps.

Regards,
Matt Richardson
http://teachr.blogspot.com

Using pop up boxes to supply a variable within a macro
 

On 27 Jan, 11:16, Matt Richardson wrote:
On 27 Jan, 05:16, Colin Hayes wrote:

Hi

I have a piece of code as part of a macro which subtracts 1 from a value
in cell D2

ActiveCell.FormulaR1C1 = "=RC[1]-1"

Sometimes , I need to vary this number. Rather than open the macro and
change the code each time , is it possible to program a popup to request
the value of the variable , and once executed proceed with the rest of
the macro?

Any help appreciated.

This might work. Put it at the top of your code:-

Dim intNumber as integer
intNumber = inputbox("Please enter the number you wish to subtract
by","Enter number")

This will then store the number you wish to subtract by into the
variable intNumber. The next bit of your code would then look like
this:-

ActiveCell.FormulaR1C1="=RC[1]-"&intNumber

And that should pretty much do the trick. Hope this helps.

Regards,
Matt Richardsonhttp://teachr.blogspot.com

You might want to change the 'Dim intnumber...' bit to this actually:-

Dim intNumber as double

The prior version will only work with integers and therefore would
only work with whole numbers. Changing the type of variable means
that you can subtract decimals as well.

Regards,
Matt Richardson
http://teachr.blogspot.com

Using pop up boxes to supply a variable within a macro
 

On Jan 27, 4:16 pm, Colin Hayes wrote:
Hi

I have a piece of code as part of a macro which subtracts 1 from a value
in cell D2

ActiveCell.FormulaR1C1 = "=RC[1]-1"

Sometimes , I need to vary this number. Rather than open the macro and
change the code each time , is it possible to program a popup to request
the value of the variable , and once executed proceed with the rest of
the macro?

Any help appreciated.

Best Wishes

Maybe Application.InputBox. Something like...

Dim vaNumber
vaNumber = Application.InputBox( _
Prompt:="Enter the number to subtract.", _
Title:="Get Number to Subtract.", _
Default:=1, _
Type:=1)
If vaNumber = False Then
Exit Sub
End If
ActiveCell.FormulaR1C1 = "=RC[1]-" & vaNumber

Although you might not want the Exit Sub part.
vaNumber equals False if the user clicks the Cancel Button.

Ken Johnson

Using pop up boxes to supply a variable within a macro
 

HI

OK thanks for your help with that - worked perfectly , first time.

Much appreciated.

I made a small variation , so that positive numbers , as well as
negative , could be entered :

ActiveCell.FormulaR1C1="=RC[1]+"&intNumber


Thanks again.

Best wishes.




In article
,
Matt Richardson writes
On 27 Jan, 11:16, Matt Richardson wrote:
On 27 Jan, 05:16, Colin Hayes wrote:

Hi

I have a piece of code as part of a macro which subtracts 1 from a value
in cell D2

ActiveCell.FormulaR1C1 = "=RC[1]-1"

Sometimes , I need to vary this number. Rather than open the macro and
change the code each time , is it possible to program a popup to request
the value of the variable , and once executed proceed with the rest of
the macro?

Any help appreciated.

This might work. Put it at the top of your code:-

Dim intNumber as integer
intNumber = inputbox("Please enter the number you wish to subtract
by","Enter number")

This will then store the number you wish to subtract by into the
variable intNumber. The next bit of your code would then look like
this:-

ActiveCell.FormulaR1C1="=RC[1]-"&intNumber

And that should pretty much do the trick. Hope this helps.

Regards,
Matt Richardsonhttp://teachr.blogspot.com

You might want to change the 'Dim intnumber...' bit to this actually:-

Dim intNumber as double

The prior version will only work with integers and therefore would
only work with whole numbers. Changing the type of variable means
that you can subtract decimals as well.

Regards,
Matt Richardson
http://teachr.blogspot.com