Macro for Data Validation

[sucett]

Hi,

Hope you can help me with this. The Sum of the range A1-A8 should equal 100. Cell A9 contains the formula result. If A9 does not equal 100, i would like to populate a messages to the user stating to please review the entries.

I tried using data validation from excel, but since the A9 contains a formula the message wasn't populated.

Thanks,

 

[JoeMo]

This is a macro for the worksheet. Right-click the worksheet tab and choose 'View Code'. Then copy and paste the code below into the white space in the VB editor that opens. Close the editor and save the file.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A8")) Is Nothing Then
    Me.Calculate
    If Range("A9").Value <> 100 Then
        MsgBox "Cell A9 is the sum of cells A1:A8 and must be 100. Please adjust the values in A1:A8 accordingly."
        Application.EnableEvents = False
        Range("A1:A8").Select
        With Target
            .ClearContents
            .Activate
        End With
    End If
End If
Application.EnableEvents = True
End Sub

 

[sucett]

I must be doing something wrong... VB does not recognize the macro

I modified it as follows:

Private Sub Worksheet()
If Range("a22").Value <> 100 Then
MsgBox "Cell A9 is the sum of cells A1:A8 and must be 100. Please adjust the values in A1:A8 accordingly."
Application.EnableEvents = False
Range("a1:a22").Select
With Target
.ClearContents
.Activate
End With
End If
Application.EnableEvents = True
End Sub

 

[JoeMo]

This is a module for the worksheet itself - NOT a standard module. Follow the instructions in my earlier post to install it. Your modification is not going to work.