+ Reply to Thread
Results 1 to 5 of 5

VBA is not working after first button click

Hybrid View

  1. #1
    Registered User
    Join Date
    08-01-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    5

    VBA is not working after first button click

    So my problem is that everything works perfectly fine even when i click the buttons, but the second time i want to click them it gives me an error and the whole excel document is almost like in debug mode even after i clicked stop.
    I am able to type on other excel sheets however no cursor appears nor the green box around cells when you click them.
    This code copies either one or all of the sheets from workbook, opens a new workbook and pastes the sheet there all while being protected. Again it has work in the past but now it only works the first time i click it and none after that and i cant seem to figure out why.

    the error on the second try is "method copy of object range failed". But if it worked the fist time why not the second??
    thank you

    EDIT: also when you exit out of excel after i get this error it says "excel stopped working and is checking for a solution"

    Sub copy_first_sheet()
    Sheet20.Unprotect ""
    Dim newbook As String
    Dim GetBook As String
    GetBook = ActiveWorkbook.Name
    
    Call CopyWorksheetValues
    Call Clear_Buttons
    
    Call DeleteallCharts
    ActiveSheet.Range("a59:i180").Clear
    ActiveSheet.Range("67:68").Locked = True
    Rows("67:180").EntireRow.Hidden = True
    ActiveWindow.DisplayGridlines = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
     End With
    
    newbook = ActiveWorkbook.Name
    ActiveSheet.Protect ""
    Workbooks(GetBook).Activate
    Sheet20.Protect ""
    Workbooks(newbook).Activate
    
    
    End Sub
    
    
    Sub copy_all_sheet()
    Sheet20.Unprotect ""
    Dim newbook As String
    Dim GetBook As String
    GetBook = ActiveWorkbook.Name
    
    Call CopyWorksheetValues
    Call Clear_Buttons
    
    ActiveSheet.Range("67:68").Locked = True
    
    ActiveWindow.DisplayGridlines = False
    
    
    newbook = ActiveWorkbook.Name
    ActiveSheet.Protect ""
    Workbooks(GetBook).Activate
    Sheet20.Protect ""
    Workbooks(newbook).Activate
    End Sub
    
    
    
    Sub Clear_Buttons()
    Dim i As Integer
            ActiveSheet.Buttons.delete
    End Sub
    Sub CopyWorksheetValues()
        ThisWorkbook.Worksheets("comparison").Copy
    
        Cells.Copy
        Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End Sub
    Sub DeleteallCharts()
    Dim chtObj As ChartObject
    For Each chtObj In ActiveSheet.ChartObjects
        chtObj.delete
    Next
    
    Dim pic As Picture
        For Each pic In ActiveSheet.Pictures
            If pic.Name <> "" Then
                pic.delete
            End If
        Next pic
    End Sub

  2. #2
    Valued Forum Contributor meabrams's Avatar
    Join Date
    10-03-2014
    Location
    USA
    MS-Off Ver
    2007, 2010, 2013, 2016
    Posts
    451

    Re: VBA is not working after first button click

    not having any issues running the code on my temp workbook that I created not sure in its just to much information that it is trying to copy over you can try adding.
    Application.ScreenUpdating = False
    To the beginning of your main macros and at the end of them add
    Application.ScreenUpdating = True
    If this doesn’t help then is it possible for you to upload a sample file so we can better help you with this issue

  3. #3
    Registered User
    Join Date
    08-01-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    5

    Re: VBA is not working after first button click

    Thank you for your reply. However that did not work. I am going to try and replicate the error on a different document and post it here

  4. #4
    Registered User
    Join Date
    08-01-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    5

    Re: VBA is not working after first button click

    Ok i think i uploaded an attachment below. Please let me know if it does not show up because i am new to this and still getting the hang of it.

    But as you can see the button to copy the sheets works the first time but when the new document is closed out and you try to perform the same action on the original document again i get an error?

    Thank you for looking at it!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-01-2016
    Location
    US
    MS-Off Ver
    2013
    Posts
    5

    Re: VBA is not working after first button click

    UPDATE: even if i delete all the code of my posted excel document and only leave the following code i still receive the same error.
    Sub test()
    ThisWorkbook.Worksheets("Comparison").Copy
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. "Click and Drag" mouse scroll button not working
    By techgrl64 in forum Excel General
    Replies: 4
    Last Post: 02-25-2016, 02:59 PM
  2. Running the code in an option button from a seperate normal button click
    By camikhazi in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-22-2016, 10:54 AM
  3. Button Click on Spreadsheet commanded by Button Click on Userform
    By Quilie19 in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 04-07-2015, 11:35 PM
  4. [SOLVED] Right click button not working in excel
    By longlong625@gmail.com in forum Excel General
    Replies: 3
    Last Post: 02-26-2015, 11:59 AM
  5. [SOLVED] click on a color button and after button click it will color entire row of cell you click
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-11-2014, 06:06 PM
  6. [SOLVED] Code working when using F8, but doesnt work properly through button click.
    By emilyloz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-01-2013, 09:58 AM
  7. [SOLVED] Create floating button based on button click in menu
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-12-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1