I am putting together a spreadsheet that allows a client to enter
ineterview criteria that is weighted etc. Part of this is allowing them
to change the weights (pervcentages). I would like for a popup message
to appear if the percentage goes over 100.

I have roughly put this together

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
If Sum(Target.Column) Maximum Then
Beep
MsgBox "Caution, 100% has been exceeded!"
End If
End Function

In the cell that shows total percentage I have =ValueCheck(B7:B28,100)

I would like to try make it pretty generic so I can check against other
values later.

As a 2nd, how can I out a variable in the msgBox message ie: instead of
100% just put Maximum.

TIA

W

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
If Sum(Target.Column) Maximum Then
Beep
MsgBox "Caution, " & CStr(Maximum) & "% has been exceeded!"
End If
End Function


----- William Hamilton wrote: -----

I am putting together a spreadsheet that allows a client to enter
ineterview criteria that is weighted etc. Part of this is allowing them
to change the weights (pervcentages). I would like for a popup message
to appear if the percentage goes over 100.

I have roughly put this together

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
If Sum(Target.Column) Maximum Then
Beep
MsgBox "Caution, 100% has been exceeded!"
End If
End Function

In the cell that shows total percentage I have =ValueCheck(B7:B28,100)

I would like to try make it pretty generic so I can check against other
values later.

As a 2nd, how can I out a variable in the msgBox message ie: instead of
100% just put Maximum.

TIA

W

William,

You have a couple of problems with that function. First you cannot use SUM
directly in VBA. and if you use Target.Column, you will the column number of
the first cell summed,. not the values

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
Dim nSum As Double

nSum = WorksheetFunction.Sum(Target)
If nSum Maximum Then
Beep
MsgBox "Caution, your value of " & Format(nSum, "#,##0.0") & vbCrLf &
_
"exceeds the maximum value of " & Format(Maximum, "#,##0.0") &
"%"
End If
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"William Hamilton" wrote in
message ...
I am putting together a spreadsheet that allows a client to enter
ineterview criteria that is weighted etc. Part of this is allowing them
to change the weights (pervcentages). I would like for a popup message
to appear if the percentage goes over 100.

I have roughly put this together

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
If Sum(Target.Column) Maximum Then
Beep
MsgBox "Caution, 100% has been exceeded!"
End If
End Function

In the cell that shows total percentage I have =ValueCheck(B7:B28,100)

I would like to try make it pretty generic so I can check against other
values later.

As a 2nd, how can I out a variable in the msgBox message ie: instead of
100% just put Maximum.

TIA

W

First, Bob fixed your code, but I'm not sure I'd use a msgbox in a function that
will be called from the worksheet.

Each time it recalculates, I could get a msgbox that I have to dismiss before I
finish making corrections.

And if you use it lots of places, you could get really irritated.

Stealing Bob's code:

Option Explicit

Function ValueCheck(ByVal Target As Range, Maximum As Long) As String
Dim nSum As Double

ValueCheck = "Ok"
nSum = WorksheetFunction.Sum(Target)
If nSum Maximum Then
Beep
ValueCheck = "Caution, your value of " & Format(nSum, "#,##0.0") _
& " exceeds the maximum value of " & _
Format(Maximum, "#,##0.0") & "%"
End If
End Function

(I'd lose the beep, too!)

But for things like this, I think I'd stick with worksheet formulas.





Bob Phillips wrote:

William,

You have a couple of problems with that function. First you cannot use SUM
directly in VBA. and if you use Target.Column, you will the column number of
the first cell summed,. not the values

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
Dim nSum As Double

nSum = WorksheetFunction.Sum(Target)
If nSum Maximum Then
Beep
MsgBox "Caution, your value of " & Format(nSum, "#,##0.0") & vbCrLf &
_
"exceeds the maximum value of " & Format(Maximum, "#,##0.0") &
"%"
End If
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"William Hamilton" wrote in
message ...
I am putting together a spreadsheet that allows a client to enter
ineterview criteria that is weighted etc. Part of this is allowing them
to change the weights (pervcentages). I would like for a popup message
to appear if the percentage goes over 100.

I have roughly put this together

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
If Sum(Target.Column) Maximum Then
Beep
MsgBox "Caution, 100% has been exceeded!"
End If
End Function

In the cell that shows total percentage I have =ValueCheck(B7:B28,100)

I would like to try make it pretty generic so I can check against other
values later.

As a 2nd, how can I out a variable in the msgBox message ie: instead of
100% just put Maximum.

TIA

W

--

Dave Peterson

Thanks for the help guys, Dave, Bob and Chris. I will show the client
with/without msgbox and see which they prefer, I am with you on the
annoyance factor Dave.


Thanks again.

W


Dave Peterson wrote:
First, Bob fixed your code, but I'm not sure I'd use a msgbox in a function that
will be called from the worksheet.

Each time it recalculates, I could get a msgbox that I have to dismiss before I
finish making corrections.

And if you use it lots of places, you could get really irritated.

Stealing Bob's code:

Option Explicit

Function ValueCheck(ByVal Target As Range, Maximum As Long) As String
Dim nSum As Double

ValueCheck = "Ok"
nSum = WorksheetFunction.Sum(Target)
If nSum Maximum Then
Beep
ValueCheck = "Caution, your value of " & Format(nSum, "#,##0.0") _
& " exceeds the maximum value of " & _
Format(Maximum, "#,##0.0") & "%"
End If
End Function

(I'd lose the beep, too!)

But for things like this, I think I'd stick with worksheet formulas.





Bob Phillips wrote:

William,

You have a couple of problems with that function. First you cannot use SUM
directly in VBA. and if you use Target.Column, you will the column number of
the first cell summed,. not the values

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
Dim nSum As Double

nSum = WorksheetFunction.Sum(Target)
If nSum Maximum Then
Beep
MsgBox "Caution, your value of " & Format(nSum, "#,##0.0") & vbCrLf &
_
"exceeds the maximum value of " & Format(Maximum, "#,##0.0") &
"%"
End If
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"William Hamilton" wrote in
message ...

I am putting together a spreadsheet that allows a client to enter
ineterview criteria that is weighted etc. Part of this is allowing them
to change the weights (pervcentages). I would like for a popup message
to appear if the percentage goes over 100.

I have roughly put this together

Function ValueCheck(ByVal Target As Range, Maximum As Integer)
If Sum(Target.Column) Maximum Then
Beep
MsgBox "Caution, 100% has been exceeded!"
End If
End Function

In the cell that shows total percentage I have =ValueCheck(B7:B28,100)

I would like to try make it pretty generic so I can check against other
values later.

As a 2nd, how can I out a variable in the msgBox message ie: instead of
100% just put Maximum.

TIA

W