Userform closing error

[DazTaylor]

Hi all,

I’ve an excel user form, within personal.xls, that is used to run several bits of code, basic copy and paste between sheets and workbooks. I’ve added the code to copy a sheet from one workbook to another:

Windows(TPAForm.txtopenxl.Value).Activate 'Select the xl with the sheet
Sheets(TPAForm.cbotosheets.Value).Select 'activate the sheet to copy

Sheets(TPAForm.cbotosheets.Value).Copy Before:=Workbooks(TPAForm.txtcurrxl.Value).Sheet(TPAForm.cbocurrsheets.Value)

the code works fine but the form closes afterwards, is there a way to keep the form open?

Ta

 

[DazTaylor]

Closed. I'm going mad!

 

[bdmangum]

DazTaylor,

I created a test file to check to see if I could mimic your problem. I never encountered your problem. After what exact line does the form close? I could never get mine to close, it always stayed open upon completion of the code. Below is my attempt to mimic your problem.

Private Sub UserForm_Initialize()

For Each w In Workbooks
    Me.txtopenxl.AddItem w.Name
Next w

For Each w In Workbooks
    Me.txtcurrxl.AddItem w.Name
Next w

End Sub


Private Sub txtcurrxl_Change()

On Error GoTo last
Workbooks(Me.txtcurrxl.Value).Activate
With Workbooks(Me.txtopenxl.Value)
    For Each s In Worksheets
        Me.cbocurrsheets.AddItem s.Name
    Next s
End With
last:

End Sub


Private Sub txtopenxl_Change()

On Error GoTo last
Workbooks(Me.txtopenxl.Value).Activate
With Workbooks(Me.txtopenxl.Value)
    For Each s In Worksheets
        Me.cbotosheets.AddItem s.Name
    Next s
End With
last:

End Sub


Private Sub CommandButton1_Click()

If Me.txtcurrxl.Value <> "" And Me.cbocurrsheets.Value <> "" Then
    Windows(Me.txtopenxl.Value).Activate 'Select the xl with the sheet
    Sheets(Me.cbotosheets.Value).Select 'activate the sheet to copy
    Sheets(Me.cbotosheets.Value).Copy Before:=Workbooks(Me.txtcurrxl.Value).Sheets(Me.cbocurrsheets.Value)
End If

ActiveSheet.Range("A5").Value = "True"

End Sub

Let me know if that is similar to your code. Again what exact line is the last line processed in your form code?

 

[DazTaylor]

Hi bdmangum, yeah the code is fine, don't know what was going on! Just me on a monday ;-) Thanks