VBA: command button not working after resetting sheet

Kraakelars

New Member
Joined
Feb 5, 2019
Messages
3
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You should probably post the relevant code for your macro, perhaps the script that clears and hides the used sheets.
 
Upvote 0
If your Buttons are ActiveX, then I'd recommend changing to Forms Controls.
 
Upvote 0
Under is the code for the reset button.

Code:
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top