PDA

View Full Version : five second interval



lior03
12-25-2007, 01:25 AM
hello
occasionally i color certain cells that meet a certain criteria red.i built the following macro that 5 seconds later will ask me whether i want to restore black color to this cells.i noticed that he repeates asking me for each cell separtely.how can i make it do it once no matter how many cells are involved?

Sub aab()
Application.OnTime Now + TimeValue("00:00:05"), "msgd1"
End Sub
Sub msgd1()
If MsgBox("shall i restore black in red cells ?", vbYesNo + vbInformation, "turn color?") = vbYes Then
selection.Font.ColorIndex = 1
End If
End Sub


thanks

mikerickson
12-25-2007, 06:36 AM
I suspect that your "Make cells red" routine calls aab once for each red cell.
If we could see that code, it would show how to ask for only one msgd1.

lior03
12-25-2007, 06:55 AM
here is my code :loop in all cells and color red all cell with quarter's date as i selected.
Sub deletedatebyquarter4()
On Error Resume Next
Dim cell As Range
Dim i As Integer, qtr As Integer
Dim qr As Integer
If IsEmpty(selection) Then Exit Sub
qtr = InputBox("select quarter number: 1 to 4", "select:")
i = 0
For Each cell In selection
qr = DatePart("q", cell)
If qr = qtr Then
i = i + 1
MsgBox " in this selection,there are - " & i & " - date of - " & qr & " - quarter", vbExclamation, "count date's quarter"
cell.Font.ColorIndex = 3
aaa
End If
Next
End Sub

the aaa procedure in the end enables the user to restore the black color.
thanks

mikerickson
12-25-2007, 09:25 AM
Your loop is generating a message box for each found cell. Putting that outside the loop makes one message. Similarly, that is when aab could be called


For Each cell In selection
qr = DatePart("q", cell)
If qr = qtr Then
i = i + 1
cell.Font.ColorIndex = 3
End If
Next cell
MsgBox " in this selection,there are - " & i & " - date of - " & qr & " - quarter", vbExclamation, "count date's quarter"
Call aab