(SOLVED) How to control one cell with two input cells

Hi again,

I have been working on my problem, and obviously no success. There is one piece of information which I think will limit even more what can be done to solve this whole thing.

The name of the scenarios are likely to change overtime (the user can actually change that in the user interface). So ideally, I would like to setup some sort of drop down list on the graph spreadsheet with the scenario names (already listed somewhere else in the workbook as a list) which would, probably via a macro, change the content of the "selector" cell in the user interface.

As I am writing this, I realise that it is probably super easy to setup for someone who knows VBA, but I keep trying and I am unsuccessful :-(

I hope this post will attract someone's attention :-)

Thanks guys!

Third update as I'm trying to find a way around my problem. Really, I would just need the drop down list (which could be done as a Combo box apparently, not that I know what it is.... maybe the equivalent of a drop down list with data validation but which could run a macro in the background....) to overwrite the cell in the user interface.

Seems a lot simpler now :-) I just need to find the code to write a value in that cell and to also link the choices from the drop down menu to a list from the workbook....

Hi,

If I understood correctly you just need a drop down menu.
May be you should put all parameters in one column and name it in both sheets to make use of it

If you could post an example workbook that would probably make things much clearer.

It sounds like what you need is just a bit of code which runs on sheet change event and reflects a change made on the graph sheet to the selector cell. It should be easy enough, but without seeing the structure of your workbook we're really in the dark.

Andrew-R, it sounds like what you just explained is what I need.

I just attached a basic version of what I'm after with the actual names for the spreadsheets. I would like to create in the graph spreadsheet (it's actually going to be 3 graph spreadsheets) a drop down list, or Combo Box that displays the list of scenarios existing in "Param" and when a scenario is selected, it overwrites the value of the cell "scenarioselector" in "DAILYPLOT".

This will then change the scenario check cell in "Param" where the magic happens (this part is already setup).

I hope this clarifies my (really confusing) explanation.

The only problem is that I don't know much about VBA and objects, so I hope I'll be able tof loow your procedure :-D

Thanks Andrew!

I'm not sure I will have time to test this today as I am at home on a Mac and VBA is not supported. I'll be reinstalling Windows during the day so hopefully I'll get a chance to try it out! Otherwise definitely tomorrow at work.

In the mean time, two quick questions:
. is a VBA macro like the one you wrote always "working" in the background? I was under a (stupid) impression that macros had to be enabled by an object/button on the spreadsheet.
. in your macro, how does Excel know that the cells D3 is in the spreadsheet called "graph"? I understand that you set the target spreadsheet via TargetBook but I can't see anything like this for the original input. I'm mainly asking to understand the reasoning behind it so I can have that function in two other tabs.
....
I just realized one thing: can I "duplicate", in a second alternative macro, the lines

HTML Code: 

