worksheet change event

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
955
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?

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

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
955
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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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.
Rich (BB code):
Application.EnableEvents = False
Cells(ActiveCell.Row, 20).Resize(, 49).SpecialCells(xlConstants).Font.Color = RGB(255, 255, 255) 
Application.EnableEvents = True
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
That line in itself, cannot trigger a selection change event.
Are you changing the active cell in that code?
 

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
955
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 13, 2007
Messages
955
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 13, 2007
Messages
955
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)... :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,326
Messages
5,624,021
Members
416,006
Latest member
PCaffrey

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