V.simple problem with column, msgbox

  • Thread starter Thread starter adamh
  • Start date Start date
A

adamh

I'm sure this is straight forward, but can't seem to find my answer via
numerous searches.

Basically I need to modify my current code (which does work), just to
open it out a bit more. Dates are calculated then added to column I.


Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Date = [I3] Then
MsgBox "The highlighted G.A needs to be sent for approval", vbInformation, "Please Note!"
End If
End Sub

--------------------


But rather than (cell) I3, it needs to be the column I. NB. The
highlighting is done with conditional formatting.

And if any body knows how I can delay the msgbox appearing again after
clicking the 'ok' that would also be very useful. At present, as it
finds today’s date it reappears after ever click on the work sheet!

I've only ever touched on VB before.


Thanks in advance

Adam.
 
Hi Adam,

Try replacing your code with something like:

'=====================================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range

If Not Intersect(Target, Columns("I:I")) Is Nothing Then
For Each rCell In Target
If Not IsError(Application.Match(CLng(Date), rCell, 0)) Then
' If Not IsError(Application.Match(4, Target, 0)) Then
' If Date = [I3] Then
MsgBox "The highlighted G.A " & "(" & rCell.Address(0, 0) _
& ") needs to be sent for approval", _
Buttons:=vbInformation, Title:="Please Note!"
End If
Next
End If
End Sub
'<<=====================================
 
Thanks for the help Norman.

I had a go with the bit of code you provided, but it didn't seem to do
anything.

I'm going to have to keep on searching I think.

Thanks anyway

Adam.
 
Hi Adam,

The suggested code was intended to respond to any entry of the current
days date in column I. If this inention does not accord with your need, then
post back.

If the date entries are the result of formulas, try instead:

'=====================================>>
Private Sub Worksheet_Calculate()

If Not IsError(Application.Match(CLng(Date), Columns("I"), 0)) Then
MsgBox "The highlighted G.A needs to be sent for approval", _
Buttons:=vbInformation, Title:="Please Note!"
End If
End Sub
'<<====================================

If the date value(s) in column I are not the result of formulas, then check
that Events are not disabled - in the intermediate window, run the
instruction:

Application.EnableEvents = True

and try this simplified version of the oridginal code:

'=====================================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rng As Range

Set rng = Intersect(Target, Columns("I:I"))

If Not rng Is Nothing Then
If Not IsError(Application.Match(CLng(Date), rng, 0)) Then
MsgBox "The highlighted G.A needs to be sent for approval", _
Buttons:=vbInformation, Title:="Please Note!"
End If
End If

End Sub
'<<=====================================
 
Thanks for your reply Norman. The first sample of code works great,
exactly what I needed.

Thank you for your time and help.

Regards
Adam.
 
Back
Top