Format spreadsheet so no input allowed if certain cell is =<0

I want to format a worksheet so that only credit inputs are allowed (amounts
less than 0) in any cell formatted as a number cell, whenever the cell
containing the spreadsheet balance is less than or equal to zero. This is a
spreadsheet where purchases are logged and a balance is maintained. If the
balance falls below zero, users are supposed to contact me for more money,
but they do not, and continue to log entries when the balance is in the red.
I want to prevent them from making any entries that will cause the balance to
fall below zero. I do want to allow them to make entries that will increase a
balance that is at or below zero. Can anyone help?

--

HTH

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


"Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
> I want to format a worksheet so that only credit inputs are allowed
(amounts
> less than 0) in any cell formatted as a number cell, whenever the cell
> containing the spreadsheet balance is less than or equal to zero. This is
a
> spreadsheet where purchases are logged and a balance is maintained. If the
> balance falls below zero, users are supposed to contact me for more money,
> but they do not, and continue to log entries when the balance is in the
red.
> I want to prevent them from making any entries that will cause the balance
to
> fall below zero. I do want to allow them to make entries that will
increase a
> balance that is at or below zero. Can anyone help?

Jan,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.NumberFormat = "$#,##0.00" Then
If Me.Range("H10").Value <= 0 Then
Target.Value = ""
MsgBox "Contact Jan for more money"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


Change the balance cell (H10 in my example) to suit.

--

HTH

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


"Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
> I want to format a worksheet so that only credit inputs are allowed
(amounts
> less than 0) in any cell formatted as a number cell, whenever the cell
> containing the spreadsheet balance is less than or equal to zero. This is
a
> spreadsheet where purchases are logged and a balance is maintained. If the
> balance falls below zero, users are supposed to contact me for more money,
> but they do not, and continue to log entries when the balance is in the
red.
> I want to prevent them from making any entries that will cause the balance
to
> fall below zero. I do want to allow them to make entries that will
increase a
> balance that is at or below zero. Can anyone help?

Bob,

Thank you for your response. I'm not very adept at using Macros yet. I
pasted the module as you directed, but don't see any effect on my worksheet.
I'm sure I've not "connected all the dots". How do I make the code effective?
Sorry to be such a pest.

Jan


"Bob Phillips" wrote:

> Jan,
>
> Try this
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Target.NumberFormat = "$#,##0.00" Then
> If Me.Range("H10").Value <= 0 Then
> Target.Value = ""
> MsgBox "Contact Jan for more money"
> End If
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> Change the balance cell (H10 in my example) to suit.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
> news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
> > I want to format a worksheet so that only credit inputs are allowed
> (amounts
> > less than 0) in any cell formatted as a number cell, whenever the cell
> > containing the spreadsheet balance is less than or equal to zero. This is
> a
> > spreadsheet where purchases are logged and a balance is maintained. If the
> > balance falls below zero, users are supposed to contact me for more money,
> > but they do not, and continue to log entries when the balance is in the
> red.
> > I want to prevent them from making any entries that will cause the balance
> to
> > fall below zero. I do want to allow them to make entries that will
> increase a
> > balance that is at or below zero. Can anyone help?
>
>
>

Jan

When you and Bob do get this working properly, please email me a copy.

I like the part where you send me money<g>


Gord

On Tue, 25 Jan 2005 09:49:03 -0800, "Jan Buckley"
<JanBuckley@discussions.microsoft.com> wrote:

