Change the list contents in a cell, when changing the contents in another cell.

[Hans Gatu]

The user fill in these cells:
D5, D6, D7, D9 and D10.
Other cells are locked, so with Tab, Enter or Arrows, you step through these cells.
D5 and D7 are pull down lists with 10 different alternatives each.
Normally you start with selecting someting in the D5 list.
Depending on what you select in D5, the content list of D7 shall alter beween two different lists.
Those two lists are the contents of AM3:AM12 or AN3:AN12.

Basically it works now, but if you have a value (a word) in the D5 list, that correspond to the AM3:AM12 list in D7,
then you see a sertain value in D7 (lets say that you see the value of the AM6 cell).
If you now select another value (another word) in the D5 list, that now correspond to the AN3:AN12 list in D7,
then you instantly should see a value in D7, that correspond to the AN3:AN12 list (i.e. you should see the value of the AN6 cell),
but now the old value (the AM6 value) is still in the cell, until you actually step to D7 and pull down the list and select the new value.

I was thinking that the OnKey function could work, when you leave the D5 cell with any of the keys:
Application.OnKey "{ENTER}"
Application.OnKey "~"
Application.OnKey "{DOWN}"
Application.OnKey "{UP}"
to activate the shift of lists in D7 and also shift the value in D7 that you see, before you actually get to the D7 cell.
But I havn't got it working.

Any idea to make it work ?

/ Hans Gatu

[p45cal]

The description's very good, but to save us some work (and guessing wrongly) attach a file with this scenario so that we can experiment.
(I'd be considering the Worksheet_Change event)

[p45cal]

cross post sans links:
https://www.mrexcel.com/forum/excel-...ther-cell.html

[Hans Gatu]

cross post sans links:
https://www.mrexcel.com/forum/excel-...ther-cell.html

Yes, I posted it in that forum at first, but haven't got any answers there.

Well, the file has 10 worksheets, contain quite some amount of VBA code, and total size of approx 500kB.
I am using Worksheet_Change(ByVal Target As Range) already, so I added Application.OnKey but then I got an error.
Since I am using the Swedish language version of Excel, the error message is in Swedish, but if I translate the message it is:

It is not possible to run the macro !TRUE. Maybe the macro isn't available in this workbook or all macros are inactivated.


And the code, where I put in the Application.OnKey is like this:

---------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)


If Worksheets("A").Range("Q8") > 200 Then
CommandButton5.Visible = False

Else
CommandButton5.Visible = True

End If


If Worksheets("A").Range("D5").Value = ("TBLE") Then
CommandButton12.Visible = False


Else
CommandButton12.Visible = True

End If


If Worksheets("A").Range("D19") = ("") Then
CommandButton1.Visible = False

Else
CommandButton1.Visible = True

End If


Application.OnKey "{ENTER}", Worksheets("A").Calculate
Application.OnKey "~", Worksheets("A").Calculate
Application.OnKey "{DOWN}", Worksheets("A").Calculate
Application.OnKey "{UP}", Worksheets("A").Calculate


End Sub

---------------------------------------------------------------------


/ Hans Gatu

[p45cal]

Well, the file has 10 worksheets, contain quite some amount of VBA code, and total size of approx 500kB.
I am using Worksheet_Change(ByVal Target As Range) already, so I added Application.OnKey but then I got an error.
Since I am using the Swedish language version of Excel, the error message is in Swedish, but if I translate the message it is:

It is not possible to run the macro !TRUE. Maybe the macro isn't available in this workbook or all macros are inactivated.

I understand your reluctance to post it, especially as it may contain sensitive information.
Instead, put together a small file, with only the necessary to reproduce the problem. You can do this either by creating a file from scratch or by removing irrelevant sheets/data/code from your existing file.
In doing this you may well solve your own problem, or at least find what's going wrong.

