XL VBA - trigger Macro based on Value of cell next to ActiveCell

2Took

Board Regular
Joined
Jun 13, 2022
Messages
203
Office Version
  1. 365
Platform
  1. Windows
In WorksheetSelectionChange I have:

VBA Code:
If ActiveCell.Offset(0, 1).Value = "ABC BC KLM" Then
Call CloseWBbyName
End If

CloseWBbyName Macro is in the same workbook, and it works as expected when tested by itself.

Problem must be with the trigger part: trying to have code run if Cell to the right of ActiveCell contains "ABC BC KLM"
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,494
Office Version
  1. 365
Platform
  1. Windows
Can you post your entire "Worksheet_SelectionChange" procedure for us to see?
And is this code located in the Sheet module that you want to run it against (and not in a General module)?
 

2Took

Board Regular
Joined
Jun 13, 2022
Messages
203
Office Version
  1. 365
Platform
  1. Windows
Can you post your entire "Worksheet_SelectionChange" procedure for us to see?
And is this code located in the Sheet module that you want to run it against (and not in a General module)?
"Worksheet_SelectionChange" procedure is in the Worksheet, not Module, of course.
Tried trigger code at the bottom, as well as very first at the top of "Worksheet_SelectionChange" procedure.
Tried to Call the CloseWBbyName macro from general Module, but also tried the CloseWBbyName code itself instead of calling it.
Tried MsgBox after the trigger, instead of calling CloseWBbyName and box did NOT come up.
Problem is with that trigger line, my guess is.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,494
Office Version
  1. 365
Platform
  1. Windows
If you want our help, please answer the actual questions we asked, and follow the direction we gave you.
So please answer my question and post your entire "Worksheet_SelectionChange" code.
 

2Took

Board Regular
Joined
Jun 13, 2022
Messages
203
Office Version
  1. 365
Platform
  1. Windows
If you want our help, please answer the actual questions we asked, and follow the direction we gave you.
So please answer my question and post your entire "Worksheet_SelectionChange" code.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Row = 8 Then 'if Enter is pressed in row 7, current step place won't be lost
Call TopVisibleRow
End If

  If ActiveCell.Column = 1 Then
    ActiveWindow.ScrollRow = ActiveCell.Row
    ActiveWindow.ScrollColumn = 1
  'MsgBox "Column A"
    Application.CutCopyMode = False 'no marching ants
  End If
 
'  If ActiveWindow.ScrollRow = 1166 Or ActiveWindow.ScrollRow > 1166 Then
'  Range("B8").Value = Range("B1166").Value
'  End If
 
'  Application.CutCopyMode = False 'no marching ants

If Not Application.Intersect(Target, Range("A840")) Is Nothing Then
Range("M840").Copy
End If

If Not Application.Intersect(Target, Range("A980")) Is Nothing Then
Range("F980").Copy
End If

If Not Application.Intersect(Target, Range("A1019")) Is Nothing Then
Range("N1021").Copy
End If

If Not Application.Intersect(Target, Range("A1224")) Is Nothing Then
Range("G1224").Copy
End If
  
Range("L1").Value = ActiveCell.Address
'MsgBox "hello"

If ActiveCell.Offset(0, 1).Value = "Close prior exported" Then
'Application.Run "'PERSONAL.xlsb'!CloseWBbyName"
Call CloseWBbyName
'MsgBox "hello"
End If
 
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,494
Office Version
  1. 365
Platform
  1. Windows
It seems to work just fine for me.

Here are some things to check for (please confirm each one):

1. Does this code reside in the Sheet module of the sheet that you want to apply it to?

2. Is the value in your cell EXACTLY equal to "Close prior exported"?
Something as simple as an extra space in there will cause it not to match.
If you aren't sure, just trying manually running a little macro like this:
VBA Code:
Sub MyTest()
    MsgBox ActiveCell.Offset(0, 1).Value = "Close prior exported"
End Sub
If it returns FALSE, then that cell does NOT exactly equal "Close prior exported".

3. Are you actively selecting the cell to the right of the cell with "Close prior exported" in it?

4. Do you have any merged cells around the cells you are checking?

5. Are you sure events have not been disabled?
You can manually run this little procedure to turn them back on, in case you accidentally shut them off in your code somewhere.
VBA Code:
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
 

2Took

Board Regular
Joined
Jun 13, 2022
Messages
203
Office Version
  1. 365
Platform
  1. Windows
It seems to work just fine for me.

Here are some things to check for (please confirm each one):

1. Does this code reside in the Sheet module of the sheet that you want to apply it to?

2. Is the value in your cell EXACTLY equal to "Close prior exported"?
Something as simple as an extra space in there will cause it not to match.
If you aren't sure, just trying manually running a little macro like this:
VBA Code:
Sub MyTest()
    MsgBox ActiveCell.Offset(0, 1).Value = "Close prior exported"
End Sub
If it returns FALSE, then that cell does NOT exactly equal "Close prior exported".

3. Are you actively selecting the cell to the right of the cell with "Close prior exported" in it?

4. Do you have any merged cells around the cells you are checking?

5. Are you sure events have not been disabled?
You can manually run this little procedure to turn them back on, in case you accidentally shut them off in your code somewhere.
VBA Code:
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
Love it how thorough you are, but I have to get to the fixed part: "," was also part of that cell, I have made the correction and the code worked.

I did try asterisks initially -- "*Close prior exported*" thinking that's the way to indicate not a complete match, but code did not work.
That's why my original question used word "contains", so if my code syntax was not reflecting "contains", but was for exact match, then we could have arrived at the fix sooner.

Maybe this could be a constructive takeaway in this example.

But I do thank you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,494
Office Version
  1. 365
Platform
  1. Windows
I did try asterisks initially -- "*Close prior exported*" thinking that's the way to indicate not a complete match, but code did not work.
That's why my original question used word "contains", so if my code syntax was not reflecting "contains", but was for exact match, then we could have arrived at the fix sooner.
I missed that subtle point. That is why it is often helpful to see sample data and the exact code, because then those things become more evident.

For "contains", the InStr function works well, i.e.
VBA Code:
If InStr(ActiveCell.Offset(0, 1).Value, "Close prior exported") > 0 Then

See here for more details on that function: MS Excel: How to use the INSTR Function (VBA)
 

Forum statistics

Threads
1,186,178
Messages
5,956,392
Members
438,249
Latest member
georgebasalic3

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