Activate Workbook via UserForm/Listbox ???
 

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

Activate Workbook via UserForm/Listbox ???
 

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

Activate Workbook via UserForm/Listbox ???
 

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

Activate Workbook via UserForm/Listbox ???
 

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 -