Moving Toolbars and Macros to another worksheet

[mogoBob]

Hi everyone...

It's great to see such a good site...


steve

This is NOT an ADD for my project nor is it spam its just a very good thing... i know its a bit long winded Blah Blah

i thought others might find the following useful... and i hope you enjoy the twisted use of the left right and mid string functions.

Warning if you are creating toolbars and or buttons and don't want to lose them i suggest you seek out your exel11.xlb and copy it to a safe place or as can happen when you get and application level error you can lose all your changes also note that if you open excel quickly after closing it you can also lose your toolbar customizations. Note: That your changes are saved into a global file not into the spreadsheet itself so any changes you make will also appear in any other spreadsheet but they may not work as the macros attached to the buttons might not be available. Often just to confuse everyone clicking a button in a new spread sheet will or may open another sheet in which the macros actually reside.

I had written a series of macro's to automatically create a 12month invoicing database (1 sheet for each month)... with default formula and formatting. The database will be used by subcontractors to invoice clients.

The Settings Toolbar

-- removed inline image ---


i achieve this by having a month template sheet that is copied after the last sheet then edited (in code) to suit the month. this is done in a For Each loop.

Once the year is created by the code the toolbar that controls months (which also contains a create invoice button) appears and you are then able to create invoices.. Invoices are also automatically created using the same template sheet process of copying the sheet and renaming it ... this is driven by a settings sheet which keeps track of the invoice numbers (incrementing each time an invoice is created)'

Both the year and any or all invoices can be exported (printed) from a custom print dialog that adjusts itself according to if a Month is shown or an invoice is the activesheet.

Invoice creation is achieved by entering the data into a custom form thusly no actual data entry is possible on the sheets themselves maintaining transparent security and data integrity by protecting and unprotecting sheets on the fly or as needed.

To locate an invoice all you do is click an an invoice number on the month sheet to load that invoice as the active sheet.

so as can be seen it is quite a good project..

Soooo i was trying to find a way to transfer my macros and toolbars to another system without using the personal spreadsheet or creating code to export the macros then import them on another machine so i came up with the following procedure. after getting some clues as to how to proceed from the web.

Since my project already has a procedure to change the toolbar buttons and make toolbars visible/invisible on the fly i simply re-assign the macros to the new file location within the macro looping on the toolbars controls.count... i.e

 For i = 1 To .Controls.Count '- 1
'get the position of the ! in the OnAction setting
  instrExclam = InStr(.Controls(i).OnAction, "!")
'reconstruct the Onaction setting and reset it to the current workbook
  .Controls(i).OnAction =  ActiveWorkbook.FullName & "!" & Right(.Controls(i).OnAction,  Len(.Controls(i).OnAction) - instrExclam)

So below you will see that it first loops on the toolbars themselves when it finds one of mine it then loops on it's controls.count, fnishs that loop and grabs another toolber to loop on.

Someone may find this useful

This is the entire toolbar button_enabler macro code

  Dim Cbar As CommandBar, strSheetID As String, i As Integer, instrExclam As Integer
    For Each Cbar In Application.CommandBars
        On Error Resume Next[INDENT]        
'
[/INDENT][INDENT] 'StrsheetName = Right(Mid(StrsheetName, 2, 11), 4)
[/INDENT]'If InStr(StrsheetName, "Thru") <> 0 Then 
      
       With Cbar 'find the custom control button and disable it
            'Set the toolbar to act upon
            Select Case .Name
            Case "System Manager"
                    'Which Sheet has sent the call to the procedure
                    Select Case StrsheetName
                    'The Sheet is the settings sheet
                    Case "Settings"

                                    '"System Manager Toolbar"
                                        For i = 1 To .Controls.Count '- 1
                                        instrExclam = InStr(.Controls(i).OnAction, "!")
                                        .Controls(i).OnAction = ActiveWorkbook.FullName & "!" & Right(.Controls(i).OnAction, Len(.Controls(i).OnAction) - instrExclam)
                                           
                                           Select Case i
                                                Case 1, 2, 4, 5, 6, 7, 9
                                                .Controls(i).Enabled = True
                                                .Controls(i).Visible = True
                                                Case Else
                                                .Controls(i).Enabled = False
                                                .Controls(i).Visible = False
                                            End Select
                                         Next i
