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.

Macro window conundrum

Discussion in 'VBA Macros' started by Eloise T, Jul 21, 2017.

  1. Eloise T

    Eloise T Active Member

    Messages:
    641
    I have not been able to figure out why my Macro window sometimes looks like this:

    upload_2017-7-21_9-29-45.png

    which does not allow me to run the ChangeCase macro, and other times it looks like this:

    upload_2017-7-21_9-58-21.png

    which allows me to run the ChangeCase macro.

    What is causing the inability to run the ChangeCase macro...at times?
    Last edited: Jul 21, 2017
  2. Belleke

    Belleke Member

    Messages:
    139
    Hi,
    It looks you have another excel workbook open at the same time.
    (without seeing the code or an example it is a guess)
    But try:
    If you're using windows as OS, use an ActiveX control to trigger the macro and see if you're problem is solved.
  3. Eloise T

    Eloise T Active Member

    Messages:
    641
    The file name is: Master Technicians TABBED invoice.xlsm
    ...and no other file is opened.
    I am using Windows 7. I do not understand what you meant by: "use an ActiveX control to trigger the macro"
  4. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Eloise T!

    At the immediate pane window (from Excel, Alt-F11, Ctrl-G, Immediate) type this and press enter:
    ?workbooks.Count

    It will display below that line the number of opened workbooks.

    Despite of this, sometimes Excel keeps the VBA project of recently closed workbooks.

    Regards!
  5. Belleke

    Belleke Member

    Messages:
    139
    @SirJB7
    Your English is a lot better then mine:oops:.
    (I am working with a Dutch version of Excel)
  6. Eloise T

    Eloise T Active Member

    Messages:
    641
    I put "?workbooks.Count" and it responded back with 1


    The macro behaved itself and allowed me to run it.
    Is that a permanent fix or will I be required to put "?workbooks.Count" each time that happens?

    THANK YOU again.
  7. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Belleke!
    Spanish version here.
    Regards!
  8. Eloise T

    Eloise T Active Member

    Messages:
    641
    Are you sure you are not using an Irish version? :DD
  9. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Eloise T!

    The printing (? symbol) was just to elucidate how many workbooks you actually had opened. It's nothing required for running any macro. Another way of knowing how many workbooks opened do you have (more technically speaking, with or without VBA code) is to read the left pane Project - VBA Project of the Visual Basic Editor (Alt-F11). There you'll probably find more workbooks than what you opened manually as Excel opens other *.xl?? files depending on the components configuration that is set for your installation. You won't be able to open them, don't care about it, it's for your own security; you can check there which other user workbook code modules are still loaded. If any of them doesn't want to go away, simply close and reopen Excel, that should do the job.

    Regards!
    Chirag R Raval likes this.
  10. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Eloise T!
    More sure than I can't learn Gaelic... yet! :eek:
    Regards!
  11. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Belleke!
    Regarding your signature "When the Last Tree Is Cut Down, the Last Fish Eaten, and the Last Stream Poisoned, You Will Realize That You Cannot Eat Money."... I was wondering... since there're still uncut trees, fishes in lakes and oceans, potable natural water sources... if you're willing to, you can refill my bank account and I promise to try to eat some 500 € notes. And by some I mean a lot! If I fail in the intent, I promise not to deliver them back to you. :rolleyes:
    Regards!

    Hi, Eloise T!
    I apologize for this little hijack of your thread, it won't happen again.
    Regards!
    PS: Should I have added "until next time"?
    Chirag R Raval and Bomino like this.
  12. Belleke

    Belleke Member

    Messages:
    139
    Hi SirJB7
    How do they look like, I never saw one:(
  13. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Belleke!
    Excel in German but you in Siberia?
    And they look like this.
    Regards!

    Attached Files:

  14. Eloise T

    Eloise T Active Member

    Messages:
    641
    Apparently the ?workbooks.Count "bumped" Excel in the correct rib to get it to allow the macro to work. Closing and reopening Excel didn't work for me.

    I greatly appreciate your help with VBA, etc. I know just enough about VBA to be dangerous to myself and everyone around me. I can't get my employer to send me to "Excel school" for a week or so to get me up to speed with the rest of the world; nor will he allow me the time off even if I were to pay for it myself. I'm certain that whatever I can learn in an "Excel school" will only serve to show me how little I really know even after the class.

    BTW, all this recent VBA stuff stemmed from a computer crash. I had to take my Excel backups and cart them to a computer with Excel 2016. I was using Excel 2007 prior to the crash. When I started using Excel 2016, the dates took on a different format other than "mm/dd/yyyy"

    When I tried to highlight Column G with the dates and use the date format
    *3/14/2012 which I had successfully been using with Excel 2007, it no longer worked with Excel 2016 as it did with Excel 2007...and there was no other format like "mm/dd/yyyy" except for creating a Custom format. I thought VBA might be the way to go as the date problem wasn't going away and I have more than 20 tabs with thousands of lines of data in each.

    Once again, your help was greatly appreciated.

    P.S. Yes....until next time. :)
  15. Eloise T

    Eloise T Active Member

    Messages:
    641
    "When the Last Tree Is Cut Down, the Last Fish Eaten, and the Last Stream Poisoned, You Will Realize That" ... the earth is no more and it has been replaced with a new earth. Even if man decides to use nuclear weaponry, he will kill himself off before he has destroyed the last tree, fish, or polluted the last stream. God created a self-sustaining universe that will continue on, despite what man may do.
    Chirag R Raval likes this.
  16. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Eloise T!

    I absolutely agree with this:
    But believe me that in your case danger comes tied with a bit of common sense, so it could be worse.

    Now this is weird:
    Moving from an Excel version to another one doesn't produce those changes. Excel store date & time as numbers, integer part for date and decimal part for time. Try putting =NOW() in a cell, let say A1, in B1 put =A1, now explicitly format A1 as date -any format- and B1 as General.
    I said explicitly because Excel uses different default format for dates, depending on how they're entered in cells. If you enter it in the form without the year, it assumes actual year and it formats as mmm-dd or dd-mmm, depending on your Windows options at Regional Settings and Number Configuration. But if you enter it specifying the year, it formats as dd/mm/yyyy or mm/dd/yyyy, depending on the same criteria.
    Briefly, you were previously using a machine with a date regional config settings of mm/dd/yyyy and you moved on to a new machine with a different one. You can check this at Start, Control Panel, Clock Language and Region, Regional Configuration and Language, Change Date Time Number Format.

    Regards!
    Chirag R Raval likes this.
  17. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Eloise T!
    I totally agree! That's why I'd like to relieve Belleke now from wondering what to do with those dirty notes in that moment! ;)
    Regards!
  18. Eloise T

    Eloise T Active Member

    Messages:
    641
    And once again, you were right on the money.
    The "new" computer was set for m/d/yyyy
  19. Eloise T

    Eloise T Active Member

    Messages:
    641
    upload_2017-7-21_13-41-35.png

    A1= *3/14/2012 B1=General
  20. Eloise T

    Eloise T Active Member

    Messages:
    641
    For some reason, using "?workbooks.Count" doesn't work every time. I'm still experimenting...
    ...yet another conundrum. There must be a simple explanation.
    I'll let you know what I find when I come up for air. :)
  21. Eloise T

    Eloise T Active Member

    Messages:
    641
    Just FYI, on my version of Windows 7, it was: Control Panel, Region and Language, Formats tab, Short Date.
  22. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Eloise T!

    If you change that to mm/dd/yyyy you shouldn't need my code. But, I always recommend to explicitly format date and number columns, to avoid this issues. If in your computer looks fine, you don't know how will it look when you send the file to another person.
    Displaying no. of opened workbooks doesn't make any kind of magic, it was just for testing how many opened workbooks you had at that very moment, since I tend to not believe in users.
    Thanks, I had no machine with English OS at a hand so tried to guess from my Spanish LA one.

    Regards!
    Chirag R Raval likes this.
  23. Monty

    Monty Well-Known Member

    Messages:
    836
    Eloise

    Firstly there is no need for you to run from that dialog box...Simply add a button and assign macro to it.
    Every time you can just click on that to perform it job...Hope you know this.



    Chirag R Raval likes this.
  24. Eloise T

    Eloise T Active Member

    Messages:
    641
    I knew that but it had slipped my mind. My conundrum now is figuring out where the best place put the button. :)
  25. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Eloise T!
    I'm struggling to refrain suggesting my top-10 best place list... :mad::confused::eek::oops:
    Regards!

Share This Page