Excel freezes upon ribbon load with customization and accompanying macro for ribbon dropdown box execution (Unless VBA editor is open)

[aviaf]

Hi Everyone,

I have a file at work that has been created by someone else and has some VBA coding behind it as well as some ribbon customization. The ribbon customization and the changes I've made were done through the Custom UI Editor program to prevent having to convert to zip, extract, etc. As far as I can tell,that was working fine even after I added some extra functionality (extra buttons on a custom tab).

The trouble came when I added a dropdown box to the ribbon custom tab and then wrote in corresponding code. I would say I'm a beginner with VBA but have a knack for solving my own stuff out, however the dropdown box was a challenge given differing instructions on different sites. Once I finished the code and debugged it a bit, it was running very well. Then I realized that upon opening the file, it would freeze excel when the ribbon loaded. After some trial and error, I realized that this only happens when the VBA editor has not been opened at least once since the Excel application was opened.

What I know so far:
I've done some research and understand that when this happens it can be an error in the coding. I've been trying to isolate the problem but can't seem to figure out what's wrong. By including debug.prints in my code, I know that the macros for the dropdown are only run when the custom tab is clicked on (usually the workbook starts on the home tab), and because the workbook freezes as soon as the ribbon loads, (as in before most of my new macros are run) I think that it might be an error with my XML coding, but I am not sure.

I have MS Office 2010 on Windows 7.

I have included all of the modules below as well as the custom XML coding for the ribbon. My additions to the file are in red text and existing code is in black. (all of module 4 is my new stuff)