'                                   Case "Months Exist"
'                                            '"System Manager Toolbar settings if Months exist"
'                                          For i = 1 To .Controls.Count - 1
'                                             Select Case i
'                                                Case 1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 12
'                                                .Controls(i).Enabled = True
'                                                .Controls(i).Visible = True
'                                                Case Else
'                                                .Controls(i).Enabled = False
'                                                .Controls(i).Visible = False
'                                            End Select
'                                         Next i
                    Case "Nil_Months_Exist"
                        '"System Manager Toolbar settings if no Months exist"
                                        For i = 1 To .Controls.Count '- 1
                                         instrExclam = InStr(.Controls(i).OnAction, "!")
                                        .Controls(i).OnAction = ActiveWorkbook.FullName & "!" & Right(.Controls(i).OnAction, Len(.Controls(i).OnAction) - instrExclam)
   
                                            Select Case i
                                                Case 5 ', 2, 3, 6, 7, 8, 9, 10
                                                .Controls(i).Enabled = True
                                                .Controls(i).Visible = True
                                                Case Else
                                                .Controls(i).Enabled = False
                                                .Controls(i).Visible = False
                                            End Select
                                         Next i
                    Case "Thru"
                                
                                For i = 1 To .Controls.Count
                                instrExclam = InStr(.Controls(i).OnAction, "!")
                                        .Controls(i).OnAction = ActiveWorkbook.FullName & "!" & Right(.Controls(i).OnAction, Len(.Controls(i).OnAction) - instrExclam)

                                    Select Case i
                                        Case 3, 4, 6, 7, 8, 9, 10, 11, 12
                                        .Controls(i).Enabled = True
                                        .Controls(i).Visible = True
                                        Case Else
                                        .Controls(i).Enabled = False
                                        .Controls(i).Visible = False
                                    End Select
                                Next i
                    
                    Case "Proteus"
                    'MsgBox "Invoice"
                    End Select
            Case "Invoice Manager"
                        For i = 1 To .Controls.Count - 1
                        instrExclam = InStr(.Controls(i).OnAction, "!")
                         .Controls(i).OnAction = ActiveWorkbook.FullName & "!" & Right(.Controls(i).OnAction, Len(.Controls(i).OnAction) - instrExclam)

                            .Controls(i).Enabled = True
                            .Controls(i).Visible = True
                        Next i
            Case Else
                    For i = 1 To .Controls.Count - 1
                    instrExclam = InStr(.Controls(i).OnAction, "!")
                    .Controls(i).OnAction = ActiveWorkbook.FullName & "!" & Right(.Controls(i).OnAction, Len(.Controls(i).OnAction) - instrExclam)

                            .Controls(i).Enabled = True
                        Next i
            End Select
       End With
        On Error GoTo 0
    Next Cbar

1. To get the macro's onto the new system Save your sheet as an xla
2. Copy the xla to the target system.
3. open a new sheet Add the xla you created to your Addins using the Tools\Addin Menu.
4. copy your excel11.xlb to the folder you want on the target system..

if you want to write code to change or save the xlb on the fly while opening or closing your project that's up to you of course.
​

5. Have fun.

Incidentally because my project has template sheets in it and i copied the excel 11.xlb over it runs without any problems. You may however run into some references problems in the vba editor. However anyone who can understand the code above won't have a problem me thinks.

 

[mogoBob]

I have been testing since i posted and have found that some of the references to macro transfer and xla's in my last post are not exactly correct... i will correct them once i do some more testing..

 

[mogoBob]

Solved: Moving Toolbars and Macros to another Workbook

