simple question

[someguy]

Hi,

This seems like it should be a fairly simple task but i'm new to vba
and can't seem to figure it out. I have 2 cells in excel whose values have
to add up to 1.0. If the user changes the values in any cell and their
total value is not equal to one, a popup window should notify the user.
I've written the fuction in VB but how do i call it when the user changes
the cell values? Many thanks.

TIA

 

[JulieD]

Hi

put the code in as a worksheet_change event against the sheet itself
(in the project explorer window double click on the sheet name and put the
code there rather than in a module)

also you'll need to use something like:-
If Not Intersect(Target, Range("A2")) Is Nothing Or _
Not Intersect(Target, Range("B2")) Is Nothing Then

at the top of your code to limit the code to run only when one or other of
the two nominated cells are changed.

let us know how you go

Cheers
JulieD

 

[William]

Hi

Place the following in the ThisWorkbook module. I'm assuming the 2 cells are
A1 and C1.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim r As Range
With ActiveSheet
Set r = .Range("A1,C1")
If Application.Sum(r) <> 1 Then _
MsgBox "Numbers do not equal 1"
End With
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Hi,
|
| This seems like it should be a fairly simple task but i'm new to vba
| and can't seem to figure it out. I have 2 cells in excel whose values
have
| to add up to 1.0. If the user changes the values in any cell and their
| total value is not equal to one, a popup window should notify the user.
| I've written the fuction in VB but how do i call it when the user changes
| the cell values? Many thanks.
|
| TIA

 

[William]

Correction to my previous post - place the code in the relevant worksheet
module and not the ThisWorkbook module - apologies.

--
XL2002
Regards

William

(e-mail address removed)

| Hi,
|
| This seems like it should be a fairly simple task but i'm new to vba
| and can't seem to figure it out. I have 2 cells in excel whose values
have
| to add up to 1.0. If the user changes the values in any cell and their
| total value is not equal to one, a popup window should notify the user.
| I've written the fuction in VB but how do i call it when the user changes
| the cell values? Many thanks.
|
| TIA

 

[Bob Phillips]

a bit better to qualify this on just the cells in question

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim r As Range
If Target.Address = "$A$1" Or Target.Address = "$C$1" Then
Set r = Me.Range("A1,C1")
If Application.Sum(r) <> 1 Then
MsgBox "Cell " & Target.Address & _
" has been changed," & vbCrLf & _
"the numbers do not now equal 1"
End If
End If
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[William]

Hi Bob

The reason I didn't qualify the range is that the OP's post was slightly
unclear as to whether the two cells contained hard values or formulae. The
comment "If the user changes the values in ANY cell..." could imply that the
range in question contained formulae and other cells were being changed.

Notwithstanding, your point is well taken.

--
XL2002
Regards

William

(e-mail address removed)

| a bit better to qualify this on just the cells in question
|
| Private Sub Worksheet_Change(ByVal Target As Excel.Range)
| Dim r As Range
| If Target.Address = "$A$1" Or Target.Address = "$C$1" Then
| Set r = Me.Range("A1,C1")
| If Application.Sum(r) <> 1 Then
| MsgBox "Cell " & Target.Address & _
| " has been changed," & vbCrLf & _
| "the numbers do not now equal 1"
| End If
| End If
| End Sub
|
|
|
| --
|
| HTH
|
| RP
| (remove nothere from the email address if mailing direct)
|
|
| | > Hi
| >
| > Place the following in the ThisWorkbook module. I'm assuming the 2 cells
| are
| > A1 and C1.
| >
| > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
| > Dim r As Range
| > With ActiveSheet
| > Set r = .Range("A1,C1")
| > If Application.Sum(r) <> 1 Then _
| > MsgBox "Numbers do not equal 1"
| > End With
| > End Sub
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > | > | Hi,
| > |
| > | This seems like it should be a fairly simple task but i'm new to
vba
| > | and can't seem to figure it out. I have 2 cells in excel whose values
| > have
| > | to add up to 1.0. If the user changes the values in any cell and
their
| > | total value is not equal to one, a popup window should notify the
user.
| > | I've written the fuction in VB but how do i call it when the user
| changes
| > | the cell values? Many thanks.
| > |
| > | TIA
| >
| >
|
|

 

[Bob Phillips]

Hi William,

That is a good point. It could be that the two cells are results of other
cells, in which case that would cause a problem with my suggested change.

The OP could also consider conditional formatting I guess.

Regards

Bob

 

[Sharad Naik]

Well he can still make it the cells are the result of a calculation:
If the cells in question are say A3 and B3 then

Private Sub Worksheet_Change(ByVal Target As Range)
Static lastTot As Single
Dim nowTot As Single
nowTot = Me.Range("A3").Value + Me.Range("B3").Value
If lastTot = nowTot Then Exit Sub
lastTot = nowTot
If nowTot > 1 Then
MsgBox "Total of A3 and B3 is higher than 1"
End If
End Sub

 

[Bob Phillips]

I think you have missed the point Sharad.

Of course he can do that, and William's original formula handled that fine.
It was my change that tested for those cells being changed that would
preclude such a situation.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Sharad Naik]

Oh I see, sorry.
With William's code, once the sum is not 1
then it will keep of giving that message, even if the user changes any other
cell in the worksheet, which means the user must correct it first.

So your code verifying the target address wouldn't do it.
I thought this was the issue being discussed.

Sharad

 

[Fredrik Wahlgren]

Hi,

This seems like it should be a fairly simple task but i'm new to vba
and can't seem to figure it out. I have 2 cells in excel whose values have
to add up to 1.0. If the user changes the values in any cell and their
total value is not equal to one, a popup window should notify the user.
I've written the fuction in VB but how do i call it when the user changes
the cell values? Many thanks.

TIA

Are you 100% sure you want to use a popup window? Why not use something like
=IF(1=A1+A2, "OK", "SUM <> 1")
The problem with a popup is that you potentially could get lots of windows
poping up by just changing the value in one cell
/Fredrik

 

[someguy]

Thanks for all your help guys!...it works....and it seems there are many
ways it can me done