Error 50290 on Selection Macro

[garyj]

Hello to the VBA experts, and thanks so much for your help.

I have three data validation dropdown lists on my sheet. They work together to locate invoices.
One selects the area for the search. The second selects the group according to the selected area. The third selects from the invoice numbers for the group.

Because data validation dropdowns do not have arrows until the cell is selected, I created an image of one using [screen copy/crop/paste]. I placed the image right over where the other one would appear. The user has no idea which is which, as the real one appears over the other when the cell is selected.

It works like this. When the user selects the image, the attached macro selects the cell, which shows the other dropdown and and auto-drops the selection list. It's slick.
Here are the 3 macros that make it work.

Sub DropdownSelectArea()
    Range("InvFSelect1").Select
    Application.SendKeys "%{DOWN}"
End Sub


Sub DropdownSelectGroup()
    Range("InvFSelect2").Select
    Application.SendKeys "%{DOWN}"
End Sub


Sub DropdownSelectInvoice()
    Range("InvFInvNo").Select
    Application.SendKeys "%{DOWN}"
End Sub

But there is one issue. If the user selects the dropdown, then instead of choosing within the list, decides to select the other dropdown and clicks the other arrow image...
1. The system is waiting for a response within the list.
2. At the same time VBA is telling the system to select a cell range instead.
3. It throws a 50290 error.

I tried adding an error trap, but it seems to skip that for some reason. None of the code will work, so the error trapping would have to jump the macro completely. It doesn't seem to respond to the On Error go to... so I removed that.

I tried to find a way to cancel the selection, such as sendkeys "{ESC}", but that erros as well.

Here is the strange bit. I select an image, it selects the cell and drops the list. I then select the other cell instead of the other image. The code doesn't run of course and there is no error. But I still have to select the arrow with a second click. This works without an error.

Any ideas on how to fix this? I have run out of Google search ideas.

[arnelgp]

show us your workbook or can you use a Userform?

[garyj]

I suppose I could use a Userform, though that means a button to bring it up, and a method to populate the dropdowns with the appropriate formulas.
I could show the workbook, though it's nearly a finished product. How do I show it to just one or two people so as to protect the product?
Should I just clip the appropriate pages? The entire workbook is 27 sheets.

Gary

[garyj]

I have uploaded a few sheets, having removed the bulk of them. The one I would like help with is Invoice Form.
Because other sheets and modules are mostly removed, not all the functions in Booking and Invoice Form will work. Also, there are lots of hidden columns in Booking, which I have an automated button system that reveals the columns as required, so that all can be viewed without horizontal scrolling.

Setup and ListFunctions have been modified to include the pieces necessary.

Ok so on Invoice Form, clicking the first item works - you may not notice that it changes the formula in A7 and also behind the second dropdown.
Then clicking on the second dropdown allows a selection of the client.

But if I click the image of the C1 dropdown (which runs the macro, selecting the cell and extending the list), but instead of clicking on an item, I select the image of the other dropdown, it begins a 50290 error.

I don't know if this can be solved without a lot of code?
But thanks either way.

Gary

[georgiboy]

Did you try the error trap as below?

Option Explicit

Global GblInvFArea As Variant


'Three Invoicing DropDowns
Sub DropdownSelectGroup()
    On Error Resume Next
    Range("InvFSelect2").Select
    If Err.Number = 50290 Then Exit Sub
    On Error GoTo 0
    Application.SendKeys "%{DOWN}"
End Sub


Sub DropdownSelectArea()
    On Error Resume Next
    Range("InvFSelect1").Select
    If Err.Number = 50290 Then Exit Sub
    On Error GoTo 0
    Application.SendKeys "%{DOWN}"
End Sub


Sub DropdownSelectInvoice()
    On Error Resume Next
    Range("InvFInvNo").Select
    If Err.Number = 50290 Then Exit Sub
    On Error GoTo 0
    Application.SendKeys "%{DOWN}"
End Sub

[garyj]

Did you try the error trap as below?

Yes, and at first I thought it was working, but later I noticed it was not. So I think I didn't do the test correctly... perhaps my Application.EnableEvents was false at the time and I didn't know it. Anyway, I tried again now and it still raises a code.



Screenshot 2024-02-12 143330.jpg

Gary

[Aflatoon]

Your sendkeys code doesn't work at all for me, but I don't get any errors either. This seems like overengineering to me, but it would also seem like just using Form comboboxes would make more sense.

[Paul_Hossler]

Sendkeys just toggles Num Lock for me

Known issue

https://www.msofficeforums.com/excel...s-numlock.html

[garyj]

Yes, Aflatoon, I felt it was over engineering as well. The problem is that cell level data validation dropdowns do not look any different than other cells, so to show the user what to do, I wanted a pic of the down arrow. At first the sendkeys didn't work, and I did something, and it began working. Just cannot recall what that something was.

Anyway, I don't want the user to have to make a decision on errors when error trapping isn't working. So I think I may have to draw a user form combo-box in the cells and code them instead. I am working tomorrow and Thursday, so maybe I will have time to check if answers come before then... and go from there.

[garyj]

Crazy threads about NumLock. Interesting.

[georgiboy]

In my place of work, I use the Input style that you can select from the ribbon, everyone eventually got used to the fact that the orange colour was for them to input data in. When they select the cell to input data the dropdown appears so they would use it.

It is strange as I don't get the error with the error trapping I posted above, maybe you are testing it in a different way. It also messes with my numlock state as above.

[Aflatoon]

I think I may have to draw a user form combo-box in the cells and code them instead.

Unless you have a specific need for the activex version, I would recommend sticking with the native Excel form control (the DropDown). They are a lot more stable.

[garyj]

Unless you have a specific need for the activex version, I would recommend sticking with the native Excel form control (the DropDown). They are a lot more stable.

For Sure! From what I know, that is the first set under Developer/Insert/ correct?

FrmControlsImg.jpg

[arnelgp]

I used commandbars to show the list.

[Aflatoon]

For Sure! From what I know, that is the first set under Developer/Insert/ correct?

FrmControlsImg.jpg

Yes, those are the ones.

[garyj]

Hello arnelgp..

Wow. You know, I used to do quite a bit of programming with Access, with some work in Excel. I didn't do much in Access after 2016. Recently I decided to take on a couple projects: one for our church, and one for my work company. It was during these projects that I discovered a whole new side of Excel - the whole Power Query area... and I was kind of wowed. But in the second project I did a bit more research and found dynamic ranges... and that double wowed me. It turns Excel into a database... which is what I have done with it. But you just showed me another whole area that I was clueless existed... triple wowed in about 6 months!

I hope you didn't go to a lot of trouble and time with it. I want to use it, or some of it, but alas... my project has taken me 250 hours already, and my boss says he will give me $250 for it. So rather than go deeper (which I would love to do for self) I am thinking I will just get by on the few finishing touches, and add the form control. If my dispatch uses this program it will lower the stress level over all of the workplace, and it can be a nice thing I did for someone while I learned. I am going to keep your info though and study it. Thanks.

Gary

[arnelgp]

I suggest you go back to Access.
Why? much faster than excel and easy to manage your tables.
If you have problem with Access there are lots of Forum also including UtterAccess.com and
Access World Forums.

goodluck!

[garyj]

I suggest you go back to Access.
Why? much faster than excel and easy to manage your tables.
If you have problem with Access there are lots of Forum also including UtterAccess.com and
Access World Forums.

goodluck!

Thanks for the suggestion.