adjust code for message box

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
343
Office Version
  1. 2019
Platform
  1. Windows
Could anyone change the following code so when the alert message box is activated it would add the value of 100 to column X for row of alert ?
and message would dislay the values for cells H ,I ,J and K for the row of alert

So example z32 = yes , message is displayed for the value of cells h32 i32 j32 k32
and cell x32 would = 100 ?

VBA Code:
        Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
For Each cell In myRange
Evaluate (cell)
If StrComp(cell, "Yes", vbTextCompare) = 0 Then
Msg = MsgBox("" & vbCrLf & " " & Range("h32").Value & vbCrLf & Range("i32").Value & vbCrLf & Range("j32") & Range("k32").Value & vbCrLf & Range("l32"), vbExclamation)

End If
Next


End Sub
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See if this is what you want.

VBA Code:
Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
            If StrComp(cell, "Yes", vbTextCompare) = 0 Then
                Cells(cell.Row, "X") = 100
                Msg = MsgBox("" & vbCrLf & " " & Range("h" & cell.Row).Value & vbCrLf & Range("i" & cell.Row).Value & vbCrLf & Range("j" & cell.Row) & Range("k" & cell.Row).Value, vbExclamation)

            End If
    Next
End Sub
 
Solution

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
343
Office Version
  1. 2019
Platform
  1. Windows
See if this is what you want.

VBA Code:
Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
            If StrComp(cell, "Yes", vbTextCompare) = 0 Then
                Cells(cell.Row, "X") = 100
                Msg = MsgBox("" & vbCrLf & " " & Range("h" & cell.Row).Value & vbCrLf & Range("i" & cell.Row).Value & vbCrLf & Range("j" & cell.Row) & Range("k" & cell.Row).Value, vbExclamation)

            End If
    Next
End Sub
That looks exactly what i want, but i dont get any message box when cell = yes and 100 does not go to column x
 

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
343
Office Version
  1. 2019
Platform
  1. Windows
See if this is what you want.

VBA Code:
Private Sub Worksheet_Alert()
Dim Msg As Long
Dim myRange As Range
Set myRange = Sheet85.Range("z1:z99")
Dim cell As Range
    For Each cell In myRange
        Evaluate (cell)
            If StrComp(cell, "Yes", vbTextCompare) = 0 Then
                Cells(cell.Row, "X") = 100
                Msg = MsgBox("" & vbCrLf & " " & Range("h" & cell.Row).Value & vbCrLf & Range("i" & cell.Row).Value & vbCrLf & Range("j" & cell.Row) & Range("k" & cell.Row).Value, vbExclamation)

            End If
    Next
End Sub
Thanks alot its working now, appreciate it
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Don't know why yours is not working. This is the test set up with values entered in columns H:K
TestBase.xlsm
HIJKLMNOPQRSTUVWXYZAA
1abcd
2abcd100yes
3abcd
4abcd
5abcd100yes
6abcd
7abcd100Yes
8abcd
9abcd
10abcd
11abcd100Yes
12abcd
13abcd
14abcd
15abcd
Sheet1


this is the message box for each row with "yes" in column z.
 

Attachments

  • 1607622127374.png
    1607622127374.png
    3.8 KB · Views: 1

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
343
Office Version
  1. 2019
Platform
  1. Windows
Don't know why yours is not working. This is the test set up with values entered in columns H:K
TestBase.xlsm
HIJKLMNOPQRSTUVWXYZAA
1abcd
2abcd100yes
3abcd
4abcd
5abcd100yes
6abcd
7abcd100Yes
8abcd
9abcd
10abcd
11abcd100Yes
12abcd
13abcd
14abcd
15abcd
Sheet1


this is the message box for each row with "yes" in column z.
Thats strange works for me
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,678
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top