Tool bar not displayed in all WB's

[DanBlum]

I have recently moved from excel 2010 to 2013. I don't know if this is causing my problem or what the resolution is. I have a tool bar that I create programmatically with vba. The code that creates the tool bar is in my Personal wb. The toolbar is successfully created but now only shows in the personal wb. Before it showed in the other open wb's. (That are the essence of my excel application.). Please help. Is there some setting that defaults in 2013 differently than 2010?. Is there some parameter in the create tool bar vba sequence that defaults differently and needs to be specified? I tried to change trust center setting and got the tool bar in my application wb's once but in subsequent invocations since, it's gone. I don't know what action of mine actually got the tool bar in the apps wb's the one time it showed up! I can post the code from my personal wb if that would help. Assistance would be greatly appreciated. I am also newly on windows 8.1 and was on XP before.

 

[mole999]

my best guess is microsoft imposing security on programmes, so i would suspect there needs to be a way to legitimise your work so that other excel will accept it (signing the project)

 

[DanBlum]

Can you tell me more about signing the project, please

 

[mole999]

heres an official page Signing a VBA Project

 

[DanBlum]

Thank you. I have another theory that I can't try out until I return to the production machine that is not working properly. Here is my toolbar creation routine

Public Sub CreateToolBar()


Dim I As Integer
' Get rid of any toolbar named New Camp
On Error Resume Next
CommandBars("New Camp").Delete
On Error GoTo 0
'
Dim Holder As String
Dim PersonalFileObject As Workbook
Set PersonalFileObject = ThisWorkbook  'pppp
PersonalFileObject.Activate
With CommandBars.Add(Name:="New Camp", Temporary:=True)
    .Position = msoBarLeft
Dim viewstring As String




  I = 1 ' Used to index through thte table.


  While PersonalFileObject.ActiveSheet.Range("ButtonTable").Cells(I, 2) <> ""   'Loop till you find the blank entry
 
      With .Controls.Add(Type:=msoControlButton) '                                    Start the addition of the Ith button
        viewstring = PersonalFileObject.ActiveSheet.Range("ButtonTable").Cells(I, 2)
        .OnAction = PersonalFileObject.ActiveSheet.Range("ButtonTable").Cells(I, 2) 'Name of the macro to invoke.
        .FaceId = PersonalFileObject.ActiveSheet.Range("ButtonTable").Cells(I, 4)   'Put in faceid for the button
                             '  the faceid's can be found at http://www.outlookexchange.com/articles/toddwalker/BuiltInOLKIcons.asp
        .TooltipText = PersonalFileObject.ActiveSheet.Range("ButtonTable").Cells(I, 3)  ' puts in tool tip
        .BeginGroup = PersonalFileObject.ActiveSheet.Range("ButtonTable").Cells(I, 5)  'puts in separator
      End With
      
    I = I + 1  'Index I so that the code above looks at next line of the ButtonTable
   Wend    ' Loop back to create the next button ---


  
  .Visible = True    ' make the tool bar visible.
End With    'This ends the with that created the toolbar
    
End Sub

but after reading more, I have learned that it might also require:

   .Enabled = True

Is it possible that lack of this additional statement is causing the tool bar not to show in my application workbooks? What is annoying is that the problem seems to be intermittent. There are 3 workbooks that comprise the applications (supported by the Personal Workbook). I can't figure it out, but it seems that when I open them in a particular order, the tool bar magically appears. But, "seems" is the operative word because I can't reproduce what I am doing to occasionally get the tool bar to appear. Another clue is that once the tool bar properly appears in the application WB's, I can delete it and then create it again. But, if I close everything down and restart all of the excel WB's it doesn't appear. Again, this is a new installation of Windows 8.1. However, on my two development machines (one Windows 7 and the other Windows 8.1) I don't have the problem. (I can't try the production machines for a couple of days...so I can't try the ".enable=true" theory. )

 

[mole999]

i would have thought visible = true was the key

try and loose the on error resume next and see where it collapse you may need something like

if commandbars newcamp exists, then command bars new camp delete.

also in your workbook close statement, makes sure you force the command bar to close, maybe some close nicely, and other just shut excel

 

[DanBlum]

I finally got to the production machine. Adding the enable = true didn't solve the problem, but It seems to have made the problem solid instead of intermittent. Actually, it made a workaround solid so that I can get work done while trying to understand the issue better. Also, the work around may be a clue to the problem. (I also added an Auto_Close which deletes the tool bar, but that didn't seem to affect anything.)

The workaround (as normal) is to open one of the application workbooks. This obviously opens the Personal workbook too. (The New Camp tool bar appears with the Add-ins tab in the Personal workbook, but the problem is that it doesn't appear in the application workbook.) So what I do as a a workaround is close the application workbook (but not the Personal workbook), then reopen the application workbook. Upon re-opening the application workbook (while the Personal workbook has remained open), the tool bar appears in the application workbook!

Does this sequence suggest why the workbook is not appearing in the application workbook upon first opening?

More information: The auto_open subroutine (of the Personal WB) checks to see if certain other workbooks are open, and based on this, creates the tool bar (or doesn't). Earlier I had trouble with this because the "other workbooks" had not had a chance to open when Personal's auto_open executed. I solved this by putting a delay (now 2 secs) in the auto_open (Application.ontime). The ONTIME resumed in another Personal WB subroutine to do the processing in order to see what other workbooks were there. By that time the other workbooks were open (including the one that initiated the whole application), the code could correctly evaluate whether the tool bar was needed. So, could this delay be part of problem? This code is correctly creating (or not) the tool bar in the Personal WB...it's just that the tool bar doesn't show in the application WB's. This all worked ok under XP and Excel 2010. The problem has emerged under W 8.1 and Excel 2013.

I also tried eliminating the delay...that just reverted to the problem of the other workbooks not being opened before the Personal WB's auto-open subroutine checked on the other workbook's existence.

So, I remain stumped.