Trigger macro when range entry complete

S

Sandy

I have a range of cells; non-contiguous; namely

("D109, C111:K111, C114:K114")

What I would like to achieve is that when this range is complete with data
to have a macro run - lets call it "SetHR".

The range of cells is in a sheet called ("Current Round").

Now my thinking was to trigger the macro in the Worksheet_Change event - but
only trigger it when all cells in the range contain data (which is a mixture
of text and numeric), and the focus has now moved outwith the above range.
Furthermore not to run "SetHR" until any further change occurs to the above
range.

Hope this makes sense.

Any thoughts?

Sandy
 
M

Mike H

Sandy,

Right click your sheet tab, view code and paste this in. When all cells in
your range are populeted it call your macro and won't call it again until a
cell changes.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim FilledCells As Long, TotCells As Long
Set MyRange = Range("D109, C111:K111, C114:K114")
If Intersect(Target, MyRange) Is Nothing Then Exit Sub

FilledCells = WorksheetFunction.CountA(MyRange)
TotCells = MyRange.Cells.Count
If FilledCells = TotCells Then
Call SetHR
End If
End Sub

Mike
 
S

Sandy

Mike
Thank you very much, works perfectly.
Sandy

Mike H said:
Sandy,

Right click your sheet tab, view code and paste this in. When all cells in
your range are populeted it call your macro and won't call it again until
a
cell changes.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim FilledCells As Long, TotCells As Long
Set MyRange = Range("D109, C111:K111, C114:K114")
If Intersect(Target, MyRange) Is Nothing Then Exit Sub

FilledCells = WorksheetFunction.CountA(MyRange)
TotCells = MyRange.Cells.Count
If FilledCells = TotCells Then
Call SetHR
End If
End Sub

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top