Excel vb script crashes When trying to print or clear sheets

Question

I am trying to create a Vb script in Excel that will take 6 textbox inputs and place them in the desired locations (i have that working perfectly) next i need it to create "X" number of copies and increment a specific Cell. I have this working to a point. I get two sheets that have "1 of X" the rest of the copied sheets are fine... "2 of X".... "3 of X" and so on. When i have more then around 10 sheets created Excel Freezes when i try to print or delete all the Sheets (except Sheet1). Any thoughts on what i am doing wrong? Thanks for any input.

    Private Sub CommandButton1_Click()
    ' sets my text from the text boxes to the excel cells
    Range("C3").Value = TextBox2.Text
    Range("C4").Value = TextBox3.Text
    Range("C5").Value = TextBox4.Text
    Range("C6").Value = TextBox5.Text


    Range("E11").Value = TextBox6.Text
    Range("I15").Value = TextBox1.Text


    Dim p As Integer
        ' this is my value that gets incremented so its started at 1
        Range("G15").Value = "1"

        ' this is my loop that creates each sheet and increments the X of X number
        For p = 0 To (TextBox1.Text - 1)
        Sheet1.Copy After:=Sheet1
        Range("G15").Value = (p + 1)

        Range("C3").Value = TextBox2.Text
        Range("C4").Value = TextBox3.Text
        Range("C5").Value = TextBox4.Text
        Range("C6").Value = TextBox5.Text


        Range("E11").Value = TextBox6.Text
        Range("I15").Value = TextBox1.Text

        Next p




    End Sub

    Private Sub CommandButton2_Click()
    'This deletes all my extra sheets and clears my text boxes
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In Worksheets
    If ws.Name <> "Sheet1" Then ws.Delete
    Next
    Application.DisplayAlerts = True

    Range("C3").Value = ""
    Range("C4").Value = ""
    Range("C5").Value = ""
    Range("C6").Value = ""


    Range("E11").Value = ""
    Range("I15").Value = ""
    Range("G15").Value = ""

    End Sub



    Private Sub CommandButton3_Click()
    'My button on the excel sheet to open the user form
    UserForm1.Hide
    End Sub

    Private Sub CommandButton4_Click()
      'This is my print out the sheets button 
      Dim ws As Worksheet
      Dim i As Integer
      i = 0
      For Each ws In ActiveWorkbook.Worksheets
        If (i = 0) Then
    ws.Select
        Else
    ws.Select False

End If

      i = i + 1
      Next ws

      Application.Dialogs(xlDialogPrinterSetup).Show
      ActiveWindow.SelectedSheets.PrintOut Copies:=1

    End Sub

Update (may help to have the error information) I can confirm it happens on anything over 9 for the TextBox1.Text

Error says.....

Run-time error '-2147417848(80010108)':

Automation error The object invoked has disconnected from its clients

Raidcore Raidcore · Accepted Answer · 2014-07-10T17:57:43

Change your

Sheet1.Copy After:=Sheet1

to

Sheet1.Copy Before:=Sheet1

The error maybe related to the application freezing from running the macro. Try disabling the screen update while your actions are running.

Application.ScreenUpdating = False

Application.ScreenUpdating = True

source

Excel vb script crashes When trying to print or clear sheets

1 Answers