Message Box should Popup on condition

[K]

A B C…..col
Car** 37 100
Van**
Bike 60
Plan** 3
Train

Hi all, I have above data in Sheet1. In above data in cell "C1" I
have fromula "=SUM(B1:B5)" which actually shows total of figures in
column B. I want some macro in Worksheet module which show a Message
Box only if cell "C1" have total amount 100 and if there is any figure
or amount been put by user in column B cell which is next to the text
of column A got ** in the end. For example looking at above data
there are amounts next to the text "Car**" and "Plan**" and as these
text got "**" in the end and total in cell "C1" is also 100 so message
box should pop up saying

As you have put amount next to the:
Car**
Plan**
Please go to Sheet2

and as soon as user click on "OK" on Message Box macro should select
Sheet2
Please can any friend help me on this

 

[Patrick Molloy]

right-click on the sheet tab and select 'View Code' then paste this:

Option Explicit
Private Sub Worksheet_Calculate()
If Range("C1").Value = 100 Then
checkRangeB1_B5
End If
End Sub
Sub checkRangeB1_B5()
Dim cell As Range
Dim text As String
For Each cell In Range("A1:A5").Cells
If Right(cell.Value, 2) = "**" Then
If Not (IsEmpty(cell.Offset(, 1))) Then
text = text & vbCrLf & cell.Value
End If
End If
Next
If text = "" Then Exit Sub
text = "As you have put amount next to the:" & vbCrLf & Mid(text, 2)
MsgBox text
Worksheets("Sheet2").Activate
End Sub


This uses the cheet's 'Calculate' event. When any cell is calculated on the
sheet, this event will fire. First we check if the value in C1 is equal to
100, and if it is we proceed to check cells B1-B5
If any cells have a value, and the cell in the correspondin row column A
ends with **, then a message is displayed and sheet2 activated as requested.

 

[JP Ronse]

Hi K,

Open VBE <Alt><F11>, double click on sheet1 and paste following code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strMsg As String
strMsg = "As you have amount next to the:" & vbCr

If Target.Column = 2 Then
If Range("C1") = 100 Then
If Range("B1") > 0 Then strMsg = strMsg & "Car**" & vbCr
If Range("B2") > 0 Then strMsg = strMsg & "Van**" & vbCr
If Range("B4") > 0 Then strMsg = strMsg & "Plan**" & vbCr

strMsg = strMsg & "Please go to Sheet2"
MsgBox Prompt:=strMsg, Buttons:=vbOKOnly, Title:="aaa"
Sheets("Sheet2").Activate
End If
End If
End Sub

This code is not checking "**", so you cannot change the order in A1. So you
might need to add additional checks.
(to check on "**" you can use instr ...

if instr(1, range("A1"), "**",vbtextcompare)>0 and range("A1").offset(0,1)>0
then styrmsg =strmsg & range("A1") & vbcr

Wkr,

JP

A B C…..col
Car** 37 100
Van**
Bike 60
Plan** 3
Train

Hi all, I have above data in Sheet1. In above data in cell "C1" I
have fromula "=SUM(B1:B5)" which actually shows total of figures in
column B. I want some macro in Worksheet module which show a Message
Box only if cell "C1" have total amount 100 and if there is any figure
or amount been put by user in column B cell which is next to the text
of column A got ** in the end. For example looking at above data
there are amounts next to the text "Car**" and "Plan**" and as these
text got "**" in the end and total in cell "C1" is also 100 so message
box should pop up saying

As you have put amount next to the:
Car**
Plan**
Please go to Sheet2

and as soon as user click on "OK" on Message Box macro should select
Sheet2
Please can any friend help me on this

 

[K]

thanks guys for your help. its fantastic