Help needed to force excel to show up & a few other doubts

I encountered some problems while doing my excel vba. Please give some enlightenment and guidance along.

Overview of what i am doing
I have a workbook with 3 worksheets. In each worksheet, there is a web query trying to get data from a webpage. The web query is at Cell A1 of each worksheet. The query is supposed to autorefresh every 5 minutes and i will have some code running through the cells to get a few values in each sheet.

The checking code (call check) will be called every 3minutes.

If the value is greater than a threshold then pop out a message box stating the value. When this happen, the message box needs to be focus/pop up in front of the screen no matter what the user is doing (checking microsoft outlook, browsing web using Internet Explorer or other activity).

Problem I have encountered:
1) I needed to password protect the workbook in case the user accidentally remove or change the cell A1 which the data is inserted into.

But i realised that the auto refresh for the web query will not work if the sheet is protected.

thus i use the following code> unprotect each sheet in the workbook then refresh the query table

Please Login or Register  to view this content.

Qn: Is there a better way to go about refreshing the web query in a protected sheet beside the code above?

How do we give a index to the querytable (i am not too sure as i imported the data using record macro method and i believe they didnt assign an index to the webquery). QueryTables(1) refering to querytable with index 1?


Another problem> Msgbox pop up:
After I run the call check macro, when the cells value exceeded a threshold, a msgbox should pop out in front of the screen. This is very important and crucial for the msgbox to grab the attention of the user.

As this excel program will be running for 12-20 hours each day, I needed the msgbox to grab the user attention every time the cell values exceeded threshold (currently i am running the check subroutine every 3 minutes which is still sufficent timing for the user to react or immediately which is better still).

I had use the following code in my program to cause the message box to pop up in front of the screen. But there is a few occurences that the msgbox did not pop up in front of the screen (the message box did trigger behind the excel but it did not force shown in front of the other application i am running like Internet explorer, microsoft outlook and so on ). The excel icon in the taskbar did not flash also.

Method 1 (a separate subroutine)

Please Login or Register  to view this content.

Method 2 (after the if statement that check the cell)

Please Login or Register  to view this content.

both Method 1 and 2 will sometimes miss out on the pop-ing of msgbox to grab user attention

Qn: I needed the message box/the workbook to be really shown/pop up in front of the user when the value exceeded threshold. If there any other reliable method to do this? Missing the message box might cause serious problem for my work.


Another problem> Checking based on timing
I need to perform checks on the cells in the worksheet. For example if
the threshold is 60 between 8 am to 8pm and threshold is 40 between 8pm till 8am

Needed to add a timing condition in the if else also. How can we do it?

Please Login or Register  to view this content.

Qn: Currently i am using trying to let the macro check run every 3 minutes using this statement
Application.OnTime Now + TimeSerial(0, 0, 2), "check".

Is it better if i use worksheet change event for this case? If i use worksheet change event and place the call check statement in there, is it as soon as the cell value changes above threshold, the check routine will execute?



Qn: Since i am refreshing the 3 web query every 5 minutes and the check procedure is run every 3 minutes. Alot of activity is going on in this workbook. I also have other excel workbooks running doing other stuff, I realised that while working with other worksheet, it tends to be very laggy at times. I believe it's caused by the activities going on in the datacheck workbook. Any work around for this?

Hello huat08,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon

1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] After the last line.