VBA Saving worksheet (Help)

[dave8]

Please take a look at this code. I have a save button on my form that triggers this code to save a copy of the worksheet. I think the problem I'm having is that it creates another instance of Excel. That is, when I look at Windows Task Manager, I see two Excel applications running (and sometimes more than one). And, at some point, Excel hangs and I have to terminate the process. Is there something else I need to do here?

Private Sub CommandButton2_Click()
   Dim wsName As String
   Dim Wksname$
   Dim str, strLast, strFirst As String
   Dim r As Range
   Dim savewks$
   
    
   If TextBox2.Value <> "" Then
     str = Split(TextBox2.Value, ",")
     strLast = str(0)
     strFirst = str(1)
   Else
     MsgBox "There is no data to be saved.",
     Exit Sub
   End If
   
   
   
   Wksname = ActiveSheet.Name
   
   On Error Resume Next
   UnProtectSheet (Wksname$)
   Sheets("sheet1").Copy before:=Sheets(9)
   ActiveSheet.Shapes("CommandButton2").Select
   Selection.Delete
   savewks$ = ActiveSheet.Name
     
   
   
   Call clearFormfields                         
   Sheets(savewks$).Activate
   ProtectSheet(Wksname$)
End Sub

 

[Michael M]

Does this code actually work?
It won't run for me.
Also, have you had a look at the "ClearFormFields" macro as well.
The issue could be there and not in this one.

 

[dave8]

Yes, the code works. The procedure, clearformfields is nothing more than setting textboxes = "". The problem is that Excel hangs for some reason. I'm just suspecting the multiple Excel instances listed in the task manager.

 

[Michael M]

Have you tried running Task Manager beside the macro ( windows reduced to side by side) and see where the macro kicks in the 2nd Instance of Excel ?

 

[dave8]

I just tried it and the second instance runs during execution of the macro. I think its the line where making a copy of the sheet.

 

[Michael M]

Ok, go through the same process using F8. this will do it line by line and each line is highlighted yellow just before it runs that line

 

[dave8]

The line that creates a new Excel process is the copy:

Sheets("Sheet1").Copy before:=Sheets(9)

 

[Michael M]

That wouldn't do that with the line you suggested...normally.
So, after you...UnProtectSheet (Wksname$)...is there a Sheet1 available to copy OR a sheet (9) to copy before ?.
If not, it might kick in the Excel....but normally you should get a "Subscript out of Range" error

 

[dave8]

Yes, both sheets are available. The copy works and *does* put it in sheet9. But, it seems like it sucks up all the memory and crashes.

 

[Michael M]

Mate, you've got me !!
Have you tried it on another PC ?...does it crash then ?
How big is sheet1...do a CTRL + End and see where the last cell is !!
If it's huge it will cause problems, same with file size...but that should give "out of memory" error
The only other thing I can think of is there are too many apps open and it's just enough to tip it over.. .