Maintaining selections in UserForm

[Tony Bender]

My application consists of two workbooks. The first is my menu which
has one sheet with a button that when clicked opens a UserForm (UF3).
From this the user makes a selection from a ListBox (LB1), then makes
one selection from several different OptionButtons. The selected
OptionButton populates a second ListBox (LB2). The user makes a
selection from LB2 and finally clicks on a CommandButton (CB1) which
retrieves and opens the second workbook.

Private Sub CB1_Click()
Workbooks.Open "C:\Data\Library\Report.xls"
Sheets("report").Select
Range("A1").Select

Unload UF3
End Sub

This works fine, but where I have problems is writing code enabling
the user to return to the UF3 to change one or more selections.

What I want to do is:
1. Add a button to the Report sheet so the user can return to the
UserForm and change one or more selections, but keep the Report.xls
file open in the background.

2. Have the UserForm maintain their previous selections so they don't
have to start the process from scratch.

3. Upon their second wave of selections since the Report.xls is
already open, write the code so the user won't be alerted about re-
opening a file that is already open, and being in a 'read-only' mode.

I've been trying all sorts of code but nothing is working

I am working in Excel 2003.

Thank you for any direction with this.

 

[Rick Rothstein]

Instead of unloading the UserForm, try setting its Hide property to True in
order to hide it instead.

 

[john]

As rick already suggested, hide your form to retain values.
To resolve the issue of your code opening an already open workbook, try the
following as an approach:

Sub CB1_Click()

Dim wb As Workbook
Dim MyPassword As String
On Error Resume Next

Set wb = Workbooks("Report.xls")
If Not wb Is Nothing Then

wb.Activate

Else

MyPassword = "ABC123" '<< change as required

Set wb = Workbooks.Open("C:\Data\Library\Report.xls",
ReadOnly:=False, Password:="mypassword")



End If

With wb.Sheets("report")
.Activate
.Range("A1").Select
End With

UF3.Hide

On Error GoTo 0
End Sub

 

[Tony Bender]

As rick already suggested, hide your form to retain values.
To resolve the issue of your code opening an already open workbook, try the
following as an approach:

Sub CB1_Click()

    Dim wb As Workbook
    Dim MyPassword As String
    On Error Resume Next

    Set wb = Workbooks("Report.xls")
    If Not wb Is Nothing Then

        wb.Activate

    Else

        MyPassword = "ABC123" '<< change as required

        Set wb = Workbooks.Open("C:\Data\Library\Report.xls",
ReadOnly:=False, Password:="mypassword")

    End If

    With wb.Sheets("report")
        .Activate
        .Range("A1").Select
    End With

    UF3.Hide

    On Error GoTo 0
End Sub
--
jb














- Show quoted text -

JB, thank you for your idea...

Adding the code you suggested gets me half way there...

When I click on my CommandButton it opens the "Report.xls", and when I
click on the "Return to Menu" button it brings back the menu (UF3)
complete with the previous selections in LB1 and LB2. However, as
soon as I try to make a selection in either ListBox, I get an error.

Here is the code I have for the Return to Menu, and what I am trying
to achieve is to have the "Report.xls" file remain open in the
background, and not close it.

Sub ReOpenUF3()
UF3.Show
End Sub

Is there some property in the ListBoxes that I need to address.
Something like 'de-select'. I don't want to 'clear' the contents of
the ListBoxes because I'd then need to input the entries again.

I appreciate any ideas

Thanks again,