Trouble with AlertUser Macro

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Do you want this to run automatically?

If you do then what you probably want is a change event.
Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,060
Messages
5,545,761
Members
410,704
Latest member
Cobber2008
Top