Trouble with AlertUser Macro

[seenfresh]

I am having trouble with my Macro:


Sub AlertUser(Value As Long)
For Each c In Worksheets("ExpenseForm").Range("C20:J20")
If c.Value > 0 Then
c("A30").AddComment ("Must Fill out Description Meals Section!")
End Sub

I would like to have a macro that remains in the worksheet ("Expense Form") and will alert the user when a value is entered into Cell C20:J20 by either a comment or msgbox alert! I have tried running my attempt above, but nothing happens when I enter a value in C20?

Perhaps someone could point me in the right direction?

Thanks

 

[Norie]

Do you want this to run automatically?

If you do then what you probably want is a change event.

Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count>1 Then Exit Sub
      If Intersect(Target, Range("C20:J20") Is Nothing Then Exit Sub

      If Target.Value>0 Then Msgbox "Must Fill out Description Meals Section!"

End Sub

 

[Smitty]

You can use a change event:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("C20:J20")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target.Value > 0 <SPAN style="color:#00007F">Then</SPAN> MsgBox "Must Fill out Description Meals Section!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The code goes in the Expense Form module.

HTH,

Smitty

 

[seenfresh]

Thanks gentleman it is exactly what I needed!

One more question! If I would like to lock a certain range of cells from data input on my template what would be the best way to do so?

Thanks again,

Cheers!

 

[Smitty]

By their nature, all cells on a sheet are set to Locked, so if you select the cells that you want to allow data entry, then goto Format-->Cells-->Protection-->Uncheck "Locked", then protect the sheet, only those cells can accept entries.

Smitty