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

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" <(E-Mail Removed)> wrote in message
news:70DCA10E-A509-4389-BB50-(E-Mail Removed)...
> 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, 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)!

"Patrick Molloy" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:70DCA10E-A509-4389-BB50-(E-Mail Removed)...
> > 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
> >

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

On Jun 23, 8:56*pm, Paige <Pa...@discussions.microsoft.com> wrote:
> 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)! *
>
>
>
> "Patrick Molloy" wrote:
> > the userform defaults to "modal" which locks the apllication
>
> > if you change the calling code to modeless, your user can select a celland
> > change worksheets - essentially the form doesn't have any lock on excel
>
> > * UserForm1.Show vbModeless
>
> > "Paige" <Pa...@discussions.microsoft.com> wrote in message
> >news:70DCA10E-A509-4389-BB50-(E-Mail Removed)...
> > > 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 inthe
> > > 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/listboxesthat
> > > I
> > > have....even tho the code is the same (except the other code does nothave
> > > the last 3 lines). *Am sure it has to do with the fact that my codeto
> > > 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- Hide quoted text -
>
> - Show quoted text -