By doing this, the file will answer lots of questions we might need to put to you: is the code in the right code-module? is the code in the right workbook even?, is it firing at all?, and much more… This will ultimately save you and us time, bags of it.
Yes, it's some work for you, but it will encourage people to answer your query.
What's more, should it be necessary, it will encourage people to get ingenious with their solutions, and do what programmers do, use their ingenuity (the real meaning of the word 'engineer'), whereas with no file, all you'll get is a few shots-in-the-dark suggestions, or nothing at all (MrExcel seems to be such a case).

The error you're getting doesn't seem to come from the code you've copied here.

About cross-posting, I'm not a moderator here, but I am elsewhere, and the rules of these forums aren't there for fun, nor to get in your way. They are there for you to read before posting. The business about cross-posting without links is well explained here: http://www.excelguru.ca/content.php?184
Ultimately, it's you that will suffer if you ignore them.

By the way, this code:

If Worksheets("A").Range("Q8") > 200 Then
            CommandButton5.Visible = False
            
    Else
            CommandButton5.Visible = True
    
    End If
        
    
    If Worksheets("A").Range("D5").Value = ("TBLE") Then
            CommandButton12.Visible = False

            
    Else
            CommandButton12.Visible = True
    
    End If
    

    If Worksheets("A").Range("D19") = ("") Then
            CommandButton1.Visible = False
            
    Else
            CommandButton1.Visible = True
                
    End If

can probably be condensed to the likes of:

CommandButton5.Visible = Not (Worksheets("A").Range("Q8") > 200)
CommandButton12.Visible = Worksheets("A").Range("D5").Value <> ("TBLE")
CommandButton1.Visible = Worksheets("A").Range("D19") <> ""

[Hans Gatu]

I understand your reluctance to post it, especially as it may contain sensitive information.
Instead, put together a small file, with only the necessary to reproduce the problem. You can do this either by creating a file from scratch or by removing irrelevant sheets/data/code from your existing file.
In doing this you may well solve your own problem, or at least find what's going wrong.

Yes, I will put together a small file, with only the necessary to reproduce the problem, and try it out.

The error you're getting doesn't seem to come from the code you've copied here.

Well, the error doesn't show up whithout the Application.OnKey part of the code, and there is no macro !TRUE.

About cross-posting, I'm not a moderator here, but I am elsewhere, and the rules of these forums aren't there for fun, nor to get in your way. They are there for you to read before posting. The business about cross-posting without links is well explained here: www_excelguru_ca/content.php?184
Ultimately, it's you that will suffer if you ignore them.

