Using ComboBox Change or Click event with IF or Case statement

[julhs]

Yet again I can’t compile the syntax to get the desired outcome.
What I am trying to achieve is;
If ComboBox20 is Changed or Clicked, EITHER D22 or E22 will be selected DEPENDENT on whether D22 is greater than "", or E22 is greater than "".
Note; D22 and E22 will NEVER BOTH be greater than "", it can only be one or the other.
To summerise;
If ComboBox20 is Changed or Clicked and D22 is greater than "", then D22 is selected
Or
If ComboBox20 is Changed or Clicked and E22 is greater than "", then E22 is selected

I've tried various different approches but all have failed.

 

[Micron]

You wouldn't test for greater than "" but rather, is not equal to. If you really need both events for the same control you'd have to put the same code in two places. These 2 tests ought to work, but without seeing your code I don't know how I'd integrate them. It also depends on each condition being mutually exclusive as you describe. If it were not, the tests need to be on more than one line so that an Exit Sub can be inserted. Otherwise, the second test could undo the first.
If Range("D22") <>"" Then Range("D22").Select
If Range("E22") <> "" Then Range("E22").Select

That also assumes the combos are on a sheet. If not, then where are they?

 

[julhs]

Thanks for response Micron.
ComboBox20 is embedded on the Active Sheet.

BUT before I can respond and test further, can you confirm if you can have a “Change Event and a “Click Event” coded separately for the same ComboBox?

It seems the initial mistake I made with my coding was to;
declare “Dim rng As Range”
and then use “If rng("D22")
Because when I changed it to “If Range("D22"), then I at least got partial success to the desired outcome.
This is code now works for the Change Event

Private Sub ComboBox20_Change()
  Dim wt As Worksheet
    Set wt = ThisWorkbook.ActiveSheet
       'Dim rng As Range
       'If rng("D20").Value > "" Then ,this did not work
   If Range("D20") > "" Then
      Range("D22").Select
 Else
   If Range("E20") > "" Then
      Range("E22").Select
  End If
 End If
End Sub

But how can I add a separate “Event” to handle if the Combobox is Clicked but no Change is made?

 

[julhs]

Just realised that I should have originally used ;

If rng("D19") > "" Then

As opposed to

If rng("D19").Value > "" Then

 

[Micron]

have a “Change Event and a “Click Event” coded separately for the same ComboBox?

As long as an object has an associated event, you can use any of them. However, sometimes similar events can cause conflicts or undesirable results. I don't understand why you need the click event (which is often unresponsive in Excel anyway). You want code to run when the user does what, click in the combo, or when user drops down the list?

 

[Micron]

Just realised that I should have originally used ;

If rng("D19") > "" Then

As opposed to

If rng("D19").Value > "" Then

Those are exactly the same thing. Value is the default in most cases and need not be used in those cases.

 

[julhs]

You want code to run when the user does what, click in the combo, or when user drops down the list?

Code I posted will go to the required cell if there is a “Change” in the Combobox but not if I just select the Combo but make no “Change” to it
Essentually I’m trying to circumvent having to manually select the next required cell to input data into if I inadvertently select the Combo but DON’T make a “Change” to it (in the same way the “Change” event is fired)

Leave it with me to testing a possible solution that has just come to mind re using “Change Event and a “Click Event” for the same Combo

 

[Micron]

AFAIK, Click event code won't fire a combo on a sheet. Does for Access though, which is kinda stupid. Maybe you can use Dropdown as suggested, or GotFocus event, or both if needed.

 

[julhs]

Thanks.
I will experiment with your last suggestions

 

[julhs]

Finding a solution to this query in some ways out ways the code and effort required for a minor enhancement!
But have a couple of quick questions.
Are “Select” and “GetFocus” the same thing?
If not, I’m assuming they MAY require different syntax?
If they are not the same, how would each code be written?
Also,
Can I use wildcards eg;

If Range("D24") = "*Check*" Then

Or would I need to specifically use,

If Range("D24") = "Check Entry value" Then