Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

grunwaldtx · Jun 16th 2012

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:

Code

Private Sub Worksheet_Activate()
    call CreateTopButtons
End Sub

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

vwankerl · Jun 17th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Here is some code I use to do something very much like you are asking about:

Code

Sub MakeButtons()
    Dim objButton As Object, objListBox As Object, rngTemp As Range, TopLeft As Range, lRowCnt As Long
    Set TopLeft = Range("B6")
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left, TopLeft.Top, 72, 30)
    With objButton
        .Caption = "Import Visa"
        .OnAction = "ImportVisaRecs"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left, TopLeft.Top + 36, 72, 30)
    With objButton
        .Caption = "Import User Forms"
        .OnAction = "ProcessReturns"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left, TopLeft.Top + 72, 72, 30)
    With objButton
        .Caption = "Query Database"
        .OnAction = "QueryProcess"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left, TopLeft.Top + 108, 72, 30)
    With objButton
        .Caption = "Recalc Dashboard"
        .OnAction = "CalculateDashboard"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left + 90, TopLeft.Top, 72, 30)
    With objButton
        .Caption = "Save File"
        .OnAction = "RemoveQuery"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left + 90, TopLeft.Top + 36, 72, 30)
    With objButton
        .Caption = "Print Account Summary"
        .OnAction = "PrintAcctSummary"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left + 90, TopLeft.Top + 72, 72, 30)
    With objButton
        .Caption = "Close Application"
        .OnAction = "RemoveQuery"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left + 90, TopLeft.Top + 108, 72, 30)
    With objButton
        .Caption = "Resend User Forms"
        .OnAction = "ResendUserForm"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left + 90, TopLeft.Top + 144, 72, 30)
    With objButton
        .Caption = "Resend Group of User Forms"
        .OnAction = "ResendUserFormGroup"
    End With
    Set objButton = gwksDashboardSht.Buttons.Add(TopLeft.Left + 90, TopLeft.Top + 180, 72, 30)
    With objButton
        .Caption = "Remove User Forms"
        .OnAction = "RemoveUserForm"
    End With
    gwksDashboardSht.Buttons.Visible = True
End Sub
Sub RemoveButtons()
    Dim obtnButton As Object
    With gwksDashboardSht
        If .Buttons.Count > 0 Then
            For Each obtnButton In .Buttons
                obtnButton.Delete
            Next obtnButton
        End If
    End With
End Sub

Display More

gwksDashboardSht is a global variable pointing to one of the sheets in my workbook. It could be Activesheet or any other worksheet object.

Note that these buttons are the Forms buttons not Activex buttons.

This code works in Excel 2003 and Excel 2007. I haven't tested it with Excel 2010 but have no reason to believe it won't work there also.

I call the RemoveButtons routine from Workbook_Open and Workbook_BeforeClose procedures. I call MakeButtons routine from the Workbook_Open event procedure. I call the RemoveButtons from both because I have found there are times when the workbook got saved but the BeforeClose event didn't fire. And buttons seem to accumulate if you are careful about adding and removing.

If you have any questions please ask. The unasked question never gets answered

Smallman · Jun 17th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Hi Grunwaldtx

You actually have 11 buttons not 9 as stated. Anywho, I used the code you provided to come up with something that relevant to your problem. Have a look at the table I set up on Sheet 2. Setting up a matrix will reduce the coding required and increase the speed of your code. Anyways my stab so far.

Take care

Smallaman

Code

Option Explicit
Private Sub Worksheet_Activate()
Dim var As Variant
Dim ar As Variant
Dim arr As Variant
Dim i As Integer


var = Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value
arr = Sheet2.Range("B2", Sheet2.Range("D" & Rows.Count).End(xlUp)).Value
ar = Sheet2.Range("E2", Sheet2.Range("E" & Rows.Count).End(xlUp)).Value


    For i = 1 To 11
        With ActiveSheet
            With .OLEObjects.Add("Forms.CommandButton.1", , , Left:=ar(i, 1), Top:=39, Width:=56.25, Height:=18.75)
                .Name = var(i, 1)
                With .Object
                    .Caption = var(i, 1)
                    .ForeColor = vbWhite
                    .Font.Bold = True
                    .Font.Name = "Calibri"
                    .FontSize = 10
                    .BackColor = RGB(arr(i, 1), arr(i, 2), arr(i, 3))
                End With
            End With
        End With
    Next
End Sub
'Second part, get rid of created buttons.
Private Sub Worksheet_Deactivate()
Dim var As Variant
Dim i As Integer
var = Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value
    For i = 1 To 11
        Sheet3.Shapes.Range(Array(var(i, 1))).Delete
    Next i
End Sub

Display More

grunwaldtx · Jun 19th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

[ATTACH=CONFIG]46540[/ATTACH]

