| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro message box.
Hi, i have this macro:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
are you wanting to find the string "no collaboration in the range and then
return it? this would make no difference to wht you already have! Or are you wanting to return the value in the first or last non blank cell in the range (Worksheets("PLANNING").Range("K4:K10")),? "puiuluipui" wrote: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
It depends on some other cell's condition creates "NO COLABORATION" in column
K. You could do something like this - example assumes column D has some value that causes "N C" to show up in column K: Dim searchRange As Range Dim anySearchCell As Range ....other If tests... Set searchRange = ActiveSheet.Range("D4:D10") For Each anySearchCell In searchRange If anySearchCell = condition to cause "NO COLABORATION" in K on the row MsgBox anySearchCell.Offset(0,8), vbExclamation, "" Range ("C" & anySearchCell.Row).Activate End If .... more code "Atishoo" wrote: are you wanting to find the string "no collaboration in the range and then return it? this would make no difference to wht you already have! Or are you wanting to return the value in the first or last non blank cell in the range (Worksheets("PLANNING").Range("K4:K10")),? "puiuluipui" wrote: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
Hi, i have this range:
B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i will write "John". after i click enter the macro is searching in J4:J10 for "John" and the macro display the message. I need the macro to display the message next to "John" in range K4:K10. J4:J10 and K4:K10 are in "PLANNING" sheet. Ex: In B5 i write John J7=john K7= No colaboration. I need the macro to display a msgbox with message from the cell next to John. John in J7, message is in J7. If the macro find John in another cell, let's say J9, then to display message from K9. In every cell range K4:K10, i will have another message In range J4:J10, there will be no duplicates. I need to extract message from range. Can this be done? Thanks! "puiuluipui" a scris: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
Hi, i have this range: B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i will write "John". after i click enter the macro is searching in J4:J10 for "John" and the macro display the message. I need the macro to display the message next to "John" in range K4:K10. J4:J10 and K4:K10 are in "PLANNING" sheet. Ex: In B5 i write John J7=john K7= No colaboration. I need the macro to display a msgbox with message from the cell next to John. John in J7, message is in J7. If the macro find John in another cell, let's say J9, then to display message from K9. In every cell range K4:K10, i will have another message In range J4:J10, there will be no duplicates. I need to extract message from range. Can this be done? Thanks! "JLatham" a scris: It depends on some other cell's condition creates "NO COLABORATION" in column K. You could do something like this - example assumes column D has some value that causes "N C" to show up in column K: Dim searchRange As Range Dim anySearchCell As Range ...other If tests... Set searchRange = ActiveSheet.Range("D4:D10") For Each anySearchCell In searchRange If anySearchCell = condition to cause "NO COLABORATION" in K on the row MsgBox anySearchCell.Offset(0,8), vbExclamation, "" Range ("C" & anySearchCell.Row).Activate End If ... more code "Atishoo" wrote: are you wanting to find the string "no collaboration in the range and then return it? this would make no difference to wht you already have! Or are you wanting to return the value in the first or last non blank cell in the range (Worksheets("PLANNING").Range("K4:K10")),? "puiuluipui" wrote: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
Hi Don, i will have many names in J4J:10.
Ex J4=ANNE K4=A MESSAGE 1 J5=JOHN K5=A MESSAGE 2 J6=JIM K6=A MESSAGE 3 J7=CRIS K7=A MESSAGE 4 When i write a name in range B5:B120, in any sheet except sheet "PLANNING" and "SEARCH", then the macro to search for that name in sheet "PLANNING" in range J4J:10 and to display the message next to that name. Message is located in next colum, in K4:K10. The macro i have, (in my first post) is very good, except that it display a message for every name, and the message is inside macro. I have a list with names and i need to display a message for every name. And i need to have names and their message in a table so, i can modify them as needs. Can this be done? Thanks! "Don Guillett" wrote: Modify to suit Sub findtext() For i = 1 To Sheets.Count MsgBox Sheets(i).Name Set myfind = Sheets(Sheets(i).Name).Columns("j") _ ..Find(What:="john", LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not myfind Is Nothing Then myfind.Offset(, 1) = "hello" 'MsgBox myfind.Row End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this range: B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i will write "John". after i click enter the macro is searching in J4:J10 for "John" and the macro display the message. I need the macro to display the message next to "John" in range K4:K10. J4:J10 and K4:K10 are in "PLANNING" sheet. Ex: In B5 i write John J7=john K7= No colaboration. I need the macro to display a msgbox with message from the cell next to John. John in J7, message is in J7. If the macro find John in another cell, let's say J9, then to display message from K9. In every cell range K4:K10, i will have another message In range J4:J10, there will be no duplicates. I need to extract message from range. Can this be done? Thanks! "puiuluipui" a scris: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. Will not be able to look until later today -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi Don, i will have many names in J4J:10. Ex J4=ANNE K4=A MESSAGE 1 J5=JOHN K5=A MESSAGE 2 J6=JIM K6=A MESSAGE 3 J7=CRIS K7=A MESSAGE 4 When i write a name in range B5:B120, in any sheet except sheet "PLANNING" and "SEARCH", then the macro to search for that name in sheet "PLANNING" in range J4J:10 and to display the message next to that name. Message is located in next colum, in K4:K10. The macro i have, (in my first post) is very good, except that it display a message for every name, and the message is inside macro. I have a list with names and i need to display a message for every name. And i need to have names and their message in a table so, i can modify them as needs. Can this be done? Thanks! "Don Guillett" wrote: Modify to suit Sub findtext() For i = 1 To Sheets.Count MsgBox Sheets(i).Name Set myfind = Sheets(Sheets(i).Name).Columns("j") _ ..Find(What:="john", LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not myfind Is Nothing Then myfind.Offset(, 1) = "hello" 'MsgBox myfind.Row End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this range: B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i will write "John". after i click enter the macro is searching in J4:J10 for "John" and the macro display the message. I need the macro to display the message next to "John" in range K4:K10. J4:J10 and K4:K10 are in "PLANNING" sheet. Ex: In B5 i write John J7=john K7= No colaboration. I need the macro to display a msgbox with message from the cell next to John. John in J7, message is in J7. If the macro find John in another cell, let's say J9, then to display message from K9. In every cell range K4:K10, i will have another message In range J4:J10, there will be no duplicates. I need to extract message from range. Can this be done? Thanks! "puiuluipui" a scris: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
Don,
I'm assuming he probably did, or will, take you up on your offer. Take a look at my earlier post and his reply to it. I think my code was almost there, although you could use FIND vs the loop, it just needs to NOT exit the loop when it finds the 1st match, and it needs to work through several worksheets ... which I think his original code may already do. Jerry "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. Will not be able to look until later today -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi Don, i will have many names in J4J:10. Ex J4=ANNE K4=A MESSAGE 1 J5=JOHN K5=A MESSAGE 2 J6=JIM K6=A MESSAGE 3 J7=CRIS K7=A MESSAGE 4 When i write a name in range B5:B120, in any sheet except sheet "PLANNING" and "SEARCH", then the macro to search for that name in sheet "PLANNING" in range J4J:10 and to display the message next to that name. Message is located in next colum, in K4:K10. The macro i have, (in my first post) is very good, except that it display a message for every name, and the message is inside macro. I have a list with names and i need to display a message for every name. And i need to have names and their message in a table so, i can modify them as needs. Can this be done? Thanks! "Don Guillett" wrote: Modify to suit Sub findtext() For i = 1 To Sheets.Count MsgBox Sheets(i).Name Set myfind = Sheets(Sheets(i).Name).Columns("j") _ ..Find(What:="john", LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not myfind Is Nothing Then myfind.Offset(, 1) = "hello" 'MsgBox myfind.Row End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this range: B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i will write "John". after i click enter the macro is searching in J4:J10 for "John" and the macro display the message. I need the macro to display the message next to "John" in range K4:K10. J4:J10 and K4:K10 are in "PLANNING" sheet. Ex: In B5 i write John J7=john K7= No colaboration. I need the macro to display a msgbox with message from the cell next to John. John in J7, message is in J7. If the macro find John in another cell, let's say J9, then to display message from K9. In every cell range K4:K10, i will have another message In range J4:J10, there will be no duplicates. I need to extract message from range. Can this be done? Thanks! "puiuluipui" a scris: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
If I understand what you've written correctly, then this should work for you.
These lines of code would replace your If WorksheetFunction.CountIF(... End If block of code: Dim searchRange As Range Dim anySearchCell As Range ....other If tests... Set searchRange = ActiveSheet.Range("J4:J10") For Each anySearchCell In searchRange If anySearchCell.Value = Target.value MsgBox anySearchCell.Value & ": " & _ anySearchCell.Offset(0,1).Value, vbExclamation, "" End If Next Range ("C" & anySearchCell.Row).Activate "puiuluipui" wrote: Hi, i have this range: B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i will write "John". after i click enter the macro is searching in J4:J10 for "John" and the macro display the message. I need the macro to display the message next to "John" in range K4:K10. J4:J10 and K4:K10 are in "PLANNING" sheet. Ex: In B5 i write John J7=john K7= No colaboration. I need the macro to display a msgbox with message from the cell next to John. John in J7, message is in J7. If the macro find John in another cell, let's say J9, then to display message from K9. In every cell range K4:K10, i will have another message In range J4:J10, there will be no duplicates. I need to extract message from range. Can this be done? Thanks! "JLatham" a scris: It depends on some other cell's condition creates "NO COLABORATION" in column K. You could do something like this - example assumes column D has some value that causes "N C" to show up in column K: Dim searchRange As Range Dim anySearchCell As Range ...other If tests... Set searchRange = ActiveSheet.Range("D4:D10") For Each anySearchCell In searchRange If anySearchCell = condition to cause "NO COLABORATION" in K on the row MsgBox anySearchCell.Offset(0,8), vbExclamation, "" Range ("C" & anySearchCell.Row).Activate End If ... more code "Atishoo" wrote: are you wanting to find the string "no collaboration in the range and then return it? this would make no difference to wht you already have! Or are you wanting to return the value in the first or last non blank cell in the range (Worksheets("PLANNING").Range("K4:K10")),? "puiuluipui" wrote: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro message box.
Actually it was much easier than that. Turns out this works. Of course, FIND
& offset could also be used. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Employee info" or Sh.Name = "DB" Then exit sub If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then If Trim(Target.Text) = "" Then exit sub MsgBox Application.VLookup(Target, Sheets("DB").Range("f1:g21"), 2, 0) End If End Sub ===== Using FIND Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Employee info" Or Sh.Name = "DB" Then Exit Sub If Not Application.Intersect(Target, Range("B2:B10")) Is Nothing Then If Trim(Target.Text) = "" Then Exit Sub Set mv = Sheets("DB").Columns("F").Find(Target, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not mv Is Nothing Then MsgBox mv.Offset(, 1) Else MsgBox "No Match" End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "JLatham" wrote in message ... Don, I'm assuming he probably did, or will, take you up on your offer. Take a look at my earlier post and his reply to it. I think my code was almost there, although you could use FIND vs the loop, it just needs to NOT exit the loop when it finds the 1st match, and it needs to work through several worksheets ... which I think his original code may already do. Jerry "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. Will not be able to look until later today -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi Don, i will have many names in J4J:10. Ex J4=ANNE K4=A MESSAGE 1 J5=JOHN K5=A MESSAGE 2 J6=JIM K6=A MESSAGE 3 J7=CRIS K7=A MESSAGE 4 When i write a name in range B5:B120, in any sheet except sheet "PLANNING" and "SEARCH", then the macro to search for that name in sheet "PLANNING" in range J4J:10 and to display the message next to that name. Message is located in next colum, in K4:K10. The macro i have, (in my first post) is very good, except that it display a message for every name, and the message is inside macro. I have a list with names and i need to display a message for every name. And i need to have names and their message in a table so, i can modify them as needs. Can this be done? Thanks! "Don Guillett" wrote: Modify to suit Sub findtext() For i = 1 To Sheets.Count MsgBox Sheets(i).Name Set myfind = Sheets(Sheets(i).Name).Columns("j") _ ..Find(What:="john", LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not myfind Is Nothing Then myfind.Offset(, 1) = "hello" 'MsgBox myfind.Row End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this range: B5:B120-in this range, in every sheet, except "SEARCH" and "PLANNING", i will write "John". after i click enter the macro is searching in J4:J10 for "John" and the macro display the message. I need the macro to display the message next to "John" in range K4:K10. J4:J10 and K4:K10 are in "PLANNING" sheet. Ex: In B5 i write John J7=john K7= No colaboration. I need the macro to display a msgbox with message from the cell next to John. John in J7, message is in J7. If the macro find John in another cell, let's say J9, then to display message from K9. In every cell range K4:K10, i will have another message In range J4:J10, there will be no duplicates. I need to extract message from range. Can this be done? Thanks! "puiuluipui" a scris: Hi, i have this macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name < "SEARCH" And Sh.Name < "PLANNING" Then If Not Application.Intersect(Target, Range("B5:B120")) Is Nothing Then If Trim(Target.Text) < "" Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("PLANNING").R ange("J4:J10"), _ Split(Target.Value)(0)) 0 Then MsgBox "NO COLABORATION", vbExclamation, "" Range("C" & Target.Row).Activate End If Application.EnableEvents = True End If End If End If End Sub I need to extract "NO COLABORATION" from range K4:K10. I don't want to have "NO COLABORATION" in macro. i need to transform this: MsgBox "NO COLABORATION", vbExclamation, "" into something like this: MsgBoxFunction.CountIf (Worksheets("PLANNING").Range("K4:K10")), _ Can this be done? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A macro disabled message comes up but there is no macro. | Excel Discussion (Misc queries) | |||
| Macro message | Charts and Charting in Excel | |||
| Message Box Macro | Excel Discussion (Misc queries) | |||
| Message box macro | Excel Discussion (Misc queries) | |||
| Macro message box | Excel Discussion (Misc queries) | |||
Linear Mode
