Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

[blafarm]

I've done a bunch of research, and I'm not sure a solution exists for this challenge -- but I would be grateful for any input from the forum.

I have a sheet that displays financial news that is accessed from a 3rd party data vendor using Excel WEBSERVICE calls.

The sheet works fine, but the user has to periodically update the WEBSERVICE calls in order to get the latest news from the vendor.

The data supplier does support SSE Streaming and REST calls, which would be great, but I don't know if that is even possible in Excel (using VBA or other?).

Anyway, at this moment in time, my only solution to allow a user to update the news is with an Excel Checkbox that enables/disables the WEBSERVICE call formulas.

When the user unchecks, and then quickly rechecks the Excel Checkbox, the WEBSERVICE calls (news stories) are all updated. Admittedly, it's a low-tech solution -- but it works.

I realize that support for SSE Streaming or REST calls might not be available in Excel.

However, I was hoping to learn how I might be able to automate the uncheck-recheck process at a set interval -- so that the user is not forced to do it manually.

If I could automatically change the value of a specific cell from its existing state of TRUE -- to FALSE -- and then immediately back to TRUE again -- that would be great.

And, of course, if I could control how often that state-change happened (e.g.: every 10-minutes, or 30-minutes, or 1-hour) -- my problem would be solved.

I would be very grateful for any suggestions the forum might have.

Thanks in advance for taking a look.

 

[BlueAure]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

Hi blafarm,

Have you used the Application.OnTime method to schedule a macro yet? Here is a link explaining the usage:
https://wellsr.com/vba/2019/excel/vba-application-ontime-to-schedule-macros/

You can use this to trigger a macro that either updates your webservice calls itself (probably possible) OR it could simply check/uncheck your checkbox automatically. The first is more elegant but the second would also work.

 

[blafarm]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

Hi blafarm,

Have you used the Application.OnTime method to schedule a macro yet? Here is a link explaining the usage:
https://wellsr.com/vba/2019/excel/vba-application-ontime-to-schedule-macros/

You can use this to trigger a macro that either updates your webservice calls itself (probably possible) OR it could simply check/uncheck your checkbox automatically. The first is more elegant but the second would also work.

Thank you BlueAure. I'll dig into this to see if I can make it work.

By any chance, do you have any idea how I might be able to automate the "AutoFilter>Reapply" menu bar function when a cell value changes from FALSE to TRUE?

 

[BlueAure]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

Have you tried this?

Sub ReapplyFilter()
    if ActiveSheet.Range("A1").Value = "TRUE" Then
        ActiveSheet.AutoFilter.ApplyFilter
    End Ff
End Sub

I found that online so because I've never had to do that before. Hopefully it works for you.

 

[blafarm]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

Have you tried this?

Sub ReapplyFilter()
    if ActiveSheet.Range("A1").Value = "TRUE" Then
        ActiveSheet.AutoFilter.ApplyFilter
    End Ff
End Sub

I found that online so because I've never had to do that before. Hopefully it works for you.

Thanks very much for that.

Unfortunately, it is not replicating the functionality of using the toolbar "Sort & Filter > Reapply" pulldown menu option.

I can't seem to find how to call that in VBA.

 

[BlueAure]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

I just used the Macro Recorder and it gave me the exact same line that I found online. Are you sure that it is being applied to the correct sheet?

 

[blafarm]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

I just used the Macro Recorder and it gave me the exact same line that I found online. Are you sure that it is being applied to the correct sheet?

I see what you are saying. Thank you for testing that.

I've tested that code with both the "ActiveSheet" naming convention --as well as the Worksheet CodeName ("Sheet2")

So my modified code looks like this:

Sub ReapplyFilter()
    If Worksheets("Sheet2").Range("A6").Value = "TRUE" Then
        Worksheets("Sheet2").AutoFilter.ApplyFilter
    End Ff
End Sub

After a bit more experimentation, I am now of the belief that this is code is not working because I am trying to use it successively on the same range -- and I am not clearing the filter before doing so.

Somehow, using the "Reapply" pulldown menu option does not require clearing the previous filter range -- and it works perfectly against data that has changed.

However, when using the code, the sheet is not refreshed to expose all of the missing rows before the filter is reapplied -- so it can't display any more information that it was formerly displaying.

Does that make sense?

 

[blafarm]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

I should also mention that a Macro also does not work.


EDIT: I take that back, I have the Macro working now (not sure what changed).

Now I just have to understand why the code is not working.

 

[blafarm]

Re: How to Automate a Cell Value from TRUE to FALSE at a Set Interval of Time

Well, I found one problem.
Your original post included:

End Ff
End Sub

Rather than:

[COLOR=#333333]End If
[/COLOR]End Sub

But it's still not working.