>Bob,
>
>Thank you for your response. I'm not very adept at using Macros yet. I
>pasted the module as you directed, but don't see any effect on my worksheet.
>I'm sure I've not "connected all the dots". How do I make the code effective?
>Sorry to be such a pest.
>
>Jan
>
>
>"Bob Phillips" wrote:
>
>> Jan,
>>
>> Try this
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>
>> On Error GoTo ws_exit:
>> Application.EnableEvents = False
>> If Target.NumberFormat = "$#,##0.00" Then
>> If Me.Range("H10").Value <= 0 Then
>> Target.Value = ""
>> MsgBox "Contact Jan for more money"
>> End If
>> End If
>>
>> ws_exit:
>> Application.EnableEvents = True
>> End Sub
>>
>> 'This is worksheet event code, which means that it needs to be
>> 'placed in the appropriate worksheet code module, not a standard
>> 'code module. To do this, right-click on the sheet tab, select
>> 'the View Code option from the menu, and paste the code in.
>>
>>
>> Change the balance cell (H10 in my example) to suit.
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
>> news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
>> > I want to format a worksheet so that only credit inputs are allowed
>> (amounts
>> > less than 0) in any cell formatted as a number cell, whenever the cell
>> > containing the spreadsheet balance is less than or equal to zero. This is
>> a
>> > spreadsheet where purchases are logged and a balance is maintained. If the
>> > balance falls below zero, users are supposed to contact me for more money,
>> > but they do not, and continue to log entries when the balance is in the
>> red.
>> > I want to prevent them from making any entries that will cause the balance
>> to
>> > fall below zero. I do want to allow them to make entries that will
>> increase a
>> > balance that is at or below zero. Can anyone help?
>>
>>
>>

Some people can always sniff out a good deal :-)

Bob

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:k28dv0l8le162t0b9d19ib3jkp5rqgsfab@4ax.com...
> Jan
>
> When you and Bob do get this working properly, please email me a copy.
>
> I like the part where you send me money<g>
>
>
> Gord
>
> On Tue, 25 Jan 2005 09:49:03 -0800, "Jan Buckley"
> <JanBuckley@discussions.microsoft.com> wrote:
>
> >Bob,
> >
> >Thank you for your response. I'm not very adept at using Macros yet. I
> >pasted the module as you directed, but don't see any effect on my
worksheet.
> >I'm sure I've not "connected all the dots". How do I make the code
effective?
> >Sorry to be such a pest.
> >
> >Jan
> >
> >
> >"Bob Phillips" wrote:
> >
> >> Jan,
> >>
> >> Try this
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >>
> >> On Error GoTo ws_exit:
> >> Application.EnableEvents = False
> >> If Target.NumberFormat = "$#,##0.00" Then
> >> If Me.Range("H10").Value <= 0 Then
> >> Target.Value = ""
> >> MsgBox "Contact Jan for more money"
> >> End If
> >> End If
> >>
> >> ws_exit:
> >> Application.EnableEvents = True
> >> End Sub
> >>
> >> 'This is worksheet event code, which means that it needs to be
> >> 'placed in the appropriate worksheet code module, not a standard
> >> 'code module. To do this, right-click on the sheet tab, select
> >> 'the View Code option from the menu, and paste the code in.
> >>
> >>
> >> Change the balance cell (H10 in my example) to suit.
> >>
> >> --
> >>
> >> HTH
> >>
> >> RP
> >> (remove nothere from the email address if mailing direct)
> >>
> >>
> >> "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
> >> news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
> >> > I want to format a worksheet so that only credit inputs are allowed
> >> (amounts
> >> > less than 0) in any cell formatted as a number cell, whenever the
cell
> >> > containing the spreadsheet balance is less than or equal to zero.
This is
> >> a
> >> > spreadsheet where purchases are logged and a balance is maintained.
If the
> >> > balance falls below zero, users are supposed to contact me for more
money,
> >> > but they do not, and continue to log entries when the balance is in
the
> >> red.
> >> > I want to prevent them from making any entries that will cause the
balance
> >> to
> >> > fall below zero. I do want to allow them to make entries that will
> >> increase a
> >> > balance that is at or below zero. Can anyone help?
> >>
> >>
> >>
>

Jan,

This is worksheet event code, which means it gets fired when a certain event
happens within a worksheet.

