Multiply selected cell by a input form

[Ben in CA]

Hi,

I need a macro that I can assign to a form button that will multiply the
value in the selected cell by a percentage entered in an input message box.

Basically, I want to be able to select a cell with a number in it, I press
this form button with a macro, and then it gives me an input message box - I
enter 80 for 80% - and it multiplies the current value by 0.8 and replaces
the previous value with that value.

(Because for the end users, most cells will be locked and protected, I need
an easy way to do this with a macro.)

Does anyone know how to make a macro that will do this?

Thanks in advance! I really appreciate your replies!

Ben

 

[OssieMac]

Hi Ben,

Your quote: "assign to a form button ". Forms and buttons is all very
confusing. Do you mean a button on a User Form or a forms type button on the
worksheet?

There are 2 types of buttons. Not sure how much help you need here so I'll
be explicit in case you need the explanation.

In pre xl2007 versions of Excel, Forms button is found on the Forms toolbar
and Command Button is found on the Control Toolbox toolbar and is an ActiveX
control. When you hover the cursor over a forms button on the forms toolbar
it simply says button. On the Control toolbox toolbar it says Command
Button.

In Excel 2007 both are found together using the Developer ribbon under
Controls and Insert button.

If on a User Form then unless you have a very early version of Excel then
all the controls are ActiveX type.

Anyway the code is the same for both. It is just a matter of where it needs
to be installed. I have included the code for both.

Forms type button where code goes in a standard module and you assign a
macro to it. (Alt/F11 to open the VBA editor and then Insert->Module and copy
the code into the VBA editor)

Sub Button1_Click()
Dim MyPercentage As Double

'Test that the active cell is in fact numerical and not 0
If IsNumeric(ActiveCell) And ActiveCell > 0 Then
MyPercentage = Application.InputBox _
(prompt:="Enter the percentage.", Type:=1)

ActiveCell = ActiveCell * MyPercentage / 100

End If
End Sub

Command Button from the Control Toolbox where code is in the worksheet
module. (Right click the worksheet tab name and select View Code and copy it
into the VBA editor)

Private Sub CommandButton1_Click()
Dim MyPercentage As Double

'Test that the active cell is numerical and greater than zero
If IsNumeric(ActiveCell) And ActiveCell > 0 Then
MyPercentage = Application.InputBox _
(prompt:="Enter the percentage.", Type:=1)

ActiveCell = ActiveCell * MyPercentage / 100
Else
MsgBox "Selected cell must be numeric and greater than zero."
End If

End Sub