1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quick Access Toolbar Conundrum

Discussion in 'Ask an Excel Question' started by Eloise T, Jan 10, 2019.

  1. Eloise T

    Eloise T Active Member

    Messages:
    851
    I use 4 custom items on my Quick Access Toolbar:

    upload_2019-1-10_12-11-33.png

    The "problem" I'm having is with #4 (SetBackground2White).

    Even though I have installed it on about 4 different Excel files, it seems to want to default to one of the Excel files that is opened and not in use, or isn't opened all!

    I have tried opening each of the 4 files (workbooks), one at a time and deleting
    the "SetBackground2White," reinitializing it, closing and saving the file, then repeating the process for each Excel file. It still wants to default to another file that is not opened. (It works, but is annoying to have to wait for another file to open to engage the macro.)

    Any ideas how I can force it to default to the file that I'm using at the moment?
    Having gone through all this made me realize that if I simply not use the exact same macro name in each Excel file, that would solve the problem, but I can't let it drop and would like to know if this is an Excel bug or not.




    The process I'm using to create the "SetBackground2White" Quick Access tool at the Home tab:

    upload_2019-1-10_12-11-33.png

    Then right click over previous location of "white square":

    upload_2019-1-10_12-10-41.png

    Next select the down arrow to the right of Popular Commands and choose Macros.
    Next select: "Sheet1.Setbackground2White," then [Add >>] Then [Modify...]
    upload_2019-1-10_12-18-14.png



    and Finally I select the White box and then [OK].
    upload_2019-1-10_12-21-7.png

    The following is the SetBackground2White macro:

    Code (vb):

    Sub SetBackground2White()
      Selection.Interior.ColorIndex = 2
    End Sub
     
    Last edited: Jan 10, 2019
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,692
    I would have all the macro's in your personal.xlsb file
    Store that in the Xlstart directory

    To find that use the following
    1. Press [Alt]+[F11] to launch the VBE.
    2. If the Immediate window isn't visible, press [Ctrl]+g.
    3. In the Immediate window, type ? application.StartupPath and press Enter. VBA will display the path to XLStart.
    Read more at: https://bettersolutions.com/excel/macros/personal-xlsb.htm
    Thomas Kuriakose likes this.
  3. AlanSidman

    AlanSidman Active Member

    Messages:
    421
    Thomas Kuriakose likes this.
  4. Eloise T

    Eloise T Active Member

    Messages:
    851
  5. Eloise T

    Eloise T Active Member

    Messages:
    851
    How do I unhide my PERSONAL.XLSB file to make sure it is correct?
    I clicked on the file name in:
    C:\Users\Owner\AppData\Roaming\Microsoft\Excel\XLSTART
    and clicked on Properties and then unchecked hide, but that didn't unhide it.
  6. AlanSidman

    AlanSidman Active Member

    Messages:
    421
    With excel open, right click on any tab name and select unhide. Personal.xlsb should be one of your selections available. Personally, I never have my personal.xlsb hidden, but that is a personal preference as many others prefer to have it hidden.
  7. Eloise T

    Eloise T Active Member

    Messages:
    851

Share This Page