I gave instructions at the bottom on how to load it, did you understand and
follow these? If so, change one of the currency formatted cells, and if H10
is greater than 0, all is ok, otherwise it sends Gord more money :-)

--

HTH

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


"Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
news:74F01893-62A0-47F8-A353-EC381F68BC67@microsoft.com...
> Bob,
>
> Thank you for your response. I'm not very adept at using Macros yet. I
> pasted the module as you directed, but don't see any effect on my
worksheet.
> I'm sure I've not "connected all the dots". How do I make the code
effective?
> Sorry to be such a pest.
>
> Jan
>
>
> "Bob Phillips" wrote:
>
> > Jan,
> >
> > Try this
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Target.NumberFormat = "$#,##0.00" Then
> > If Me.Range("H10").Value <= 0 Then
> > Target.Value = ""
> > MsgBox "Contact Jan for more money"
> > End If
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> > Change the balance cell (H10 in my example) to suit.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
> > news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
> > > I want to format a worksheet so that only credit inputs are allowed
> > (amounts
> > > less than 0) in any cell formatted as a number cell, whenever the cell
> > > containing the spreadsheet balance is less than or equal to zero. This
is
> > a
> > > spreadsheet where purchases are logged and a balance is maintained. If
the
> > > balance falls below zero, users are supposed to contact me for more
money,
> > > but they do not, and continue to log entries when the balance is in
the
> > red.
> > > I want to prevent them from making any entries that will cause the
balance
> > to
> > > fall below zero. I do want to allow them to make entries that will
> > increase a
> > > balance that is at or below zero. Can anyone help?
> >
> >
> >

Bob,

I understood, and followed your directions. I copied the code and changed it
from your cell H10 to my cell E2 (cell containing the balance). My current
balance is 0. If I increase any of the amounts in the currency formatted
cells, the balance still goes in the red and I don't see any error message -
so no bucks for Gordo. . . Do I need to indicate a range of cells anywhere or
is the entire worksheet included by default? Pardon my ignorance, but this is
the first time I've worked with a 'worksheet code event'. Is the event always
"on"?

"Bob Phillips" wrote:

> Jan,
>
> This is worksheet event code, which means it gets fired when a certain event
> happens within a worksheet.
>
> I gave instructions at the bottom on how to load it, did you understand and
> follow these? If so, change one of the currency formatted cells, and if H10
> is greater than 0, all is ok, otherwise it sends Gord more money :-)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
> news:74F01893-62A0-47F8-A353-EC381F68BC67@microsoft.com...
> > Bob,
> >
> > Thank you for your response. I'm not very adept at using Macros yet. I
> > pasted the module as you directed, but don't see any effect on my
> worksheet.
> > I'm sure I've not "connected all the dots". How do I make the code
> effective?
> > Sorry to be such a pest.
> >
> > Jan
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Jan,
> > >
> > > Try this
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > > On Error GoTo ws_exit:
> > > Application.EnableEvents = False
> > > If Target.NumberFormat = "$#,##0.00" Then
> > > If Me.Range("H10").Value <= 0 Then
> > > Target.Value = ""
> > > MsgBox "Contact Jan for more money"
> > > End If
> > > End If
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > 'This is worksheet event code, which means that it needs to be
> > > 'placed in the appropriate worksheet code module, not a standard
> > > 'code module. To do this, right-click on the sheet tab, select
> > > 'the View Code option from the menu, and paste the code in.
> > >
> > >
> > > Change the balance cell (H10 in my example) to suit.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
> > > news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
> > > > I want to format a worksheet so that only credit inputs are allowed
> > > (amounts
> > > > less than 0) in any cell formatted as a number cell, whenever the cell
> > > > containing the spreadsheet balance is less than or equal to zero. This
> is
> > > a
> > > > spreadsheet where purchases are logged and a balance is maintained. If
> the
> > > > balance falls below zero, users are supposed to contact me for more
> money,
> > > > but they do not, and continue to log entries when the balance is in
> the
> > > red.
> > > > I want to prevent them from making any entries that will cause the
> balance
> > > to
> > > > fall below zero. I do want to allow them to make entries that will
> > > increase a
> > > > balance that is at or below zero. Can anyone help?
> > >
> > >
> > >
>
>
>

