Excel vb script crashes When trying to print or clear sheets

user3822454

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

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Program crashes when trying to print a double (but other types are ok)

From Dev

C++ Program crashes when trying to print data from Double Linked List

From Dev

GDB crashes when trying to attach

From Dev

ArgumentOutOfRangeException when trying to clear combobox

From Dev

VB Excel - When opening a word doc from VB with Excel I get a 'Variable not defined' error when trying to use ActiveDocument

From Dev

VB Excel - When opening a word doc from VB with Excel I get a 'Variable not defined' error when trying to use ActiveDocument

From Dev

Run script on all sheets excel

From Dev

File created in Open XML SDK 2.5 crashes excel when selecting print

From Dev

Print Multiple Excel sheets in a specific order

From Dev

Print Multiple Excel sheets in a specific order

From Dev

Excel macro to print sheets based on multiple values

From Dev

Excel VBA script crashes when calling a DDL function written in C, which returns a string BSTR

From Dev

Strange result when trying to print

From Dev

App crashes when trying to implement multilingual support

From Dev

AngularJs crashes when trying to access data in directive

From Dev

App crashes when trying to show AlertDialog in thread

From Dev

Meteor app crashes when trying to populate collections

From Dev

App crashes when trying to fullscreen activity

From Dev

App crashes when trying to record audio

From Dev

Application crashes when it trying to display Toast

From Dev

Program crashes when trying to read numbers with scanf

From Dev

Bootstrap carousel crashes when trying to skip index

From Dev

App crashes when trying to open a fragment with listview

From Dev

App crashes when trying to start a new activity

From Dev

Program crashes when trying to retrieve contents of pointer

From Dev

App crashes when trying to connect to Neura service

From Dev

App crashes when trying to save to SQLite database

From Dev

Trying to understand why this crashes when switch changes

From Dev

Excel vba userform crashes excel when opened

Related Related

  1. 1

    Program crashes when trying to print a double (but other types are ok)

  2. 2

    C++ Program crashes when trying to print data from Double Linked List

  3. 3

    GDB crashes when trying to attach

  4. 4

    ArgumentOutOfRangeException when trying to clear combobox

  5. 5

    VB Excel - When opening a word doc from VB with Excel I get a 'Variable not defined' error when trying to use ActiveDocument

  6. 6

    VB Excel - When opening a word doc from VB with Excel I get a 'Variable not defined' error when trying to use ActiveDocument

  7. 7

    Run script on all sheets excel

  8. 8

    File created in Open XML SDK 2.5 crashes excel when selecting print

  9. 9

    Print Multiple Excel sheets in a specific order

  10. 10

    Print Multiple Excel sheets in a specific order

  11. 11

    Excel macro to print sheets based on multiple values

  12. 12

    Excel VBA script crashes when calling a DDL function written in C, which returns a string BSTR

  13. 13

    Strange result when trying to print

  14. 14

    App crashes when trying to implement multilingual support

  15. 15

    AngularJs crashes when trying to access data in directive

  16. 16

    App crashes when trying to show AlertDialog in thread

  17. 17

    Meteor app crashes when trying to populate collections

  18. 18

    App crashes when trying to fullscreen activity

  19. 19

    App crashes when trying to record audio

  20. 20

    Application crashes when it trying to display Toast

  21. 21

    Program crashes when trying to read numbers with scanf

  22. 22

    Bootstrap carousel crashes when trying to skip index

  23. 23

    App crashes when trying to open a fragment with listview

  24. 24

    App crashes when trying to start a new activity

  25. 25

    Program crashes when trying to retrieve contents of pointer

  26. 26

    App crashes when trying to connect to Neura service

  27. 27

    App crashes when trying to save to SQLite database

  28. 28

    Trying to understand why this crashes when switch changes

  29. 29

    Excel vba userform crashes excel when opened

HotTag

Archive