Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Userform closing error

Status
Not open for further replies.

DazTaylor

Programmer
Jun 18, 2007
3
GB
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,

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.

Code:
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?
 
Hi bdmangum, yeah the code is fine, don't know what was going on! Just me on a monday ;-) Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top