Worksheet Procedure question

[Noz2k]

So I have a bit of code which currently runs on the worksheet_selectionchange procedure, which updates the values in a form with the values from specified worksheet.

The worksheet to look for is dictated by the value in "E3". However I want this data to be amendable, so I only want it to change when "E3" is changed, not when there is a selection change anywhere in the worksheet.

Where do I put the code in order to make it so that it only runs when "E3" is changed.

 

[Noz2k]

Ok I've just found that it does kind of work. At the moment it doesn't work when I change "E5" but if I change it, select another cell, and then select "E5" again it runs the code for the new value.

It still doesn't allow me to amend anything though

 

[Noz2k]

If I get rid of the Not from the line

If Not Intersect(Target, Range("E5")) Is Nothing Then

Then I don't have to reselect the cell for it to make the changes. However I still can't amend the data, it still runs the code no matter what cell I change and not just E5

 

[Noz2k]

anyone?

 

[Peter_SSs]

You mention that your workbook has a number of sheets and that you want the code to fire when E5 is changed. E5 on which sheet? Or do you want it to fire when E5 is changed on ANY sheet?

If a specific sheet, then have you put your Worksheet_Change code in that sheet's module?

Again assuming you are referring to a specific worksheet, right click that sheet's name tab and choose 'View Code'. That is where you should see your Worksheet_Change code. If it is there, I suggest that you comment it out and try this code to see if we can get something working.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("E5")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Change included E5"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "Change did not include E5"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Now try changing single or multiple cells that do or do not include E5 and see if you get the appropriate message.

 

[Noz2k]

It is only refering to the main sheet that calls back the data from the week sheet specified.

I've added that code and I get the message 'Change did not include E5' whenever I make a change (no matter what cell).

The problem is the code is running whatever cell I change, which means I can't then amend the data that gets called back from the week sheets, and use my update button to update it, because I can't change it without it rerunning the code.

 

[Peter_SSs]

I've added that code and I get the message 'Change did not include E5' whenever I make a change (no matter what cell).

1. Even if you select cell E5 and press the Delete key?

2. Delete that code and replace it with this

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    MsgBox "Range changed = " & Target.Address(0, 0)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Now select cell E5 and press the Delete key. What message do you get?

 

[Noz2k]

it says Range Changed = E5

So anyway, I actually have 2 main sheets where I want to employ this, and got it working for one of them.

I don't know what was going wrong, or really what I changed, but putting that msgbox in somehow inexplicably got the rest of it to work.

How can I change it so it also runs the code whenever E3 is changed? Where do I put the or statement?

 

[Noz2k]

Does anyone have an answer to this? Really should have this piece of work done by now, and this is the only problem I'm waiting to solve.

 

[Noz2k]

Solved, just did it the long way round, and added repeated the whole code for E3