Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Oct 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Customise XLXP's Web Toollbar? (XP SP-1)

    Hi,

    The screenshot shows how you can set the web toolbar's Start Page.
    First problem is when I click mine, it goes to a blank page.

    My Q. is: I want to change it's behaviour so when I click it, Excel jumps to a particular sheet in the Excel file.
    How can I do that?

    TIA.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    Your question doesn't make sense. The Web toolbar relates to Internet. The "Home" button takes you to your web home page; if your internet home page is a blank page, that is what you get. The "Home" button does not dot do anything in Excel.

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

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    Would this be one particular file? Or would you like to have it jump to a certain sheet in whichever file is open at the moment?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Oct 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    Sorry for duplicate post.

    Yes I am clearer on this now.

    I am trying to jump to a particular sheet on the CURRENT XLS file.

    Regards.

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

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    You could put this little macro in your personal.xls file:

    Sub GotoSheet()
    On error resume next
    Application.Goto Activeworkbook.Worksheets("SheetToJumpTo").Cells(1 ,1)
    End Sub

    Now insert a button on a toolbar and assign this macro to it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    You can easily insert a hyperlink in any cell in any worksheet to any cell in any worksheet.

    But I don't understand why you would want to set the start page of your browser to a worksheet. Each time you open your browser, it would go to this worksheet. You must be aware that the "Home" button on your web toolbar is not specific to Excel.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    Hi,

    Well, there's a small problem which has a bog impact!

    If you rename the Excel file, the macro doesn't work & produces the error you can see in the screenshot.

    We have to rename our files quite a bit 'cause we use that for version control, i.e. v1, v2, v3...

    What can I do?!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    Possible solutions:
    - Put the macro in your Personal.xls.
    - Create the custom toolbar button in code when the workbook is opened, and destroy it when the workbook is closed.
    - Create a custom toolbar, attach it to the workbook, and destroy it when the workbook is closed. It will be displayed automatically when the workbook is opened again.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    What is the that macro you are using?

    Jan's macro does not use the filename: It uses the activeworkbook (no matter what the name).
    It would, however, give an error if the sheet did not exist.

    Steve

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

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    <!profile=pieterse>pieterse<!/profile> said

    > You could put this little macro in your personal.xls file:

    This refers to a special file which makes macros available to all the Excel files you use. It sounds like you may have put the code into the file with which you are working. You would get the kind error message you mention if you had done that.

    If you are not familiar with Personal.xls, please look below at an explanation - from an old Star Post by <!profile=LegareColeman>LegareColeman<!/profile> which I have copied out of back email copies.


    Personal.xls Tutorial

    Excel provides a special workbook called Personal.xls where you can put any macros and User Defined Functions that you want to be available for all of your workbooks to use. This workbook is hidden, so that you would not normally see it in the list of open workbooks when you click on the Window menu command. When you install Excel, this workbook is not created by the installation process, so this tutorial is to explain how to get Excel to create a Personal.xls for you, how to put your macros and User Defined Functions into it, and how to use those macros and User Defined Functions.

    Personal.xls must be created in the correct directory or it will not work. It also should have some special attributes like being hidden. The best way to create your Personal.xls is to get Excel to create it for you. You do this by recording a macro and telling Excel to put it into Personal.xls. When you do this, Excel will automatically create Personal.xls if it does not already exist, put it in the correct place, and give it all of the correct attributes. You can use the following procedure to create your Personal.xls workbook:

    1- Start Excel with a new empty workbook.
    2- Click on the Tools menu.
    3- Click on the Macro command so that the submenu flies out.
    4- In the submenu click on "Record New Macro.
    5- In the Record Macro dialog box click on the arrow in the "Store macro in" box.
    6- Select "Personal Macro Workbook" from the list.
    7- Change the Macro Name if you like, but this is not necessary.
    8- Click on OK. You should get a small toolbar that says "Stop Recording" in the title bar, and that has a button that looks like the Stop button on a VCR.
    9- Click on any cell in the worksheet to select it.
    10- Click on the Stop Recording button in the small toolbar. The toolbar should disappear.

    You should now have a Personal.xls file. To see what has been done, do the following:

    1- Press Alt+F11. This should open the Visual Basic Editor.
    2- On the left side of the screen should be the project explorer that should show the workbook you are working in (probably named "Book1") and your Personal.xls workbook. The Personal.xls workbook should have a Modules collection that contains a Module1 object. Double click on the Module1 object and the macro you just recorded should be in the right window. It should looks something like this:<pre>Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 2/19/2002 by Legare Coleman
    '

    '
    Range("B1").Select
    End Sub</pre>

    3- This is a pretty useless macro, and you can now select the code by clicking and dragging and then delete it.

    If you put macros into Personal.xls, then those macros will be in the list when you go to the Tools menu, select Macro and then select Macros from the submenu. Just select the macro and click the run button. You can also assign macros in Personal.xls to buttons on your command bars. I have the following macro in my Personal.xls file assigned to a button on my tool bar to put the current date and time into the current cell and then move one cell to the right.<pre>Sub InsertDateTime()
    ActiveCell.Value = Now()
    ActiveCell.Offset(0, 1).Select
    End Sub</pre>

    If you put a User Defined Function into your Personal.xls, you can use this function in any workbook. However, when you enter the function you must also tell Excel that it is located in Personal.xls. If you have a User Defined Function named MyFunction in your Personal.xls, you could use the following in a cell to execute this function:<pre>=PERSONAL.XLS!MyFunction()</pre>

    Gre

  11. #11
    2 Star Lounger
    Join Date
    Oct 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    Ok, I'm starting to get the hang of this:

    You see I need a foolproof solution which can even withstand renaming of Sheets.
    The VBA uses the parameter "Worksheets("Sheet1")" so if the user renames Sheet1 to something else (which is very likely) then the macro no longer works.

    Pls. can you recommend me a way that can also withstand file renames?

    Help!!!!!!!!!!!!!!!!! TIA.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    It depends on your intent and wishes how to solve.

    Here are some thoughts:

    1) ThisWorkbook always refers to worksheet with the running code. ActiveWorkbook is the active workbook
    2) ActiveSheet refers to the active sheet
    3) You can use Workbooks with an index number instead of name
    4) you can use sheets with an index number of even worksheets with an index number instead of name. This depends on order of sheets, but is not name dependent
    5) you could name a range on the sheet of interest and get the parent of the named range. This is independent of order or name, but if you change or delete the named range...
    6) protect the workbook to "prevent" changing names.

    What are you trying to accomplish?

    Steve

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

    Re: Customise XLXP's Web Toollbar? (XP SP-1)

    All worksheets also have a "codename" which is very unlikely to be changed by a user.

    One uses the codename directly:

    Application.Goto Workbooks("fillin.xls").worksheets(Sheet1.Name).Ra nge("A100")

    Dunno why this doesn't seem to work though:

    Application.Goto Workbooks("fillin.xls").Sheet1.Range("A100")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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