(I had to change it to www_excelguru_ca because I was denied posting it due to too many URL's)

I have now added in the MrExcel Forum post, that it is in this forum aswell.

By the way, this code:
can probably be condensed to the likes of:

CommandButton5.Visible = Not (Worksheets("A").Range("Q8") > 200)
CommandButton12.Visible = Worksheets("A").Range("D5").Value <> ("TBLE")
CommandButton1.Visible = Worksheets("A").Range("D19") <> ""

I will try that also, but that part works as it is, though.


/ Hans Gatu

[p45cal]

Well, the error doesn't show up whithout the Application.OnKey part of the code, and there is no macro !TRUE.

Yes, you're right. OnKey expects a procedure name, but you have Worksheets("A").Calculate instead which, it seems, returns TRUE!

You'll need to do somethng like this:

Application.OnKey "{ENTER}", "DoStuff"
Application.OnKey "~", "DoStuff"
Application.OnKey "{DOWN}", "DoStuff"
Application.OnKey "{UP}", "DoStuff"

and elsewhere have a sub:

Sub DoStuff()
 Worksheets("A").Calculate
End Sub

But I don't think this is the way to go.
By the way, to revert to the default behaviour:

Application.OnKey "{ENTER}"
Application.OnKey "~"
Application.OnKey "{DOWN}"
Application.OnKey "{UP}"

I look forwarrd to your file.

[Hans Gatu]

I have attached a simplified file.
D5 and D7 should behave like D5 and D7 in the real file.
Depending of what letter is chosen in D5 (M or N), the list in D7 alters between M3:M11 and N3:N11.
If there is, say, M4 previously chosen, when D5 was M,
then N4 should be seen instantly when D5 changes from M to N.

/ Hans Gatu

[Tom Jones]

Is this what you want?

[Hans Gatu]

Thank you for the effort.

This is almost it.
When you have chosen a letter in cell D3 (let's say "M"), then the "M list" is in cell F3.
So far so good.
Next step is to choose a value in the list in cell F3, where we now have the "M list".
Let's say you choose "M5".
If you now go back to cell D3 and you choose "N" instead of "M", then the "N list" appear in cell F3.
Again, so far so good. But the value that appear should not be "N3" but "N5".
Then you can go to cell F3 and choose any other value, lets say "N8".
If you then go to cell D3 and choose "M", then the value "M8" should appear in cell F3.

So, as it is now, it changes between the "M list" and the "N list" in cell F3, in correlation with the value chosen in cell D3.
That is all correct, but it shouldn't always start at the first value in the list, but rather at the value that corresponds to the previous value chosen.

/ Hans Gatu

[p45cal]

Try the attached which contains:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D5")) Is Nothing Then
  Application.EnableEvents = False
  Set ddd = [IF(D5="M",M3:M11,N3:N11)] 'this is the data validation formula of cell D7
  x = Application.Match(Range("D7").Value, Range("M3:M11"), 0)
  If IsError(x) Then x = Application.Match(Range("D7").Value, Range("N3:N11"), 0)
  If Not IsError(x) Then Range("D7").Value = Application.Index(ddd, x)
  Application.EnableEvents = True
End If
End Sub

This will work best if there are no values common to both columns M and N.

[Hans Gatu]

Yes. That is exactly the functionallity that I wanted.
Thank you very much for the help to solve this problem.

/ Hans Gatu

[Hans Gatu]

This will work best if there are no values common to both columns M and N.

I was so happy, because it worked so well.
But, as you mentioned, it work best with no common values in the M and N colums.
However, in the real workbook there are common values, so it doesn't work with these values.
I have attached a file with the real contents of columns M and N.
The rest of this workbook is simplified, though.

/ Hans Gatu

[p45cal]

But, as you mentioned, it work best with no common values in the M and N colums.
However, in the real workbook there are common values, so it doesn't work with these values.

In this case it's not the similar values in the different columns, it's because your strings include the tilde(~) which is a wildcard character (actually it's the escape character when you want to look for a literal asterisk or question mark).
So a tweak to the macro in the attached:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D5")) Is Nothing Then
  Application.EnableEvents = False
  Set ddd = [IF(D5="M",M3:M12,N3:N11)]    'this is the data validation formula of cell D7
  SearchItem = Replace(Range("D7").Value, "~", "~~", 1, , vbBinaryCompare)
  x = Application.Match(SearchItem, Range("M3:M11"), 0)
  If IsError(x) Then x = Application.Match(SearchItem, Range("N3:N11"), 0)
  If Not IsError(x) Then Range("D7").Value = Application.Index(ddd, x)
  Application.EnableEvents = True
End If
End Sub

In this case, even if there are similar values in columns M/N, as long as they're in the same row (which they are) it'll be fine. If ever that won't be the case, then the macro can be altered to cater for that, it's just that it'll be a bit more long-winded.
I've ignored the cells M12:N12.

[Hans Gatu]

Yes! Perfect!
That's it. Now it works just the way I wanted.
Thank you very much for your help !

/ Hans Gatu

[Hans Gatu]

I'm sorry to bother you again, but now when I try to implement the function, I run into new problems.
I have attached a file (again simplified compared to the real file) that shows the problem.
The "M" and "N" is now in cell H5 instead of D5 and is a result of what's in D5.
I.e. if what's in D5 begins with "MQ..." H5 is "M", or else H5 is "N".
The "M" and the "N" in H5 is supposed to result in the same changes in D7, as the changes in D5 caused before,
but as it is now, the changes does not occur.

/ Hans Gatu

[p45cal]

change:
If Not Intersect(Target, Range("H5")) Is Nothing Then

back to:
If Not Intersect(Target, Range("D5")) Is Nothing Then

[Hans Gatu]

Yes, of course. That's it.
Again, thank you very much p45cal for your help.

/ Hans Gatu