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" wrote in message
...
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" wrote in message
...
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).Ra nge("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 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 cell and
change worksheets - essentially the form doesn't have any lock on excel

* UserForm1.Show vbModeless

"Paige" wrote in message
...
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- Hide quoted text -

- Show quoted text -