Activate Workbook via UserForm/Listbox ???

[Paige]

I have the following code that when the main user form initializes, it fills
a listbox with the names of all open workbooks and also activates a specifc
workbook/sheet/cell. Problem is that in order for the user to be able to
change the auto-selected cell, they have to highlight the workbook in the
listbox first and then use an associated refedit on the same form; i.e., they
can't just move their cursor on the sheet (essentially working 'behind' the
userform) to select another cell, like other forms/refedits/listboxes that I
have....even tho the code is the same (except the other code does not have
the last 3 lines). Am sure it has to do with the fact that my code to
activate/select is not right. Can someone advise what I'm doing wrong
please?

Private Sub UserForm_Initialize()
Dim wb As Workbook
Application.ScreenUpdating = True
For Each wb In Workbooks
SelectFormulaCellWorkbook.AddItem wb.Name
Next
Workbooks(LookupValuesWBName).Activate
Worksheets(LookupValuesSheetName).Select
Range(LookupValuesCellAddress).Select
End Sub

 

[Patrick Molloy]

the userform defaults to "modal" which locks the apllication

if you change the calling code to modeless, your user can select a cell and
change worksheets - essentially the form doesn't have any lock on excel

UserForm1.Show vbModeless

 

[Paige]

Patrick, thanks. Did not have luck with this or the ShowModal property -
just hosed Excel up - maybe because there is a refedit in the userform.
Anyway, muddled around a bit more and added some code to the
userform_initialize sub, setting the focus back to the refedit, and that
seems to be working (for now at least)!

 

[norie]

Did you ever consider using Application.Goto...?

Option Explicit

Private Sub SelectFormulaCellWorkbook_Click()

Application.Goto Workbooks
(SelectFormulaCellWorkbook.Value).Worksheets(1).Range("A1"),
Scroll:=True

End Sub

Private Sub UserForm_Initialize()
Dim wb As Workbook

Application.ScreenUpdating = True
For Each wb In Workbooks
SelectFormulaCellWorkbook.AddItem wb.Name
Next

End Sub