Jan,

Yes, the event is always on. Do you have the input cells formatted as
currency ("$#,##0.00") because that was one of your criteria. I understand
that all input cells will be formatted, but the format may be different to
that. Select one of those cells, and in the VBE immediate window, type

?Activecell.Numberformat

and change the event code with what you see.

If all else fails, mail it to me, and I will take a look.

--

HTH

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


"Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
news:5A71D855-3250-4724-84C1-C37AF30C137F@microsoft.com...
> Bob,
>
> I understood, and followed your directions. I copied the code and changed
it
> from your cell H10 to my cell E2 (cell containing the balance). My current
> balance is 0. If I increase any of the amounts in the currency formatted
> cells, the balance still goes in the red and I don't see any error
message -
> so no bucks for Gordo. . . Do I need to indicate a range of cells anywhere
or
> is the entire worksheet included by default? Pardon my ignorance, but this
is
> the first time I've worked with a 'worksheet code event'. Is the event
always
> "on"?
>
> "Bob Phillips" wrote:
>
> > Jan,
> >
> > This is worksheet event code, which means it gets fired when a certain
event
> > happens within a worksheet.
> >
> > I gave instructions at the bottom on how to load it, did you understand
and
> > follow these? If so, change one of the currency formatted cells, and if
H10
> > is greater than 0, all is ok, otherwise it sends Gord more money :-)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
> > news:74F01893-62A0-47F8-A353-EC381F68BC67@microsoft.com...
> > > Bob,
> > >
> > > Thank you for your response. I'm not very adept at using Macros yet. I
> > > pasted the module as you directed, but don't see any effect on my
> > worksheet.
> > > I'm sure I've not "connected all the dots". How do I make the code
> > effective?
> > > Sorry to be such a pest.
> > >
> > > Jan
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Jan,
> > > >
> > > > Try this
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > >
> > > > On Error GoTo ws_exit:
> > > > Application.EnableEvents = False
> > > > If Target.NumberFormat = "$#,##0.00" Then
> > > > If Me.Range("H10").Value <= 0 Then
> > > > Target.Value = ""
> > > > MsgBox "Contact Jan for more money"
> > > > End If
> > > > End If
> > > >
> > > > ws_exit:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > 'This is worksheet event code, which means that it needs to be
> > > > 'placed in the appropriate worksheet code module, not a standard
> > > > 'code module. To do this, right-click on the sheet tab, select
> > > > 'the View Code option from the menu, and paste the code in.
> > > >
> > > >
> > > > Change the balance cell (H10 in my example) to suit.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in
message
> > > > news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
> > > > > I want to format a worksheet so that only credit inputs are
allowed
> > > > (amounts
> > > > > less than 0) in any cell formatted as a number cell, whenever the
cell
> > > > > containing the spreadsheet balance is less than or equal to zero.
This
> > is
> > > > a
> > > > > spreadsheet where purchases are logged and a balance is
maintained. If
> > the
> > > > > balance falls below zero, users are supposed to contact me for
more
> > money,
> > > > > but they do not, and continue to log entries when the balance is
in
> > the
> > > > red.
> > > > > I want to prevent them from making any entries that will cause the
> > balance
> > > > to
> > > > > fall below zero. I do want to allow them to make entries that will
> > > > increase a
> > > > > balance that is at or below zero. Can anyone help?
> > > >
> > > >
> > > >
> >
> >
> >

Jan

I'm anxious to get on this gravy train<g>

E2 has the formula =SUM(A1:A10) to produce a balance.

