Excel warning with VBA 1

[kabushnell]

I have a worksheet in Excel that contains an ID # and following each ID there is a dollar amount entered each month. Similar to the following:

ID Total Jan Feb Mar
8603 200 125 50 25

There can be the same ID several times. Basically what I have is that no single ID total can be greater than 700, and no ID total can be over 1400. I have formulas written that total each row and then again for the total ID. What I was wondering is if there is a way to have Excel pop up an error when someone enters an amount in one of the months that brings that particular ID over the 700 or 1400 limit?

I hope that isn't to confusing. Right now we have to enter the amounts each month and then go see if anyone has gone over the limits. Any help is greatly appreciated.

 

[Jooky68]

You could use the..

private Sub Worksheet_Change(ByVal Target As Excel.Range)

event procedure whih can be put in any sheets coding. Target will be whatever cell the user is changing. After the change if its over the max that it can be pop up and error and dont allow the change. Does this help?

 

[kabushnell]

Yeah it does. I haven't used VBA to a great extent. Can you tell me a little bit more about how I would do this? I have an idea but I am not totally sure of the process.

 

[xlbo]

In worksheet change event

mID = cells(target.row,1).value
lRow = cells(65536,1).end(xlup).row
application.enableevents = false
'Check for current row total
if cells(target.row,2).value > 700 then
msgbox "The total value for each row may not exceed 700"
target.value = 0
else
end if

mIDSUM = worksheetfunction.sumif(Range("A2:A" & lRow),mID,Range("B1:B" & lRow))
If mIDSUM > 1400 then
msgbox "The total value for an ID may not exceed 1400"
target.value = 0
else
end if
application.enableevents = true

assumes that your "total" in col B is a formula that totals up all the values on that row

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

You don't necessarily need to write a VBA procedure for everything you do. Excel has a lot of "hidden" (not well known) features that can do wonders without having to write code.

kabushnell,

For this problem you could use the Data Validation feature available in Excel.

Select the cells you want to "Limit" and go to Data->Validation....

There are hundreds (if not thousands) of possibilities to validate data entry into your cells.

You even have the choice of creating an Input message (created when you select the cell) and a Warning Message (created if the value entered is wrong). Kina cool

TIPress F1 and type Validation into the field to find out more on Data Validation or do a Keyword Search in this forum. You will be amazed what you will find.

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Bowers74 - I totally agree that code should be used if worksheet functions can't do the job but I reckon, being as there is a test for the row sum and a test for the ID sum, that you wouldn't be able to do this with data validation...

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[kabushnell]

xlbo, I am having a little trouble putting in the change event. Can you offer a little more help with how I would insert this code.

 

[xlbo]

Ok - just gone and proved myself wrong
This assumes that you have the IDs in column A and a sum for each row in column B
Use insert>Name>Define to create 2 range names:
IDs - this should refer to the data in col A
sumTot - this should refer to the sums in col B

then, select all the individual month cells and go
Data>Validation>Custom
Enter this in the textbox provided:
=IF(OR(SUMIF(IDs,$A1,sumTot)>1400,$B1>700),FALSE,TRUE)

Then put you error message in and you should be well away

Bowers - thanks for the wake up call

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Jooky68]

Good call Bowers74

 

[Bowers74]

xlbo -

I beg to differ, but it does work for SUM() Validation as well.

If you select Custom Validation and enter =$A$5<700 into the formula field, where $A$5 is the SUM() result. As soon as the user enters a value into one of the &quot;Month&quot; cells and the SUM() goes over 700, they receive an error message.

You could also combine a Conditional Formatting for the SUM() cell that makes it turn red if it goes over 700.

I have 15 Workbooks that use this process and it works for me.

Like I said, there are hundreds (if not thousands) of Data Validation possibilities. ;-)

If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Sorry xlbo,

I must have been writing while you were posting.

My Bad

If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

kabushnell,

You should still use the Conditional Formatting I suggested because even after the error message pops-up, the user can still leave the value in the cell by clicking Retry and then not changing the value. :-(

If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

kabushnell has been &quot;two-timing&quot; us in thread68-619049.

If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Bowers - I was more concerned with having to take care of 2 conditions at the same time - that's what made me think it would be a bit tricky.....but with the sums in colB, it's not even that hard to do

BTW - regarding the &quot;retry&quot; option - I don't think that the value can be left there.....if you try pressing enter, you get the error again, if you press ESC, the cell blanks out....
How are you getting round the &quot;retry&quot; message ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[kabushnell]

Bowers74
No &quot;two timing&quot;. I started this post because of a suggestion in the other forum. Anyway, thanks for all of your help.

However, the validation doesn't seem to be working. I used the custom validation as suggested. I have tried both formulas suggested above and when I enter amounts in the months it still allows me to go over the limit without giving me an error.

 

[xlbo]

Did you create the named ranges ???
Do you have SUM formulae in column B??

both of these are essential to get my data validation formula to work...and it does - I tested it for both conditions

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[kabushnell]

I have a sum of all months in Column B. I entered the ranges as you said. I named them the same and selected the cells that apply to each range. Still no luck.

 

[Bowers74]

xlbo - By clicking out of the cell!


kabushnell -

I had the same problem and couldn't figure out why until I changed my &quot;Greater than&quot; to &quot;Less than&quot; (i.e. =$A$5<700 instead of =$A$5>700.

See if that helps.


If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[kabushnell]

I've got it to work. Thanks very much for your help xlbo and Bowers74. Actually I have made both ways work and it is just what I needed. Thank you very much.

 

[Bowers74]

kabushnell

Don't forget the Conditional Formatting suggestion I made.

If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!