I have been testing since i posted and have found that some of the references to macro transfer and xla's in my last post are not exactly correct... i will correct them once i do some more testing..

Ok, for anyone interested i have cracked it.. **** M'Soft..

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

'This Routine resets Your Toolbar macro designation if you put your workbook
'in another folder or transfer or copy your Workbook to another system.

'''''''''''''''''
'''''WARNING'''''
'''''''''''''''''
'Make sure you find and backup your Excel toolbar settings file before you run this code
'as it may alter your existing xlb file. The location of the file differs with the windows
'and/or Excel version so to find it set the Search Settings to search for hidden and
'system files. You might also want to look at the Backup_Xlb_File routine used to backup my own since it uses
'Environ("AppData") & "\Microsoft\Excel\Excel11.xlb" to locate the file.
it's (posted at the end of this post)

'For Excel 2003 and earlier read the following articles
'http://support.microsoft.com/kb/291074

'Excel Forum Link

'For Excel 2007 and above see this Website 1st
'http://support.microsoft.com/kb/926805

'Having said all that: here’s the code ..

  Public Sub Reset_Macros_To_Current_File()
      
    Dim Cbar As CommandBar, i As Integer, instrExclam As String
      For Each Cbar In Application.CommandBars
          On Error Resume Next
      ' strSheetID As String,
         With Cbar 'find the custom control button and disable it
              'Set the toolbar to act upon
              Select Case .Name
              'Change or add your toolbars to the Case below
              Case "System Manager", "Invoice Manager"
                  'Set all the custom Toolbars to the correct
                  'macros in the current workbook"
                      For i = 1 To .Controls.Count
                      'Get the postion of the Exlamation mark in the on action statement
                      instrExclam = InStr(.Controls(i).OnAction, "!")
                      'The following Sets the button's on action to the current workbook macro
                      .Controls(i).OnAction = ActiveWorkbook.FullName & "!" _
                      & Right(.Controls(i).OnAction, Len(.Controls(i).OnAction) - instrExclam)
                      Next i
                      
                      'Warning!! Don't add the code that changes the button macro below or you
                      'will mess up your excel installation because it will alter the inbuilt
                      'toolbar buttons OnAction designation.
                      
                  Case "Some Other Case"
  ''                    For i = 1 To .Controls.Count - 1
                              'Do soemthing else
  ''                            .Controls(i).Enabled = True
  ''                    Next i
              End Select
         End With
          On Error GoTo 0
      Next Cbar
  End Sub

'On Windows 7 here's a routine i use to backup my own xlb. The backup is given a name with the time and date so we can roll back to a previous version if needed.( It may be due to Excel re-writing/updatiing the XLB) but for some weird reason it doesn’t always work. So don’t trust it. Personally I backup the XLB manually every time I exit Excel when I am coding the toolbars. i've also taken to running the routine when the workbook is opened.

So here’s the backup routine

  Public Sub Backup_Xlb_File()
  On Error GoTo err_TB_Backup
  Dim SourceFile, DestinationFile
  'my excel version is 2003 so to use the code for a later version
  'you may need to tweak the code a bit you cAN USE application.Version
  'to get the excel version.
  SourceFile = Environ("AppData") & "\Microsoft\Excel\Excel11.xlb"    ' Define source file name.
  'Check that the file actually exists
  If Len(Dir(SourceFile)) <> 0 Then
  DestinationFile = ActiveWorkbook.Path & "\" & Format(Now(), "hh_ss_dd_mm_yyyy") & " Excel11.xlb"   ' Define target file name.
  FileCopy SourceFile, DestinationFile    ' Copy source to target.
  Else
  MsgBox SourceFile & " was not found in the expected location.", vbInformation, "The Excel Toolbar File Was Not Found"
  End If
  Exit_TB:
  Exit Sub
  err_TB_Backup:
  MsgBox Err.Description
  Resume Exit_TB
  End Sub

This procedure could be extended by adding code to save and re-instate the original xlb file when the worksheet is closed. Just an idea…………