A few notes before you look at the modules:
1) The book opens and presents the use with a prompt advising them of certain things
2) The book then adjusts multiple views and updates specific pivots, and also changes many viewing settings
3) Because it loads it into full screen, the workbook works well at first. As soon as you un-fullscreen it, and show the ribbon (I'm assuming this is when the ribbon loads), then it freezes excel and I can't click on anything.

XML Coding:

&ltcustomUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"&gt
&ltribbon startFromScratch="false"&gt
&lttabs&gt
&lttab id="customTab" label="Navigate Genzyme Sales Reporting" insertBeforeMso="TabHome"&gt
&ltgroup id="customGroup1" label="View"&gt
&ltbutton id="customButton1" label="Historical Data" image="History" size="large" onAction="Callback1" /&gt
&ltbutton id="customButton2" label="Patient Numbers" image="Patients" size="large" onAction="Callback2" /&gt
&ltbutton id="customButton3" label="Ytd/Qtd View" image="YtdView" size="large" onAction="Callback3" /&gt
&ltbutton id="customButton4" label="Quarter View" image="QtrView" size="large" onAction="Callback4" /&gt
&ltbutton id="customButton5" label="Qtr Stepup" image="QtrStepup" size="large" onAction="Callback5" /&gt
&ltbutton id="customButton6" label="Revenue Graph" image="RevenueGraph" size="large" onAction="Callback6" /&gt
&ltbutton id="customButton7" label="Calendar View" image="CalendarView" size="large" onAction="Callback7" /&gt
&ltbutton id="customButton8" label="Customer Transactions" image="CustomerView" size="large" onAction="Callback8" /&gt
&ltbutton id="customButton9" label="Future Menu" imageMso="FileCompactAndRepairDatabase" size="large" onAction="Callback9" /&gt
&ltbutton id="customButton10" label="News Flash" imageMso="WatchWindow" size="large" onAction="Callback10" /&gt


[COLOR=#ff0000]&ltdropDown id="DD1" label="Exchange Rate"[/COLOR]
[COLOR=#ff0000]                      getItemCount="ItemCount"[/COLOR]
[COLOR=#ff0000]                      getItemLabel="ItemLabel"[/COLOR]
[COLOR=#ff0000]                      getItemID="SelectIndex"[/COLOR]
[COLOR=#ff0000]            getSelectedItemIndex="GetSelectedItemIndex"[/COLOR]
[COLOR=#ff0000]                      onAction="CallbackList" /&gt[/COLOR]
&lt/group&gt
&lt/tab&gt
&lt/tabs&gt
&lt/ribbon&gt
&lt/customUI&gt

ThisWorkbook VBA:

Private Sub workbook_open()


            Range("username").Value = Application.UserName
            MsgBox ("Hello " & Range("usernamefirst") & " text here")
            Application.DisplayFullScreen = True
            Application.ScreenUpdating = False
            Application.DisplayFormulaBar = False
            
[COLOR=#ff0000]           Call ArrayS1[/COLOR]
[COLOR=#ff0000]            ActiveWindow.DisplayWorkbookTabs = True  'this is just so i can navigate the file well again[/COLOR]

            Application.Calculation = xlAutomatic
            Application.Goto Reference:="DataSheet"
            Application.Goto Reference:="CurrMth"
            Range("BW18").Select
            Application.Goto Reference:="CurrMth"
            Selection.Copy
            Application.Goto Reference:="ExpMth"
            ActiveSheet.Paste
            Selection.Copy
            Application.Goto Reference:="EXPmth2"
            ActiveSheet.Paste
            Application.Goto Reference:="DataSheet"
            Range("n1").Select
            Application.Goto Reference:="QtrView"
            Range("C8").Select
            Sheets("Revenue Phasing").Select
            Range("A1:m27").Select
            ActiveWindow.Zoom = True
            Range("A1").Select
            Sheets("Patient").Select
            Range("A1:O42").Select
            ActiveWindow.Zoom = True
            Range("A1").Select
            Sheets("Revenue Phasing").Select
            Range("A1:m27").Select
            ActiveWindow.Zoom = True
            Range("A1").Select
            Sheets("Historical").Select
            Range("A1:P38").Select
            ActiveWindow.Zoom = True
            Range("A1").Select
            Sheets("Calendar").Select
            Range("A1:ba49").Select
            ActiveWindow.Zoom = True
            Sheets("Stepup").Select
            Range("A1:Z41").Select
            ActiveWindow.Zoom = True
            Range("a:z").Select
            Range("a:z").EntireColumn.AutoFit
            Range("A1").Select
            Sheets("Ytd View").Select
            Range("A1:al38").Select
            ActiveWindow.Zoom = True
            Columns("C:AL").Select
            Range("C3").Activate
            Columns("C:AL").EntireColumn.AutoFit
            Range("C9").Select
            ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
            Sheets("Qtr View").Select
            Range("A1:ak35").Select
            ActiveWindow.Zoom = True
            Range("J:AA,AC:AC,AG:AJ").Select
            Range("J:AA,AC:AC,AG:AJ").EntireColumn.AutoFit
            Range("A1").Select
            Dim wnd As Window
            For Each wnd In Application.Windows
            wnd.Caption = ""
            Next
            Application.Caption = "RevenView - Genzyme Canada Interactive Sales Reporting Dashboard              " & Range("usernamefirst") & " " & Range("usernamelast") & "... " & "navigate through reports using dark red cells \ Double click [ here ] to reveal Excel menu                                                                      "
            Application.ScreenUpdating = True

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
            Application.CommandBars("Standard").Visible = True
            Application.CommandBars("Formatting").Visible = True
            Application.CommandBars("Standard").Visible = True
            Application.DisplayFullScreen = False
            Application.Caption = ""
            ThisWorkbook.Saved = True
            ActiveWorkbook.Close SaveChanges:=False
            Application.ActiveWindow.Close SaveChanges:=False
End Sub

Module 1:

'Callback for customButton1 onAction
Sub Callback1(control As IRibbonControl)
    Sheets("Historical").Select
    Range("H6").Select
End Sub

'Callback for customButton2 onAction
Sub Callback2(control As IRibbonControl)
    Sheets("Patient").Select
    Range("D5").Select
End Sub

'Callback for customButton3 onAction
Sub Callback3(control As IRibbonControl)
    Sheets("Ytd View").Select
    Range("C9").Select
End Sub

'Callback for customButton4 onAction
Sub Callback4(control As IRibbonControl)
    Sheets("Qtr View").Select
    Range("J8").Select
End Sub

'Callback for customButton5 onAction
Sub Callback5(control As IRibbonControl)
    Sheets("StepUp").Select
    Range("C7").Select
End Sub

'Callback for customButton6 onAction
Sub Callback6(control As IRibbonControl)
    Sheets("Revenue Phasing").Select
    Range("K24").Select
End Sub

'Callback for customButton7 onAction
Sub Callback7(control As IRibbonControl)
    Sheets("Calendar").Select
    Range("AD26").Select
End Sub

'Callback for customButton8 onAction
Sub Callback8(control As IRibbonControl)
    Workbooks.Open Filename:= _
        "\\camis1-wpzfs001\COMMON\Genzyme\Sales Reporting\Invoiced sales by Customer.xlsx", UpdateLinks:=0
End Sub

'Callback for customButton9 onAction
Sub Callback9(control As IRibbonControl)
MsgBox "Future Menu"
End Sub

'Callback for customButton10 onAction
Sub Callback10(control As IRibbonControl)
MsgBox "News Flash"
End Sub

Module 2:

Sub Macro1()'
' Macro1 Macro
'
    Range("S33").Select
    Application.Goto Reference:="CurrMth"
    Range("BW18").Select
    Application.Goto Reference:="CurrMth"
    Selection.Copy
    Application.Goto Reference:="ExpMth"
    ActiveSheet.Paste
    Selection.Copy
    Application.Goto Reference:="EXPmth2"
    ActiveSheet.Paste
    Application.Goto Reference:="DataSheet"
End Sub

Module 3:

Sub Macro2()'
' Macro2 Macro
'    Application.Calculation = xlAutomatic
End Sub

Module 4: (All of this module is new)

Public vRngValues
Public iItemcount As Integer
Option Explicit

Sub GetSelectedItemIndex(control As IRibbonControl, ByRef itemID As Variant)
    itemID = 0
End Sub

'Sub for setting exchange rates to array

Sub ArrayS1()
Dim x As Integer
iItemcount = Range("ExchangeRates").Cells.count
ReDim vRngValues(iItemcount)
For x = 0 To iItemcount
    vRngValues(x) = Range("ExchangeRates").Cells(1 + x, 1).Value
    Next
    Debug.Print x
End Sub

Sub ItemCount(control As IRibbonControl, ByRef returnedVal)
'Use the global variable collected in the Workbook_Open()
'assigning it to the returnedVal parameter
    returnedVal = iItemcount
    Debug.Print returnedVal
End Sub

Sub SelectIndex(control As IRibbonControl, index As Integer, ByRef ID)
'Set the ID parameter in the callback signature to each 'index'
'in the signature. We are adding cmbBox text to make it unique
'as using just a number may clash with other controls/items
'indexes in controls are zero based
    ID = "cmbBox" & index
    Debug.Print ID
End Sub

Sub ItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
'Sets the returnVal parameter, which carries the text back to the control
'This fires for each control 'item', so the 'index' parameter
'provides a zero based index for the current item, which we use in the code
    returnedVal = vRngValues(index)
    Debug.Print returnedVal
End Sub

'Callback for Combobox1 onChange

Sub CallbackList(control As IRibbonControl, ID As String, index As Integer)
'Set the 'text' parameter to a cell value
Dim Val1
Dim Val2
Dim selectedID
Dim mCurrentItemID

Sheets("Exchange").Cells(2, 2).Value = Sheets("Exchange").Cells(index + 3, 2).Value
Sheets("Qtr View").Cells(6, 2).Value = Sheets("Exchange").Cells(index + 3, 1).Value

mCurrentItemID = selectedID
Debug.Print selectedID
End Sub

I know this is a lot of code, let me know how else I can provide better information on my problem. Due to the contents of the file, I can't include it itself, so hopefully the coding helps.

Thanks for any help in advance, I've been trying to figure this out for a day. I know my coding isn't the best, so if you see blatant areas where I could make my additions more efficient, feel free to point it out in addition to my main problem.

 

[aviaf]

bump

 

[aviaf]

bump

 

[aviaf]

Just an FYI, I've cross-posted this to stackoverflow just now: xml - Excel freezes upon ribbon load with customization and accompanying macro for ribbon dropdown box execution (Unless VBA editor is open) - Stack Overflow

 

[aviaf]

bump

 

[RoryA]

Your XML appears to be fine (assuming you put it in a CustomUI14 part).

It's extremely hard to test without your workbook due to all the named ranges, images and other relationships that are not provided, but loading the XML and the 4 modules, the workbook opens without any problem with the appropriate tab. I did not test with the Workbook_Open code.

One thing I note with your new module 4 - do not put anything before the Option Explicit statement. It can lead to weird behaviour.

 

[aviaf]

Ok i'll give that a try and rearrange some of the coding and let you guys know. If I privately sent you the workbook, would you be able to take a look at it?

 

[RoryA]

I can't make any guarantees but I've sent you an email address by PM.