worksheet change event

[Av8tordude]

I'm using the code below to change the font colors of cells that have constant values (works well). However, I notice the code triggers a worksheet change event, which I'm puzzled as to why this code triggers a worksheet change event?

Cells(ActiveCell.Row, 20).Resize(, 49).SpecialCells(xlConstants).Font.Color = RGB(14, 33, 56)

 

[Av8tordude]

If you step through your code line-by-line, using the F8 key, you can see exactly when it jumps to your Worksheet_SelectionChange event procedure.
Make note of the last line of code run before it goes to the Worksheet_SelectionChange event procedure.
That is the line of code that is triggering it.

I did that already before I posted it and that when I discovered what was triggering the event. Yes...this line triggers the worksheet selection event....

Cells(ActiveCell.Row, 20).Resize(, 49).SpecialCells(xlConstants).Font.Color = RGB(255, 255, 255)

So I'm trying to understand why if nothing is selected?

 

[Joe4]

OK, if you do not want that line of code to trigger the Worksheet_SelectionChange event procedure, you can temporarily turn off events while that line runs, then turn it back on afterwards, i.e.

Application.EnableEvents = False
Cells(ActiveCell.Row, 20).Resize(, 49).SpecialCells(xlConstants).Font.Color = RGB(255, 255, 255) 
Application.EnableEvents = True

 

[Fluff]

That line in itself, cannot trigger a selection change event.
Are you changing the active cell in that code?

 

[Av8tordude]

That line in itself, cannot trigger a selection change event.
Are you changing the active cell in that code?

Believe me...Its not changing anything. That's why I'm befuddled by why its doing it? I know I can turn it off, which I have done, but two reasons...

1. It shouldn't do it since nothing is being selected.
2. its a band-aid to an annoyance, but would definitely like to find out why?

 

[Joe4]

If you can upload the file to a file sharing site (like DropBox or OneDrive), perhaps someone here can download and analyze it.
If you want to do that, just be sure to remove any sensitive data first, and then provide a link here to the file.

 

[Av8tordude]

If you can upload the file to a file sharing site (like DropBox or OneDrive), perhaps someone here can download and analyze it.
If you want to do that, just be sure to remove any sensitive data first, and then provide a link here to the file.

I would like to, but the project is proprietary and I'm trying to fix some minor and annoying bugs....like this one.
From what I gather from your expertise, this should not trigger a worksheet selection event and the best solution, at the moment, is turn it off with the enable events. I think I will sleep on this one tonight, maybe it will switch on a light bulb later.

Thanks guys for you help.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

I would like to, but the project is proprietary and I'm trying to fix some minor and annoying bugs....like this one.
From what I gather from your expertise, this should not trigger a worksheet selection event and the best solution, at the moment, is turn it off with the enable events. I think I will sleep on this one tonight, maybe it will switch on a light bulb later.

If you follow my suggestion (temporarily turn off events), and it DOES work, then that seems to suggest that something about that line of code is triggering events.
Do you have any other event procedure code in this workbook?

If you follow my suggestion (temporarily turn off events), and it DOES NOT work, that means the code is being triggered by something else.

Also, if it is possible to "dummy up" the information contained in the file and get rid of any unrelated code (so nothing proprietary remains), you could upload that for us to look it.

 

[Av8tordude]

I have this line of code in two different userforms and they both trigger. I guess I will stick with turning off the events (Grrr)...

 

[Av8tordude]

here's a thought...could this ...."Resize(, 49)"...be the reason?