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

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

"someguy" wrote in message
7.142...
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

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



"someguy" wrote in message
7.142...
| 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

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" wrote in message
...
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



"someguy" wrote in message
7.142...
| 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

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



"Bob Phillips" wrote in message
...
| 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" wrote in message
| ...
| 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
|
|
|
| "someguy" wrote in message
| 7.142...
| | 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
|
|
|
|

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


"William" wrote in message
...
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



"Bob Phillips" wrote in message
...
| 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" wrote in message
| ...
| 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
|
|
|
| "someguy" wrote in message
| 7.142...
| | 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
|
|
|
|

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" wrote in message
...
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


"William" wrote in message
...
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



"Bob Phillips" wrote in message
...
| 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" wrote in message
| ...
| 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
|
|
|
| "someguy" wrote in message
| 7.142...
| | 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" wrote in
:

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

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

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

--
XL2002
Regards

William



"someguy" wrote in message
7.142...
| 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

"someguy" wrote in message
7.142...
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