VBA: command button not working after resetting sheet

[Kraakelars]

Hello.

I am new to VBA and I'm currently working on a project with a workbook for various data analysis.
I paste data exported from another program into "Sheet 1", where I have a command button to sort and clean the data for further analysis.
In "Sheet 2" I have a number of buttons for various analysis.

It mostly works as planned, but I still have an annoying issue I can't seem to figure out; When I am finished with my analysis on one data-set and ready to move onto another set, I run a script that clears and hides the used sheets, and I paste a new data-set into "Sheet 1". After the data is pasted I am unable to press the button to sort the data. While clicking the button a duplicate button shows up underneath the original one, and the script is not ran.
If I enable developer mode and run the code from there or saves and reopen the workbook after pasting, the code executes flawlessly. Could there perhaps be a problem with the cache after pasting new data?

Thanks in advance.

 

[rlv01]

You should probably post the relevant code for your macro, perhaps the script that clears and hides the used sheets.

 

[Fluff]

If your Buttons are ActiveX, then I'd recommend changing to Forms Controls.

 

[Kraakelars]

Under is the code for the reset button.

Private Sub reset_all_Click()
ResetButton reset_all




If MsgBox("Er du sikker på at du vil resette hele arbeidsboken? Alle analysene nullstilles og du kan legge inn scope for nytt selskap.", vbYesNo, "Bekreft") = vbYes Then
    Dim testList() As Variant, test As Variant
    testList = Array("ADK", "Other", "Payroll", "Ferie")
    For Each test In testList
        ThisWorkbook.Sheets(test).Cells.Clear
        ThisWorkbook.Sheets(test).Visible = xlSheetHidden
        
        ThisWorkbook.Sheets("SB_" & test).Cells.Clear
        ThisWorkbook.Sheets("SB_" & test).Visible = xlSheetHidden
    Next test
    
    ThisWorkbook.Sheets("SB").Cells.Clear
    ThisWorkbook.Sheets("SB").Visible = xlSheetHidden
    ThisWorkbook.Sheets("Oversikt").Visible = xlSheetHidden
        
    ThisWorkbook.Sheets("Innledende analyse").Cells.Clear
    ThisWorkbook.Sheets("Innledende analyse").Visible = xlSheetHidden
    
    ThisWorkbook.Sheets("Scoping view").Cells.Clear
    ThisWorkbook.Sheets("Scoping view").Range("A1").Value = "Sett eksport her"
End If
End Sub

Where ResetButton is a script I found in a different forum post to reset the font and size of the button, as I had problems with it when connecting/disconnecting an external monitor to my computer.

Public Sub ResetButton(ByRef btn As Object)
' Purpose:      Reset button size and font size for form command button on worksheet
'               Addresses known Excel bug(s) which alters button size and/or apparent font size
' Parameters:   Reference to button object
' Remarks:      Getting/setting font size fails since font size remains the same; display (apparent) size changes
'               AutoSize maximizes the font size to fit the current button size in case it has changed
'               Button size is reset in case it has changed
'               Finally, font size is reset to adjust for font changes applied by AutoSize
'               This fix seems to handle shrinking button icon sizes as well
Dim h As Integer    'command button height
Dim w As Integer    '               width
Dim fs As Integer   '               font size
    With btn
        h = .Height             
        w = .Width
        fs = .Font.Size
        .AutoSize = True     
        .AutoSize = False
        .Height = h             
        .Width = w
        .Font.Size = fs
    End With
End Sub

 

[Kraakelars]

That seems to have fixed the problem. Thanks a lot!