worksheet_change not working is ther another way to do it

**ELFM** · 03-06-2013, 06:49 PM

well i have this code

Please Login or Register  to view this content.

i get the root information from a table from sharepoint that is allowed on sheet2 the code i post bfore is on sheet1 and with formulas i bring the data from sheet2 to sheet1 and it does change everytime i open this excell if i do change the value on cell p then the color of the string on cell 1 change as has to if i bring the value from sheet2 and the value change for itself then dont trigger the sub Worksheet_Change so it doesnt work even the value changed in some way is there another way to do it i would apprecciate

**HaHoBe** · 03-07-2013, 05:03 AM

Hi, ELFM,

Worksheet_Change will only be triggered if you enter something manually (or insert by VBA-Code), any action must take place in the cell. If you have formulas you should watch out for the Calculate event to trigger the call (or watch the cells which get updated). Maybe you should combine this code with a query to get the data and run it automatically after the import is finished. Or run it if Sheet1 is activated.

Ciao,
Holger

**ELFM** · 03-08-2013, 02:05 PM

How do i call calculate event trigger?? is something like worbooksheets_oncalculate() or some i would appreciate any help or how do i know that some cell is activated ty

**HaHoBe** · 03-08-2013, 03:28 PM

Hi, ELFM,

not call an event - you get the code trigegred by some action (change the cursor position, enter values into a cell, let a formula calculate).

Go to the sheet in the VBE, from the left drop down in the code-window choose Worksheet. In the right drop down you´ll find all events listed which are available for the worksheet (same goes for ThisWorkbook as well). Please keep in mind that on every calculation that event will get triggered and may lead to your workbook loose a lot of functionality due to permanent calculation and events raised. So maybe narrow down the range on which the action is about to take place.

Ciao,
Holger

**ELFM** · 03-08-2013, 09:40 PM

how can i add the range on worksheet_calculated should i imput on sub call?? or some??? ty for ur help and ur spended time for explain me this mustbe basic i know but i don´t do program in vba or vba.net lol i do on c or php but

programming is programming and again ty for the help

**HaHoBe** · 03-09-2013, 04:08 AM

Hi, ELFM,

to my opinion you keep asking questions that only a person having the workbook in fromt of him may answer properly (not me).

Maybe try the event in ThisWorkbook

Please Login or Register  to view this content.

Please make sure that the range is given with the name of the sheet.

I wonder right now why you would not use Conditional Formatting but would like to use VBA to solve this.

Ciao,
Holger

**ELFM** · 03-09-2013, 03:43 PM

ty for ur help hahobe i have use conditional formatting the problem there is that there is only 4 shapes for a semaphore and there is no one blue i need 5 green yellow red gray and blue that is why i cant use conditional formatting if u tell me the way i can add more than 4 shapes and there must be gray and blue i would use it ty i cant upload that file cause it have some sensitive information ... if u want i can upload a video anyway wont work if i give u cause it just connect to my company sharepoint server

**HaHoBe** · 03-10-2013, 01:49 AM

Hi, ELFM,

in the code you change the font colour, in CF you want to use semaphires? Sorry but I don´t get our logic on that as I meant to apply the same festure via CF to the font which is possible if you use Excel from 2007 on.

BTW: in the code the last line in the Select Case should restore the original font colour or be xlAutomatic?

Ciao,
Holger