Macro to request user's input before running

[Amr Tabbarah]

I want from a Macro to ask me for the value of a variable before
running, read my input and then run.

As an example, a Macro could be to multiply the value in the cell to
the left of the current position by a constant. This constant is
different each time I run the Macro, hence the need to input this
variable at the time of running the Macro. Normally, I would include
the value of a constant in the text of the Macro, but when each time
this constant is changing, having this constant defined by the user
would be much easier than editing the Macro.

Can anyone assist by proposing a solution?

 

[Dan E]

Amr,

I would suggest using an InputBox to obtain your constant.

Con_Val = Application.InputBox("Enter constant value")

You can look in the help menu for all the options and methods
of using the inputbox. Also, I would suggest testing to ensure
your user has entered the correct datatype (ie. they don't put
letters in where numbers should be)

Dan E

 

[J.E. McGimpsey]

Just as an addition to Dan's suggestion, I like to use something
like:

Dim result As Variant
Do
result = Application.Inputbox( _
Prompt:="Enter constant value", _
Title:="<macro name>", _
Default:=0, _
Type:=1)
If result = False Then Exit Sub 'User pressed Cancel
Loop Until result >= 0

which exits cleanly if the user hits cancel, provides some error
checking (by using Type:= and by looping until an acceptable value)

Amr,

I would suggest using an InputBox to obtain your constant.

Con_Val = Application.InputBox("Enter constant value")

You can look in the help menu for all the options and methods
of using the inputbox. Also, I would suggest testing to ensure
your user has entered the correct datatype (ie. they don't put
letters in where numbers should be)

Dan E

 

[Amr Tabbarah]

Thanks J.E.,

Could you please advise how use this Macro (I am new to Macros) and
where in a Macro should I insert it. The specific Macro I am using is
as follows:

Sub OddRowHt()
' change height of odd-numbered rows in selection
Con_Val = Application.InputBox("Enter constant value")
For Each rw In Selection.Rows
If rw.Row Mod 2 = 1 Then ' change 1 to 0 for even #'d rows
rw.RowHeight = Con_Val ' change to desired height
End If
Next
End Sub

The third line in the Macro was proposed by Dan and it worked.

Thanks,

Amr

 

[J.E. McGimpsey]

Take a look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm