A Button to Turn on/off Data Refresh

[PLAYBOY]

Hi Everyone,

This is my first post in this forum. I just became a member. But I feel like I`ll be a very active member from now on Now my question is...

Is there anyway to create a button (could have VBA) to turn on/off the data refresh?

I have about 30 data inquiries that refreshes every 5 minutes and it gets really annoying when I try to work on the workbook. So I just want to turn off the data refresh and be able to turn it back on again with a click of a button or something.

Can someone help please?

[alansidman]

What is the code that sets the refresh now.

[PLAYBOY]

There are lots of inquires that are being pulled from the same source. I just need a button to pause all data refreshing and be able to turn it back on again with the same button if possible

Apperently I can`t post links because I~m still new. But I can tell you that all sources are the api page of coinmarketcap. The extentions of the links are different to show the different data to be pasted on the tables. But the main domain is same for all sources

I don`t think it matters where the data is coming from anyways though. I just need a button to turn on/off the refreshing for data
I feel like this can be done by adding some VBA to a button but I`m not good at those things really

[alansidman]

https://support.office.com/en-us/art...e-b9c8ac058d35

[PLAYBOY]

I already know this. But I can`t turn off each of 30 queries everytime I need to work on the sheet and then turn them back on again one by one.

I need a quick way to just turn on/off. Like a single button with VBA in it to pause the refresh and then continue again.

[alansidman]

It looks like post #4 of this thread may do what you want.

https://www.mrexcel.com/forum/excel-...-data-vba.html

[PLAYBOY]

You`re right. This one seems like the right code but I don`t know where to put it. Can someone help me to create a button with this code please?

Please Login or Register  to view this content.

[dominicb]

Good morning PLAYBOY

Press Alt + F11, this will open your VBE
Go to Insert > Module, and copy your code into the window that opens
Close the VBE

Do you have your Developer tab visible (if not, go here)

Go to Developer > Insert and choose the first icon under "Form Controls" - this is your button
Draw your button where you want it, and when you've done it, an Assign Macro box will open up so you can choose which macro it will run.

HTH

DominicB

[PLAYBOY]

Hi dominicb

Good morning to you as well. It`s 4.30am here
Just created the button per your instructions above. But I`m not sure if it`s working right. I can`t tell if it`s on or off. Also the data refreshed after I clicked on it too. I was expecting it to turn it off with a click and turn it back on with the next click. Like an on/off switch. Is the script I posted above the right one for what I need do you think? Could you give me some advices?

[AliGW]

Playboy - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[PLAYBOY]

Sorry, my bad

[PLAYBOY]

Ok. I also just found this code. It may help to someone who can suggest me on how can I use code in the button I wrote above. I think this code just stops the data refresh if you click on it during the refresh. I`m not sure.

Please Login or Register  to view this content.

[PLAYBOY]

Is this issue very hard? Is there a particular reason I didn`t get much help on this? I was hopeful about this forum


Today I also found this code below

Please Login or Register  to view this content.

But this one didn`t really do what I need either. I think it just stops the refresh during the refresh when I click on it. I doesn`t really prevent the refresh from happening again until I click on it to turn it back on.

[AliGW]

What do you mean about not getting much help?

[PLAYBOY]

Well my issue remains after couple tries and I believe there are thousands of people here. I`m not saying people have to help me. Of course not. But It`s a little disappointing to see I got only 2 people tried to help me in the biggest excel forum in the world (I assume it is because when I google excel forum. You guys were on top and there`re milions of messages here)

I tried to help people who may attempt to help by giving them some codes but that didn`t do anything either. I don`t want to sounds like I`m lazy like I dont research before hand or just expect people to write a software for me. So I gave all I have to help. Maybe I was too hopeful about this forum. So I`m a little disappointed on amount of the help attempts

[AliGW]

OK - I see. Thanks for explaining.

I am sorry that you are disappointed. Of course, you understand the nature of the forums: you sign up and get the benefit of free help from unpaid volunteers who offer that help as and when they can in their free time. There is no guarantee, therefore, that any query posted will get a response at all, let alone a reposnse that solves the query. However, I can say this: in my experience, if there is an obvious and/or easy solution to your issue, you will get that solution within 24 hours of posting. If not, you may get a response from one or two who think they might be able to help you, and failing this, you may be asked to clarify your requirement. No response at all would indicate no interest in your issue or a complete lack of understanding of it on the part of those who help here. You do not fall into this category, fortunately.

As you say, nobody here is compelled to answer your queries, so I think you have done well to get two forum regulars interested in helping you.

[PLAYBOY]

536 currently online people out of more than a million members and I should feel lucky to have 2 people helped me? See, this is what I meant when I said disappointing.
I definitely appreciate that those people that helped me though. Don`t get me wrong.

I posted this on facebook groups also and get more people attempted to solve the issue. I think we`re looking at this from 2 very different perspectives.

I havent`t given up on this forum yet. I still keep my hope but just wanted to provide some honest (maybe even constructive) criticism

[AliGW]

Thank you very much for your comments.

[FDibbins]

Playboy, we always appreciate honest open, constructive feedback.

I too, am sorry you are disappointed with the response you have had so far, but as Ali pointed out, members here give of their time, freely, as and when they are able, and offer help to the limit of their abilities. The fact that we come out on top of most (all?) google searches is because we ARE a great forum, and help 100 000's of people all around the world. That doesnt mean that we always help everybody, with anything, at any time.

Some members are better in certain aspects of excel than in other areas of the program. Im not saying VBA is an exclusive domain, many members are proficient at it. But only a small number of those are experts. Perhaps those that could assist you further were either occupied helping others, or where simply off-line.

Another thing that often happens, is that, over time - with no responses, a thread will end up getting pushed further and further down the list until it disappears from the 1st page, and could, eventually, end up on page 6 or more. When that happens, people like those helping you, dont immediately see your post, and move on to help others.

1 way yo overcome that natural process, is to "bump" your thread each day, to keep it "fresh" in the list. Just post a reminder that you are still in need of help - Im sure that will help resolve the delay you had.

Thanks for the understanding
Admin