VBA to remove entry depending on total of column

[DrH100]

I don't really know if this is possible or where to start (which is probably why I can't search for it very well)

I have a workbook with a number of identical worksheets (one for each month) - each worksheet has data that can be entered into about 30 columns via drop down. Each column then totals up the entries in the column in row 30.

What i would like is if a user tries to enter something in one of the drop downs and that would make the total exceed 20 a message box appears saying something like "this would exceed the maximum allowed - authorization required". I would then like the change the user has just made to be reset.

I won't know which worksheet, column or cell the user would be entering in to. It is possible that additional worksheets will be added at a later time so the code would need to cover these as well.

I will need someway of over riding the change if authorization is given but that will be a problem for a different day

I don't really know where to start writing code for this (or as i said earlier if its even possible) so any advice or starting point will be much appreciated.

 

[Kenneth Hobson]

Change the range and message text to suit.

In ThisWorkbook object paste:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  Dim iR As Range, cR As Range
  Set iR = Sh.Range("A30:AD30")
  For Each cR In iR
    If cR > 20 Then
      MsgBox cR.Address & " total is > 20."
      Application.Undo
      Exit For
    End If
  Next cR
End Sub

 

[DrH100]

That's great thanks very much and much shorter than I imagined.

fantastic