E2 and A1:A10 are formatted as Currency $#,##0.00

As you enter numbers in A1:A10 as long as E2 remains above 0 nothing happens.

When you enter a number that would produce 0 or <0 in E2, you will get the
message and the cell you just entered the number in will go blank.

E2 will remain at its current value.


Gord Dibben Excel MVP

On Tue, 25 Jan 2005 13:49:02 -0800, "Jan Buckley"
<JanBuckley@discussions.microsoft.com> wrote:

>Bob,
>
>I understood, and followed your directions. I copied the code and changed it
>from your cell H10 to my cell E2 (cell containing the balance). My current
>balance is 0. If I increase any of the amounts in the currency formatted
>cells, the balance still goes in the red and I don't see any error message -
>so no bucks for Gordo. . . Do I need to indicate a range of cells anywhere or
>is the entire worksheet included by default? Pardon my ignorance, but this is
>the first time I've worked with a 'worksheet code event'. Is the event always
>"on"?
>
>"Bob Phillips" wrote:
>
>> Jan,
>>
>> This is worksheet event code, which means it gets fired when a certain event
>> happens within a worksheet.
>>
>> I gave instructions at the bottom on how to load it, did you understand and
>> follow these? If so, change one of the currency formatted cells, and if H10
>> is greater than 0, all is ok, otherwise it sends Gord more money :-)
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
>> news:74F01893-62A0-47F8-A353-EC381F68BC67@microsoft.com...
>> > Bob,
>> >
>> > Thank you for your response. I'm not very adept at using Macros yet. I
>> > pasted the module as you directed, but don't see any effect on my
>> worksheet.
>> > I'm sure I've not "connected all the dots". How do I make the code
>> effective?
>> > Sorry to be such a pest.
>> >
>> > Jan
>> >
>> >
>> > "Bob Phillips" wrote:
>> >
>> > > Jan,
>> > >
>> > > Try this
>> > >
>> > > Private Sub Worksheet_Change(ByVal Target As Range)
>> > >
>> > > On Error GoTo ws_exit:
>> > > Application.EnableEvents = False
>> > > If Target.NumberFormat = "$#,##0.00" Then
>> > > If Me.Range("H10").Value <= 0 Then
>> > > Target.Value = ""
>> > > MsgBox "Contact Jan for more money"
>> > > End If
>> > > End If
>> > >
>> > > ws_exit:
>> > > Application.EnableEvents = True
>> > > End Sub
>> > >
>> > > 'This is worksheet event code, which means that it needs to be
>> > > 'placed in the appropriate worksheet code module, not a standard
>> > > 'code module. To do this, right-click on the sheet tab, select
>> > > 'the View Code option from the menu, and paste the code in.
>> > >
>> > >
>> > > Change the balance cell (H10 in my example) to suit.
>> > >
>> > > --
>> > >
>> > > HTH
>> > >
>> > > RP
>> > > (remove nothere from the email address if mailing direct)
>> > >
>> > >
>> > > "Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
>> > > news:6D43A98A-EB08-4E5A-AD02-413E90A037C3@microsoft.com...
>> > > > I want to format a worksheet so that only credit inputs are allowed
>> > > (amounts
>> > > > less than 0) in any cell formatted as a number cell, whenever the cell
>> > > > containing the spreadsheet balance is less than or equal to zero. This
>> is
>> > > a
>> > > > spreadsheet where purchases are logged and a balance is maintained. If
>> the
>> > > > balance falls below zero, users are supposed to contact me for more
>> money,
>> > > > but they do not, and continue to log entries when the balance is in
>> the
>> > > red.
>> > > > I want to prevent them from making any entries that will cause the
>> balance
>> > > to
>> > > > fall below zero. I do want to allow them to make entries that will
>> > > increase a
>> > > > balance that is at or below zero. Can anyone help?
>> > >
>> > >
>> > >
>>
>>
>>