I have a small issue, but I cannot find the code for it. For simplicity I made a simple example.

I made a validation userform with a modeless listbox (lsb_dataval_man). This listbox is populated with named ranges with only the names that have an error or are empty. Because there are many tabs, the user can navigate quickly to the sheets and cells which need attention and modify or populate them.

When you doubleclick a named range in the listbox, it will go to that reference and users need to enter data directly. See the simplified code for this double click below.

With the command Application.Goto ThisWorkbook.names(named_range).RefersToRange it will select the cell, but I am not able to type in the cell directly as like when you would do a select. A user needs to click the cell again or click in the formula bar and enter data. How can I change my code so after going to the cell of the named range, the user can type directly? Perhaps this is related to the modeless form which is still active?

The code:

Private Sub lsb_dataval_man_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim named_range As String
named_range = Me.lsb_dataval_man

'select the named range cell
Application.Goto ThisWorkbook.names(named_range).RefersToRange

End sub

CodePudding user response：

This should work:

Private Sub lsb_dataval_man_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim named_range As String
named_range = Me.lsb_dataval_man

AppActivate ThisWorkbook.Windows(1).Caption   'switches focus from userform to workbook 
ThisWorkbook.Names(named_range).RefersToRange.Select  'selects cell

End sub