Popup macro

[puiuluipui]

Hi, can this be modified......

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$2" And UCase(Target.Value) = "Jim" Then
MsgBox "Free time", vbExclamation, ""
Range("C2").Activate
End If
End Sub

To something like this......?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "sheet1!B2:B10" And UCase(Target.Value) =
"sheet2!F2:F10" Then
MsgBox "Free time", vbExclamation, ""
Range("C2").Activate
End If
End Sub

Thanks!

 

[Jacob Skaria]

Try the below. Right click Active sheet tab>View Code and paste the below code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If Target.Value = Worksheets("Sheet2").Range("F" & Target.Row) Then
MsgBox "Free time", vbExclamation, ""
Range("C" & Target.Row).Activate
End If
End If
End Sub

If this post helps click Yes

 

[puiuluipui]

Hi Jacob, it's working, but if i have Jim in F2, then in sheet 1 the macro
display message only in B2. I need to have Jim in one cell in F column and if
in B2:B10 i have 10 entries with Jim, then the message to popup each time Jim
appear in this range.
In F column i will probably have 2 or 3 names, so i need to write this names
only one time and in B range the message to appear each time a name from F
range is written.

Thanks!

"Jacob Skaria" a scris:

 

[Jacob Skaria]

I would have probably misread your query. Try the below..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("F2:F10"), _
Target.Value) > 0 Then
MsgBox "Free time", vbExclamation, ""
Range("C" & Target.Row).Activate
End If
End If
End Sub

If this post helps click Yes

 

[puiuluipui]

Hi Jacob. It was my fault. i didn't explain better . it's working now. I
need one more change if possible. I need the macro to work with cells that
begin with one word, but may have 2 or more. i need in F to write Jim, and in
B, the macro to popup even in cell is something like Jim Brown. If it begin
with Jim, then the macro to display message.
Can this be done?
Thanks!

"Jacob Skaria" a scris:

 

[Jacob Skaria]

Modified to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("F2:F10"), _
Split(Target.Value)(0)) > 0 Then
MsgBox "Free time", vbExclamation, ""
Range("C" & Target.Row).Activate
End If
End If
End Sub

If this post helps click Yes

 

[puiuluipui]

You are the best!
Thanks allot!

"Jacob Skaria" a scris:

 

[Jacob Skaria]

Thanks for the feedback. While testing I found if you blank a cell blank it
returns an error. So you can replace with the below which will handle this..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If Trim(Target.Text) <> "" Then
Application.EnableEvents = False
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("F2:F10"), _
Split(Target.Value)(0)) > 0 Then
MsgBox "Free time", vbExclamation, ""
Range("C" & Target.Row).Activate
End If
Application.EnableEvents = True
End If
End If

End Sub

If this post helps click Yes

 

[puiuluipui]

You are really professional. Thanks allot! You were right again.
Thanks!

"Jacob Skaria" a scris:

 

[puiuluipui]

Hi, Jacob, can this code be made to work from a module and to work with only
5 sheets from 7? I have another macro in these sheets and it's an conflict
between old macro and your macro.
Thanks!

"Jacob Skaria" a scris:

 

[Jacob Skaria]

Hi "puiuluipui"

You dont need to move that to a module. From workbook press Alt+F11 to
launch VBE (Visual Basic Editor). From the left treeview search for the
workbook name and click on + to expand it. Within that you should see the
following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code
pane. Similar to sheet change events we have Workbook change events here. The
code runs for all sheets except Sheet1 and Sheet2. Adjust to suit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Sheet1" And Sh.Name <> "Sheet2" Then
If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then
If Trim(Target.Text) <> "" Then
Application.EnableEvents = False
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("F2:F10"), _
Split(Target.Value)(0)) > 0 Then
MsgBox "Free time", vbExclamation, ""
Range("C" & Target.Row).Activate
End If
Application.EnableEvents = True
End If
End If
End If
End Sub

If this post helps click Yes

 

[puiuluipui]

It's perfect.
Thanks allot!

"Jacob Skaria" a scris:

 

[puiuluipui]

Hi Jacob, i have a small problem with your code. I need messages to be
extracted from a list(range, G2:G10).

I need to change this line:
MsgBox "Free time", vbExclamation, ""
with something like this:
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("F2:F10"), _

This how i see it. Maybe it's more than change this line, but i tried to
show you what i need.
If in sheet3 in range B2:B10 i write Jim, then the code to search in sheet 2
range F2:F10, and when the code find Jim in a cell, then to display a message
found in next cell to john.
I just need the code to be just like this one, but the message to be the one
next to the name i am searching for, so every name to have his own message.
Can this be done?
Thanks!