vwankerl,

That seems to work pretty well. the only problem I see is this.

When loading a worksheet and then loading the buttons the button that is clisked to goto thate particular worksheet seems to get "frozen" on the sheet. and aslo can crash the program.

Take a look at the example I have uploaded.

**royUK** · Jun 19th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Why would you want to do this?

Better to find out the cause of the problem

grunwaldtx · Jun 19th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

I am open to any ideas you may have. I was under the assumption this was the best way to do it. I am utilizing the spreadsheet for up to 10 users right now and they are not at all computer savvy. So I need the navigation of the sheets to be seamless and intuitive. I would love to hear a better way?

vwankerl · Jun 20th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

grunwaldtx,

I have done quite a bit of work managing the User Interface for various Excel applications. I understand the need to do so with multiple users who are either computer illiterate or overly self-confident (to their own detriment). I will look at the code you have sent and see if I can help or give you some direction.

vwankerl

**royUK** · Jun 20th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

If the butons are used to navigate around the workbook why use VBA code? Use hyperlinks attached to shapes

grunwaldtx · Jun 20th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Quote from royUK;614176

If the butons are used to navigate around the workbook why use VBA code? Use hyperlinks attached to shapes

I am open to Almost anything. But let me ask this. Would that save space? And load time? My biggest problem is that it takes so long to load the Workbook, and that seems to be because of the buttons that are on all 30+ sheets. My initial idea was to delete the buttons on Worksheet_Deactivate and load them on Worksheet_Activate. But this has proved to be awfully buggy. Would shapes conserve space and quicken the loading of the workbook?

grunwaldtx · Jun 20th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Quote from vwankerl;614155

grunwaldtx,

I have done quite a bit of work managing the User Interface for various Excel applications. I understand the need to do so with multiple users who are either computer illiterate or overly self-confident (to their own detriment). I will look at the code you have sent and see if I can help or give you some direction.

vwankerl

I would be happy to send you the entire workbook for you to peruse. The workbook I uploaded was not the correct one with the login system. My initial problem still persists. I have 30+ worksheets with 11 buttons+ on each sheet to navigate around the workbook, and the load time is excessive. Looking for a way to decrease load time significantly and maintain the ease of use for the layman user.

If you would like to see the workbook, reply back with an email address and I'll send it.

**royUK** · Jun 20th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Do you have a large number of calculations in the worksheets, especially array formulas? If you want to attach the complete workbook you might be able to use a zipped file or a file sharing site

grunwaldtx · Jun 20th 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Quote from royUK;614296

Do you have a large number of calculations in the worksheets, especially array formulas? If you want to attach the complete workbook you might be able to use a zipped file or a file sharing site

I do have a large number formula. There are a "medium" number of array formula, but I have tried to mainly make them dynamically copy into the worksheets when the worksheet is activated and deleted when the worksheet becomes deactive.
Click this http://www.ge.tt/#!/6ntsrPJ/v/0 to download the current workbook.

Again, one problem with size and load time seems to be the navigation buttons. I have tried to delete them on worksheet deactivate and load them on activate, but it seems to cause crashes. the cose for that is in the module mButtons.

Let me know what you think.

PS Username is: tgrunwald
Password is : shitload

sorry about the bad word

**royUK** · Jun 21st 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

I would say the major problem is the number of formulas & arrayformulas.

Why don't you manually set up the workbook - display tabs, etc and not have it in the code?

This line won't work & debugs as soon as the workbook opens.

Code

.Caption = "Terry Grunwald's T-Max for Automobile Dealerships: " & ActiveWorkbook.CodeName

You can remove all the buttons replace with shapes that hyperlink to the sheets/named ranges etc. That would reduce your code.

Try running Rob Bovey's Code Cleaner

grunwaldtx · Jun 21st 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Quote from royUK;614394
I would say the major problem is the number of formulas & arrayformulas.

Why don't you manually set up the workbook - display tabs, etc and not have it in the code?

This line won't work & debugs as soon as the workbook opens.
Code
.Caption = "Terry Grunwald's T-Max for Automobile Dealerships: " & ActiveWorkbook.CodeName
You can remove all the buttons replace with shapes that hyperlink to the sheets/named ranges etc. That would reduce your code.

Try running Rob Bovey's Code Cleaner
Display More

Thank You for the help Roy,

It's funny you mentioned replacing the buttos with shaapes. I just got done doing that, and it reduced the size of the file by 50%, and decreased load time significantly. What exactly do you mean by manually setting up the workbook - display tabs, etc?

And why?

Regards,

Terry

**royUK** · Jun 22nd 2012

Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Your code in the workbook open event sets up the workbook if I remember right, like hiding sheet tabs, etc. This can be done manually & wouldn't need doing by code

Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

Participate now!

Share