Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Porting a spreadsheet to another machine (Excel 2000)-MU

    I'm wanting to place a copy of an Excel 2000 workbook on another machine. If I copy only the .xls, I lose my custom toolbar even though the macros and VBA still function correctly from the menus. What else do I need to copy across and in what location does it need to appear on the other machine?

    I'm also wondering if this 2000 .xls will run OK on Office 97.

    thank you

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 2000)-MU

    Have you tried attaching the custom toolbar to the .xls: Tools/Customise/Toolbars/Attach? HTH
    Gre

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 2000)-MU

    I hadn't tried that, but when I do it appears that my custom toolbar is already attached under "Toolbars in workbook".

    Alan

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 2000)-MU

    That works for me (even when I forgotten that the toolbar still exists). <img src=/S/grin.gif border=0 alt=grin width=15 height=15> On the "ported" setup, have you looked under Tools/Customise/Toolbars to see whether the toolbar is still there, but simply not checked? HTH
    Gre

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 2000)-MU

    You may have something there. The person with the copy says...

    Sorry. C'est un problem.
    I did that, and whilst the Custom1 toolbar box is ticked, there ain't no Custom1 Toolbar visible.

    I'll pass on you instructions and see what transpires.

    Thanks unkamunka.

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 2000)-MU

    To ensure the user gets your latest toolbar, make sure you add code to your workbook that deletes the toolbar from the system when the book is closed:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Commandbars("YourBar").Delete
    End Sub
    Then in the Workbook OPen event, make sure it gets visible:

    Private Sub Workbook_Open()
    Commandbars("YourBar").Visible=True
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 20

    I have tried the check suggested by unkamunka and the toolbar seems to be "attached" and visible/selectable on dropdown lists etc. All macros function properly, and the other user is able to recreate a similar toolbar manually.

    The problem remains though. His copy can not get the toolbar to display.

    Unfortunately Jan, your code segment:

    Private Sub Workbook_Open()
    Commandbars("YourBar").Visible=True
    End Sub

    generates an Error 91 on opening - I can't figure want the problem is from the error diagnostics.

    Any more ideas still appreciated [img]/forums/images/smilies/smile.gif[/img]

    Alan

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 20

    At the users system, try renaming his .xlb file (change extension) and then start XL and test things again. Maybe his toolbar customization file is somehow corrupted.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Excel 20

    Thanks Jan. I have passed that advice on. Failing that, I have seen a reference to the possibility of creating a custom toolbar programatically with VBA. I am chasing the code to do this, again with little luck :-(

    Do you know if this is possible, and where I might source the code?

    thanks again

    Alan

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Exce

    In case you're having a bit of trouble tracking down the code here is some (slightly different in approach from Jan Karel) that you might find useful in getting yourself started:

    Private Sub Auto_Open()
    Call AddBar
    End Sub

    Private Sub Auto_Close()
    On Error Resume Next
    CommandBars("Custom").Delete
    End Sub

    Private Sub AddBar()
    StatusBar = "Setting up Custom Toolbar"
    Dim cBar As CommandBar
    Set cBar = CommandBars.Add("Custom", msoBarTop) 'This puts the Toolbar above the workbook.
    With cBar
    .Controls.Add(Type:=1, ID:=108).Caption = "&Format Painter"
    .Controls.Add(Type:=10).Caption = "Paste &Special" 'The & enables a hotkey
    .Controls.Add(Type:=1, ID:=458).Caption = "Auto&Filter"
    .Visible = True
    End With
    Set subControl = cBar.Controls(2)
    With subControl
    .Controls.Add(Type:=1, ID:=370).Caption = "&Values" 'default Paste Special Values command
    .Controls.Add(Type:=1, ID:=755).Caption = "&Options" 'default Paste Special... command
    .Controls.Add(Type:=1, ID:=2787).Caption = "&Hyperlink" 'default Paste as Hyperlink command
    .Controls(3).FaceId = 1015 'Alter Default "No Icon" property.
    End With
    End Sub

    I am not clear what type of Custom Bar you are using. If the commands are Icon-based, they are Type 1 and if they are Menu headers they are Type 10. Please note that Microsoft recommend using msoControlButton for 1 and msoControlPopup for 10 - as Type numbers may change with Excel versions. Using numbers does improve performance. (These Type numbers did not change between Excel 97 and Excel 2000.)

    I have assumed your Toolbar is entirely custom commands. Post back if you want to set up using some of the default Excel commands through the built in Command IDs. HTH
    Gre

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Exce-MULTI

    Thanks unkamunka.

    I'll certainly try your code out, as well as keep it for learning/reference. The latest feedback I have is tending towards suggesting a problem with the client's setup/installation. He claims to be unable to locate any .xlb files on his disk, and is unable to "save" a custom toolbar that he creates on his own machine.

    The symptoms are the same - his toolbar name appears on all the lists in various places, but he is unable to get it to display. Perhaps he is missing some component in his installation? In the end, his machine is just one of several, so it may not be significant in the more general sense. It still represents a problem though, since he is the "first line" of evaluation.

    thanks for your help with this.

    Alan

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Exce-MULTI

    Has your colleague selected "Show hidden files" in his Windows folder options? If he has not, then it is quite likely that his *.xlb file is sitting in a hidden folder. This means that, whether or not any of the files in that folder are hidden, they will still not show up on a search on his machine. If he has an out of the box version of Excel, he should have an *.xlb file somewhere.

    As for showing the toolbar, try this adapted version of Jan Karel's code:

    Private Sub Display()
    Commandbars("[whatever your toolbar's name is]").Visible=True
    End Sub

    HTH
    Gre

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Exce-MULTI

    I just located excel.xlb on my machine, and it is not hidden, nor is it in a hidden folder structure. But who knows what's up (or down) on his particular install ;-)

    I have noted all the possible solutions here, but the next step will be to try things out on yet another machine, to try to zero in on the source of this strange situation. The fact that it doesn't appear on any of the bugs/troubleshooting searches I've done on the WWW is pointing more & more to a specific problem with his particular setup.

    I thank you and Jan for all of the effort you have put in to try to help me solve this. At the very least, I've digested yet another useful dose of VBA coding :-)

    thanks again

    Alan

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Exce-MULTI

    The latest development in the saga is that the same behavior has now been observed on two other machines, one 97 and one 2000.

    I am now wondering whether having the VBA/macros protected is preventing something from creating the toolbar. It was noted that the user could not save his own custom toolbar that he created on the sheet - even though references to it appeared, he could not get it to show after he saved and reopened the sheet.

    It's just a custom toolbar with text + picture "buttons" attached to the macros. Running the macros manually works fine.

    I've yet to try and create the toolbar via VB code, but this may be my last resort.

    getting desperate now [img]/forums/images/smilies/sad.gif[/img]

    Alan

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Porting a spreadsheet to another machine (Exce-MULTI

    could you post the offending workbook here?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •