| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Popup macro
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
You are the best!
Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: You are the best! Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
You are really professional. Thanks allot! You were right again.:)
Thanks! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: You are the best! Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: You are the best! Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: You are the best! Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
It's perfect.
Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: You are the best! Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Popup macro
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").Ran ge("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! "Jacob Skaria" wrote: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: You are the best! Thanks allot! "Jacob Skaria" a scris: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: 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").Ran ge("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 --------------- Jacob Skaria "puiuluipui" wrote: 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: Try the below. Right click Active sheet tabView 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Popup box moving to new tab | New Users to Excel | |||
| Excel POPUP | Excel Worksheet Functions | |||
| Popup MsgBox | Excel Discussion (Misc queries) | |||
| Add a warning popup to a Macro Button | Excel Worksheet Functions | |||
| "Why did we get here????" popup | Excel Discussion (Misc queries) | |||
Linear Mode
