• 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...

  • 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

Eloise T

Eloise T

Active Member
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:
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.
 
Belleke said:
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.
Click to expand...
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"
 
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!
 
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.
 
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!
 
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"?
 
Hi, Belleke!
Excel in German but you in Siberia?
And they look like this.
Regards!
 

Attachments

  • billetes-500-grandes--620x349.jpg
    billetes-500-grandes--620x349.jpg
    114.2 KB · Views: 3
SirJB7 said:
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!
Click to expand...
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. :)
 
Belleke said:
Hi SirJB7

How do they look like, I never saw one:(
Click to expand...
"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.
 
Hi, Eloise T!

I absolutely agree with this:
Eloise T said:
I know just enough about VBA to be dangerous to myself and everyone around me.
Click to expand...
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:
Eloise T said:
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"
Click to expand...
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!
 
Eloise T said:
"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.
Click to expand...
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!
 
SirJB7 said:
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!
Click to expand...
And once again, you were right on the money.
The "new" computer was set for m/d/yyyy
 
SirJB7 said:
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!
Click to expand...
upload_2017-7-21_13-41-35.png

A1= *3/14/2012 B1=General
 
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. :)
 
SirJB7 said:
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!
Click to expand...
Just FYI, on my version of Windows 7, it was: Control Panel, Region and Language, Formats tab, Short Date.
 
Hi, Eloise T!

Eloise T said:
And once again, you were right on the money.
The "new" computer was set for m/d/yyyy
Click to expand...
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.
Eloise T said:
For some reason, using "?workbooks.Count" doesn't work every time. I'm still experimenting...
Click to expand...
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.
Eloise T said:
Just FYI, on my version of Windows 7, it was: Control Panel, Region and Language, Formats tab, Short Date.
Click to expand...
Thanks, I had no machine with English OS at a hand so tried to guess from my Spanish LA one.

Regards!
 
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.



Eloise T said:
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.
Click to expand...
 
I knew that but it had slipped my mind. My conundrum now is figuring out where the best place put the button. :)
 
You must log in or register to reply here.
Back
Top