Message box

[DDD]

I have an excel system that is used on excel 2007 & 2003. im just saying this
if it is important in some way

In the Q column i have a formula, in short displays 1 or 2 or nothing (As in
"") when certain circumstances are met.

I want to have a message box that displays when a number appears (1 or 2)


If 1 appears, i want the message to display the contents of the cell in the
same row in the A column, & state message
"tttttttttttttttttttttttttttttttttttttttttttt".


If 2 appears i want the message to display the contents of the cell in the
same row in the A column & ask to delete the record with options yes or no to
click on.
If No is clicked, nothing happens. If yes is clicked i wanted the contents
of certain cells to be cleared (Cells on the same row in coLumns C, E, & G )



Sorry if this is not clear, if you have any questions please ask.

 

[Susan]

ok, i'm going to use a small range of q1:q10, for these purposes
only. you will have to change the range to suit what you need. also,
i'm not familiar with 2007; i assume this code will work on 2007 but
don't know that for sure. Compiled but not tested - save a copy of
your workbook before running the code:

Option Explicit

Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("q1:q10")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & " tttttttttttttttttttttttttttttttttt")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(rMsg & " Do you want to" _
& " delete this record?", vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


hope it works!

susan

 

[Bernie Deitrick]

DDD,

Sub DeleteRows()
Dim myR As Long

For myR = Cells(Rows.Count, "Q").End(xlUp).Row To 2 Step -1
If Cells(myR, "Q").Value = 1 Then _
MsgBox Cells(myR, "A").Value & _
" tttttttttttttttttttttttttttttttt"

If Cells(myR, "Q").Value = 2 Then
If MsgBox(Cells(myR, "A").Value & _
" Do you want to delete this row?", vbYesNo) = vbYes Then
Cells(myR, "A").EntireRow.Delete
End If
End If
Next myR
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ooops, I read your question too quickly and came away with deleting the row... sorry - ignore it...

Bernie
MS Excel MVP

 

[DDD]

Thank you it works.

Though, when i tested this, i had to run the macro manually when i made the
value "1" display in the cell (in the q column)

& had to run the macro manually again when when i changed "1" to "2"


is there any way i can run this macro automatically when the excel file is
opened & let it run continually, so that i dont have to repeat running the
macro over & over

 

[Susan]

yes, you could change it to a worksheet_change macro. this would have
to be put in the sheet module for the sheet you're working on:

right click on the sheet tab & choose "view code".

then add this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range


Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("q1:q10")

If Target = Qrange Then
Application.EnableEvents = False

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & " tttttttttttttttttttttttttttttttttt")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(rMsg & " Do you want to" _
& " delete this record?", vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c
End If

Application.EnableEvents = True

End Sub

hope that helps!

susan