Groveling for a Little VBA

[Fujirich]

Yup – the title says it all. I have almost no skills in creating VBA solutions, so I’m tossing myself on the mercy of the forum and hoping for a little help. Thanks in advance to any who can offer some insight to solve my little issue.

I have a workbook with two identical spreadsheets on two separate pages. Some of the values in the cells on each page vary, but other than that, positions are identical.

On each page, in Q3, I want to perform a simple calculation via VBA macro. The reason I want to do this via VBA is because I want to leave that cell unlocked for the users so that a manual value entry is possible, as well as having a number calculated there via the VBA code I’ll outline below.

In Q3, I want the following formula to be performed each time a new number is entered in O3: K3/(1-O3). That’s it. I’d also want to test for a value in K3, and if none is present, then have a message box pop up saying “Unable to calculate equipment price because equipment cost value is missing”.

The end result would perform this calculation in Q3 any time a value is entered in O3, but also still allow a user to enter a separate value in Q3 if they wanted to. Since this appears on the two pages of this workbook, it has to be page-dependent.

That’s it – as said before: my thanks to any help that can be offered!

 

[erik.van.geit]

Hi,

try this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "O3" Then Exit Sub
If Range("K3") = 0 Then MsgBox "yourmessage", 48, "title"
Range("Q3") = Range("K3") / (1 - Range("O3"))
End Sub

hoping you will learn how it works

kind regards,
Erik

EDIT:
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE
see VoG II's code which has the exit sub as it should!!

 

[VoG]

I got there too late but I thought I'd post anyway:

Private Sub worksheet_change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "O3" Then Exit Sub
If Range("K3").Value = "" Then
    MsgBox "Unable to calculate equipment price because equipment cost value is missing"
    Exit Sub
End If
Range("Q3").Value = Range("K3").Value / (1 - Range("O3").Value)
End Sub

This won't change Q3 if K3 is empty.

 

[erik.van.geit]

VoG II,

it's good you posted this because I forgot the exit sub !

 

[Fujirich]

Guys - this is beautiful. Works great - and so easy. Thank you both so much!!