so it can also change the drop down menu I will have in Graph2 and Graph3? The three graph spreadsheet will always use the same set of data, but ploted differently. I would like to have the flexiblity of changing the scenario from any spreadsheet basically, with a direct impact on DAILYPLOT (as it is the one that really changes the "Param" spreadsheet.

This is probably getting confusing, but I keep thinking of new ways of doing things after the work is done :-D

Thanks again guys!

Subway, to answer your questions:

1. Excel supports event-driven macros - macros which are called when a specific event happens. The instructions I gave you insert the macro into the change event for the graph sheet - a macro that is automatically run every time a change is made to the graph sheet.

2. The macro knows that cell D3 is on the graph sheet because it's in a block of code which is specific to the graph sheet, so we don't need to explicitly say we're working with the graph sheet.

3. If you want the same macro to run on multiple sheets then rather that use the individual sheet change event we could use the Workbook SheetChange event - a macro that is called when any sheet in the workbook changes. We'd add this code in the same was as I described above, except at step 3 you'd double click on "ThisWorkbook" in the left hand pane, and at step 6 you'd select "SheetChange" from the drop-down. The code would look like this ...

Please Login or Register  to view this content.

You'll see that there's a new line of code setting a constant called sGraphSheets, which lists the sheets to which we want the macro to apply. There's an extra line of code to make sure that the sheet that changed is one of the ones we're interested in.

Hi there!

Thank you very very much for your help and explanation! I think I'm nearly there.

I entered the code as you explained, changed the cells to match my workbook but there is one more thing to tweak I think.

At the moment, if I change something on Graph1, it changes the scenario selector on DAILYPLOT (which is the main thing for me) but the cells on Graph2 and Graph3 do not change. The only thing that I can think of is that I kept calling them graph spreadsheet which may have led you to think that they were actual graph spreadsheet (hence the constant sGraphSheets) but they aren't. I had to create new spreadsheets to dump the graphs on them and create my drop down list.

So could it come from this constant called sGraphSheets?

Below is a copy of the coding:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Const sChangeRange = "S4"
Const sGraphSheets = "Plot - Total Port & Site:Plot - K Port:Plot- B Port (M&D)"
Const sTargetBook = "DAILYPLOT"
Const sTargetRange = "G20"

If InStr(sGraphSheets, Sh.Name) > 0 Then
If Not Intersect(Target, Sh.Range(sChangeRange)) Is Nothing Then
Sheets(sTargetBook).Range(sTargetRange).Value = Sh.Range(sChangeRange).Value
End If
End If

End Sub

Thanks again for your help!

Hi again,

I've been trying a few different things to understand what's happening. I just realised, shouldn't the sTargetBook also include "Plot - Total Port & Site:Plot - K Port:Plot- B Port (M&D)" and the cell S4?

The whole point of what I'm trying to do is that a change of S4 in "Plot - Total Port & Site" would change G20 in DAILYPLOT but also S4 in "Plot - K Port" and "Plot - B Port" and the other way around...

I tried a few things, and it obviously failed miserably :-)

Any thoughts?

Yes, we should be able to do something with this ...

Please Login or Register  to view this content.

This will loop through however many sheets are specified in the sTargetBook constant and, for each one, update the correspoding cell in sTargetRange.

sTargetBook and sTargetRange must both contain the same number of elements, even if the same cells are to be updated in each sheet.

Does that make sense?

It does make sense indeed! I actually had the first part right, but obviously not the section about the loop.

I just put the coding in and when I click on S4 (in any of the spreadsheets), it brings up the VBA window with an error message "Compile error: Sub or function not defined" with the first line highlighted...

Please Login or Register  to view this content.

Andrew, I already know you're my man! Can i have your opinion on this? :-)

Sorry, minor typo ... the line:

Please Login or Register  to view this content.

Should be:

Please Login or Register  to view this content.

Same problem unfortunately :-(

Could it come from the fact that the VBA editor did not include the lines you talked about in your procedure?

I've attached a screenshot of what the doc looks like in case it could help you.

Thanks again!

Originally Posted by SubwAy

Could it come from the fact that the VBA editor did not include the lines you talked about in your procedure?

So when you selected the SheetChange event it didn't put in the first and last lines of the sub automatically? That's strange.

I tried to redo the procedure that you gave me but I can't do the steps:

4. A blank code window will open up. From the left-hand drop down at the top select "Worksheet"
5. Excel will insert two lines of code automatically, ignore them.
6. From the right-hand drop down select "Change"

I can't find the option "Change" you're referring to.

From what you can see on my screenshot, does it look ok or can you see an obvious mistake?

That was for the original code - in the ThisWorkbook context the right-hand drop-down should be "SheetChange"

You're getting an ambiguous name because you've already got a sub-routine with that name.

Cut the entire existing procedure and paste it into Notepad.

Insert the SheetChange event using the drop downs

Paste all of the code, except the first and last lines in between the two lines added by Excel.

That should be it.

Don't worry about hassling me - I'm happy to help out.

Legend...... wait for it......... ary......

Thank you so so much Andrew. Everything works really well :-)