Selection Change Question

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
I have the following macro working on a sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("D36:D75")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Call Macro2
Else
Target = vbNullString

End If
End If
End Sub

If I click on a cell in the D column between D36 and D75, a check mark appears (a small caps "a" in Marlett font) in the cell I clicked on.

As soon as the check is created, I wanted another macro to be called that would select the third cell to the left of this one and change the contents to "N/A", and then the second one, and then the first one. This is it below:

Sub Macro2()

ActiveCell.Offset(0, -3).Select
ActiveCell.Value = "N/A"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "N/A"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "N/A"
ActiveCell.Offset(0, 1).Select
End Sub


But as soon as Macro2 selects another cell on the same page, the first macro above is reactivated, interrupting the new macro. The first macro steps through to the point that it discovers that the cell selected is not one it is supposed ot change, and the it goes to End If and stops, not completing the newer macro. Is there a way to totally turn off this macro until I select another cell in the D column?

Thanks,
Bill Biggs
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
No need to do all that selecting. Ranges can be worked directly with VBA.
Those Offsets don't match your description however. Also why do you need a second macro that can be accomplished in one line, if you really want the three cells to the left to say N/A.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("D36:D75")) Is Nothing Then Exit Sub
Target.Font.Name = "Marlett"
If Target = vbNullString Then
    Target = "a"
    Target.Offset(, -3).Resize(, 3) = "N/A"
Else
    Target = vbNullString
End If
End Sub
 

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
I thank you very much. I started down the path of trying to get away from the first macro and I guess I thought activating a second macro would do the trick. I am real tired today. But I am also still learning VBA. Sometimes I have flashes of brilliance, and then there are days like today. Again. I have already plugged it in and it works just fine. Thank you.
 

Forum statistics

Threads
1,181,733
Messages
5,931,726
Members
436,800
Latest member
abowalid98

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