How to simulate a mouse click in a text box

[Leon Lai]

Hello,

I have a User Form containing (inter alia) a combo box and a text box.

All my actions are taking place in the combo.
At some stage, I want to left- click in the text box, so that the mouse cursor leaves the combo and goes into the text box.

It's easy to do manually, but I can't figure out how to do it in VBA Code.

Why do I want to do this? It's a too long story to narrate. Let's say my add-in will work correctly only if I succeed to move my mouse cursor out of the combo and into the text-box. I don't really understand how it works, but it works.

I tried the foll. but it does not work:
Me.txtBox1.setfocus

Any help most appreciated.

Thanks
Leon Lai

[HansV]

What is the context in which you tried

Me.TextBox1.SetFocus

[Leon Lai]

Hello HansV

I put the line in a button click event:



'======================================
Private Sub btnAddSelectedCode_Click()
'=======================================


'// This Sub calls the Sub: AddTheSelectedCode
Dim LastRow As Long
LastRow = LastRowInCltDescCol()


Dim i As Long
Dim strFound As Boolean

'// LEON //// just added this. But it does not work!!!
Me.TxtInfo.SetFocus


With Me.cboDatabase
For i = 0 To .ListCount - 1

If .List(i) = sMatch Then strFound = True
Exit For
Next i

End With

If Not strFound Then
UserForm1.TxtInfo.Value = ""
MsgBox "' " & UCase(sMatch) & " ' not found in list." & vbNewLine & vbNewLine & _
"Please select an item from the list.", vbOKOnly + vbCritical, "VALIDATION ERROR!"

ElseIf strFound Then

Call AddTheSelectedCode

Me.txtQty.ForeColor = vbBlue
Me.txtUoM.ForeColor = vbBlue
Me.TxtInfo.Value = "1 Item Added"
Me.btnAddSelectedCode.BackColor = &H8000000F '// User Form original color
Me.lAdd.BackColor = &H8000000F

Me.cboDatabase.SetFocus

End If

ActiveWorkbook.Worksheets("Input").Range("A:A").EntireRow.AutoFit

End Sub

[HansV]

Strange - when I use SetFocus in the On Click event of a message box, I see the insertion point jump to the text box immediately.

What part of the code fails if TxtInfo does not have the focus?

[LisaGreen]

Hi Leon,

Is it possible for you to post the workbook at all?

Lisa

[Leon Lai]

Strange - when I use SetFocus in the On Click event of a message box, I see the insertion point jump to the text box immediately.

What part of the code fails if TxtInfo does not have the focus?

---
Hello HansV

Tomorrow, I will send you my add-in after removing all the irrelevant codes.
This may help you to understand my problem.

Best Regards
Leon

[Leon Lai]

Hi Leon,

Is it possible for you to post the workbook at all?

Lisa

Hello Lisa

Thanks for your reply.
My add-in is a quite complex add-in, and sending it as it is won't help anybody.
I am thinking of simplifying it by removing all irrelevant codes and posting the add-in by tomorrow.

Best Regards,
Leon

[Leon Lai]

Hello HansV

I have prepared a simplified add-in.

How can I send (a) the Excel add-in and (b) a specimen workbook on this forum?

Thanks
Leon

[HansV]

I'd zip the files.
Type the text of your reply in the reply box at the bottom of the thread.
Then click the 'Full Editor and Preview' button.
Drag the zip file (max size 256 KB) into the reply box.
Click Submit.

[Leon Lai]

What my add-in does?
It helps users to find rapidly the Item Codes of all items ordered by a client
I have removed all codes and objects which are not pertinent to my problem. I hope this will be easier to understand

The main functionalities in my add-in.
• There is a hidden worksheet called “Database” which is normally populated by running a stored procedure on SQL Server.
• Once the Database worksheet has been populated, I use arrays to pass the data to a combo box.
The combo box is where all the action takes place.
• The user can select items from the combo’s drop down. Alternately, he can type a few letters in the combo, and the combo will do a SEARCH AS YOU TYPE

How to use my add-in?
• First, you must install the add-in on your computer.
• Then you must open the SIMPLIFIED workbook (Add-in only works with this workbook).
• Click “ADD-IN 16” on the Excel Menu, and there you are.
My problems are explained below:

See Fig 1

Fig1.png

See Fig 2

Fig 2.png

See Fig 3

Fig 3.png

[Leon Lai]

Hello Lisa
I sent you the add-in.

Please feel free to ask any other information.

Thanks
Leon

[LisaGreen]

Hey Leon.. where did you send it please?

Lisa

[SpeakEasy]

>my add-in will work correctly

Sorry - just to clarify: by 'correctly' you just mean you want to get rid of the 'shadow' - the unwanted dropdown? If so, then this could be better fixed by modifying the Search As You Type code (that is presumably in your addin that you have not shared with us) to ensure the dropdown is not shown when the final selection is made.

That being said, you've put the

Me.TxtInfo.SetFocus

in the wrong place in your button code to achieve your goal. You've put it BEFORE 'Add'ing the chosen item into the combobox, but it is that Add that causes the drop down.

I suggest putting it just before

Me.cboDatabase.SetFocus

[Leon Lai]

Sorry everybody and thanks for your interest.

It seems I did not send the add-in correctly,
I'll send them tomorrow morning when I'm back to office.

Thanks
Leon

[Leon Lai]

Hello, everybody

I am attaching the following files so that you can see how my add-in works. Hope you can spot my error.
Recap:
My main problem is how to remove the unpleasant "shadow" which appears in the combo's list box.

When I hit ESCAPE (or TAB or click the mouse), the "shadow" disappears.
But I have been unable to simulate this action using VBA.

Any help most appreciated.

Leon Lai

Simplified Add-in.zip

Simplified Workbook.zip

[Leon Lai]

Hi,

I just sent my add-in and workbook.

Regards
Leon

[Leon Lai]

Hey Leon.. where did you send it please?

Lisa

-----------


Hi,
I just sent my add-in and workbook.
Regards
Leon

[Leon Lai]

Hello,
I sent you my add-in and simplified worksheet.
Hope you can help.
Thanks
Leon

[HansV]

Does anything go wrong if you comment out the line

Code: Select all

Application.OnTime Now + TimeSerial(0, 0, 0), "ShowDropdown"

in the cboDatabase_Change event procedure?

[SpeakEasy]

>Application.OnTime Now + TimeSerial(0, 0, 0), "ShowDropdown"

Yep, I question the use of this as well, was literally about to ask a similar question ... (certainly the removal fixes the problem)