asked on

Excel - Calculations using VBA and Module rather than formula in cell?

Is it possible to do something in the worksheet change event so that when the user inputs a value it does the math calculations.
Like:

if  range(D,Activecell.row) then
    range(E,Activecell.row) = range(D,Activecell.row) * 200
Endif

Open in new window

I tried something like the above, but got memory errors ...
I'm getting super frustrated with locked/protected sheets, insert not copying formulas etc.. I'd rather just 'control' the happenings of the spreadsheet from one place.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Abbie Offman

ASKER

This is exactly what I wanted. Huzzah. :)

Anthony Berenguel

Offmanax, what are you trying to compare with your if statement?

Abbie Offman

ASKER

Basically if Column = D, which I really did not convay well there I guess :)

Abbie Offman

ASKER

I huzzahed too soon.

While the code works perfect...it ceases to work when the sheet is protected, because Column E is locked.

Back to square 1 I guess...

Norie

Try this, replace 'Password with any password you are using to protect the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
' if number entered in D1:D10, multiply value and put it in adjacent column

    If Not Intersect(Target, Range("D1:D10")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            Me.Unprotect ' Password
            Application.EnableEvents = False
            Target.Offset(, 1).Value = Target.Value * 200
            Application.EnableEvents = True
            Me.Protect ' Password
        End If
   End If

End Sub

Open in new window

Roy Cox

Why would you want to use VBA instead of a formula. An inbuilt formula will be faster than any VBA code and will not require having the user enable macros.

Abbie Offman

ASKER

The user has to be able to insert rows. When you insert a row it doesn't copy the formulas from the row above.

Roy Cox

Either drag the formula down or look at using a Table which have Calculated Fields add formulas automatically.

Overview of Excel Tables

Abbie Offman

ASKER

Trying working with a table. I see that the formulas fill for the entire column. Works great, till you protect the sheet and then try inserting a row.

Roy Cox

That is one downside with Tables

Abbie Offman

ASKER

So then Tables work really work for me then I guess. I can't use the copy formula down options. If I could trust the target audience to be able to do that much, I won't need to have formulas and locked cells in the first place :)