Here is my problem:
I have a workbook that has 20+ worksheets all with 9 buttons on it. The buttons are used to navigate around the workbook. This workbook loads VEEEERRRY slow. With all the buttons removed it loads lickity split!
Since at the moment I need to keep the buttons I have tried to use the following code to remove the buttons on the Worksheet_Deactivate event:
Code
Private Sub Worksheet_Deactivate()
On Error Resume Next
With Me
.Unprotect
.cmdDashboard.Cut
.cmdDesking.Cut
.cmdDashboardNew.Cut
.cmdDashboardUsed.Cut
.cmdDashboardFinance.Cut
.cmdDashboardSales.Cut
.cmdDashboardInternet.Cut
.cmdDashboardService.Cut
.cmdDashboardParts.Cut
.cmdReports.Cut
.cmdAdmin.Cut
.Protect
End With
End Sub
Display More
Then on the activated worksheet the following code puts the buttons back:
Which calls this code from a module:
Code
Sub CreateTopButtons()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
On Error Resume Next
With ActiveSheet
.Unprotect
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=85.5, Top:=39, Width:=56.25, Height:=18.75)
.Name = "cmdDashboard"
With .Object
.Caption = "Dashboard"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(139, 0, 0)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=142.5, Top:=39, Width:=55.5, Height:=18.75)
.Name = "cmdDesking"
With .Object
.Caption = "Desking"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(0, 0, 128)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=198.75, Top:=39, Width:=56.25, Height:=18.75)
.Name = "cmdDashboardNew"
With .Object
.Caption = "Dash-New"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(139, 126, 102)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=255.75, Top:=39, Width:=55.5, Height:=18.75)
.Name = "cmdDashboardUsed"
With .Object
.Caption = "Dash-Used"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(0, 100, 0)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=313.5, Top:=39, Width:=56.25, Height:=18.75)
.Name = "cmdDashboardFinance"
With .Object
.Caption = "Dash-F&I"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(255, 193, 37)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=370.5, Top:=39, Width:=55.5, Height:=18.75)
.Name = "cmdDashboardSales"
With .Object
.Caption = "Dash-Sales"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(204, 51, 51)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=426.75, Top:=39, Width:=56.25, Height:=18.75)
.Name = "cmdDashboardInternet"
With .Object
.Caption = "Dash-Inet"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(70, 130, 180)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=483.75, Top:=39, Width:=55.5, Height:=18.75)
.Name = "cmdDashboardService"
With .Object
.Caption = "Dash-Service"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(128, 128, 105)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=541.5, Top:=39, Width:=56.25, Height:=18.75)
.Name = "cmdDashboardParts"
With .Object
.Caption = "Dash-Parts"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(46, 139, 87)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=598.5, Top:=39, Width:=55.5, Height:=18.75)
.Name = "cmdReports"
With .Object
.Caption = "Reports"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(255, 165, 0)
End With
End With
With .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=654.75, Top:=39, Width:=56.25, Height:=18.75)
.Name = "cmdAdmin"
With .Object
.Caption = "Admin"
.ForeColor = vbWhite
.Font.Bold = True
.Font.Name = "Calibri"
.FontSize = 10
.BackColor = RGB(205, 92, 92)
End With
End With
.Protect
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Display More
The problem is this. It crashes. I cannot figure it out as it does not Debug, it crashes Excel randomly. Can anyone give me some ideas about why?
Thanks