Unable to view message box when working on multiple applications

[bimmy80]

Hello,

I came across a code which prompts the users to enter time (time is entered in minutes) and reminder message.

On the specified time the message box pops-up. The code works perfectly if the users are working only on excel.

Below is the code -

Please Login or Register  to view this content.

Problem -

Since the users mostly work on multiple applications, they are not able to view the message box. But when they jump to excel they are able to view the message box.

Solution -

I'm looking for a code which will make the message box appear even if they are working on excel or any application.

Have attached sample sheet

[alansidman]

Add a line of code before the End Sub

Please Login or Register  to view this content.

[bimmy80]

Hello alansidman,

I tried your suggestion, but still facing the same problem.

1st attempt by adding your line in the first part of the code.
2nd attempt by adding your line in the second part of the code.
3rd attempt by adding your line in both part of the code.

Message box does not pop-up if working on different applications. However excel logo keeps blinking on the specified time.

My requirement is the message box should be visible to the users if they are working on any application.

Refer snapshot of the problem

[alansidman]

Maybe this solution will work for you.

http://www.experts-exchange.com/ques...cel-Macro.html

[bimmy80]

Hello alansidman,

I'm not able to view the content of the page as it requires membership.

I did some googling and came across few code snippets but now sure how to use it in my code

Below are some of the codes that I came across -

1st

Please Login or Register  to view this content.

2nd

Please Login or Register  to view this content.

3rd

Please Login or Register  to view this content.

[JapanDave]

Does this help?

Please Login or Register  to view this content.

[bimmy80]

How do I add your code to my code.

In other words if I want the CMD box to appear after 5 mins, what part of my code needs to be changed to achieve this.

[JapanDave]

My Bad, I did not see it was an input box.
In this case try Rory's (Romperstompers) method here,
http://www.excelforum.com/excel-prog...-of-excel.html
Using the Windows API,
Just change the message box to input box

Please Login or Register  to view this content.

[bimmy80]

Hello,

Edit : I have made few changes to the below code to achieve part of my requirement which is to bring the message box to the front of any application at the specified time.

Please Login or Register  to view this content.

Above code also allows the user to enter different reminder messages. I want the pop-up message box to reflect different reminder messages entered. I think below code needs to be changed but don't know how to do it.

Please Login or Register  to view this content.

[bimmy80]

kindly assist

[JapanDave]

Sorry, I don't quite follow what different messages you need and when???

[bimmy80]

The code provides 2 prompts -

1st prompt asks the user to enter time (time is entered in mins)
2nd prompt asks the user to enter the reminder message

Let's say, in the 1st prompt I enter time as 5 mins and in the 2nd prompt I enter reminder message as Update sheet. After 5 mins the message box appears with the reminder message as Update sheet.

To get a better understanding of above explanation kindly run the macro from the originally attached sheet.

Above mentioned activity keeps changing. I need the message box pop-up in front of any application at the specified time with the specified reminder.

[JapanDave]

Sorry, Christmas got in the way. How are your Input boxes fired off?

[bimmy80]

Thanks for dropping in.

Merry X'Mas.... Hope you had a wonderful celebration with your friends and family.

I played around with the code and currently I'm able to view the pop-up message box when any application is running.

Please Login or Register  to view this content.

The problem now is, how to display different messages whenever the macro is run.

I think below code needs to be changed to solve above mentioned problem. I don't know how to do it.

Please Login or Register  to view this content.

Kindly refer attached sheet to understand my problem.

Click on the button and insert minutes as 1 and reminder message as Update sheet. After 1 min pop-up box shows the message as This is a native Message Box and not as Update sheet.

[JapanDave]

Do you mean something like this?

Forget that below, I just re-read post, hold on a second.

Please Login or Register  to view this content.

[JapanDave]

Ok, Change all the code to this.

Please Login or Register  to view this content.

[bimmy80]

Can I have 1 small addition to the above code.

After clicking OK button on the pop-up message, the user should be taken to the workbook from where the macro is being run.

Let's say the code is being run from workbook named "Reminder". So after clicking ok button on the pop-up message the user should be taken to Reminder workbook.

[Leith Ross]

Hello bimmy80,

This worked for me.

Please Login or Register  to view this content.

[bimmy80]

Hello Leith Ross,

The code works well.

I only need 1 small addition.

As explained above after clicking ok on the pop-up message the user should be taken to the workbook from where the macro is being run.

[JapanDave]

What if the workbook file path?

[bimmy80]

Path is - C:\Users\BIMMY\Downloads\Reminders.xlsm

If the path is changed, will copy pasting the new path work

[JapanDave]

Try this,
Enter the workbook names into the "arr" variable and if the file path is always the same. If not you will need to adjust the code for the new file path. This macro will deal with several different named books with the same path.

Please Login or Register  to view this content.

[bimmy80]

I tried above code. After clicking ok on the pop-up message, the code doesn't take me back to reminders workbook.

The workbook Reminders is saved as xlsm. Does that have anything to do.

[JapanDave]

Sorry , try this.

Please Login or Register  to view this content.

[bimmy80]

Sorry, still not happening...

Have attached sheet for reference.

Can you check from your side after downloading,and making necessary changes to the path.

I

[JapanDave]

My bad, had a brain fart.
try this.

Please Login or Register  to view this content.

[bimmy80]

I just HOPE you have LOTS of PATIENCE.

It's not happening.

Let me again say... On clicking ok on pop-up message the code should take me to the excel workbook (or sheet) from where the macro is being run.

Don't give up on me...

[JapanDave]

I tested it and it works
Put it before the "EXIT Sub" line and tell me what it says.

Please Login or Register  to view this content.

[bimmy80]

When I put the reminder message (2nd prompt) as reminder and run the code, it takes me back to the reminder workbook.

This is not what I'm looking for.

Let me explain - (I hope you have run the code and my explanation makes sense)

When clicked on "Button1" 1st prompt appears, I insert time as 1. Clicking ok gives 2nd prompt, I insert reminder message as "Update Sheet" and click ok.

After 1 min pop-up box appears with message as "Update Sheet". Clicking ok on the pop-up message should take me back to the workbook from where the macro is being run.

The current code does go back to the workbook if the message is inserted as "Reminder". If a different message is inserted it does not work.

[JapanDave]

When I put the reminder message (2nd prompt) as reminder and run the code, it takes me back to the reminder workbook.

This is not what I'm looking for.

Let me explain - (I hope you have run the code and my explanation makes sense)

When clicked on "Button1" 1st prompt appears, I insert time as 1. Clicking ok gives 2nd prompt, I insert reminder message as "Update Sheet" and click ok.

After 1 min pop-up box appears with message as "Update Sheet". Clicking ok on the pop-up message should take me back to the workbook from where the macro is being run.

The current code does go back to the workbook if the message is inserted as "Reminder". If a different message is inserted it does not work.

I think I follow. Could you give me the names of all the books involved in a certain instance of code running?

[bimmy80]

Currently I'm testing the code from my home.

To test the code I opened 4-5 workbooks, Book1, Book2, Book3, Book4 and Book5. When I ran the code the pop-up came with the message as "Reminder". When clicked on ok the code took me to the reminder workbook.

I'm will be releasing this code to my team. The usage of excel is huge. Runs into 10-15 workbook for the time being.

[JapanDave]

So is the code that fires the input boxes and the message box is in the workbook named "Reminder"?

[bimmy80]

Yes... The code that fires the input boxes and the message box is stored in the workbook by the name "Reminder"

[JapanDave]

Yes... The code that fires the input boxes and the message box is stored in the workbook by the name "Reminder"

So how do you know which workbook needs to have something updated?

[bimmy80]

That's where the 2nd prompt comes into play.

I just type message (reminder) in the second prompt.

Let's say I want to update Summary sheet with 15 counts after 20 mns. I run the code, I type 15 in 1st prompt and in the 2nd prompt I type "Upd Summary sheet 15 cts". After 15 mns the pop-up box appears with the message as "Upd Summary sheet 15 cts".

Click ok button on the pop-up box should take me to the "Reminder" workbook to enter next time and reminder message.

[JapanDave]

So you want the code to take you to "Summary sheet 15 cts" ? Than you want the code to take back to the "Reminder" (the workbook with the code in it). Is the Summary sheet workbook open? Or do you need to have the code open it?

[bimmy80]

There's some confusion here...

"Reminder" workbook is were the code will be stored. This workbook will be used by the users to insert messages to remind them which workbook or activity needs to be updated after certain time.

Current code does this, but, it does not take the user back to "Reminder" workbook to insert next message.

[bimmy80]

Edit - This post was deleted as it was repeated

[JapanDave]

Does this work for you,

Please Login or Register  to view this content.

[bimmy80]

getting error message as - User defined type not defined - and below code gets highlighted

Please Login or Register  to view this content.

[JapanDave]

My bad,

Miss spelled Workbook as Worbook.

Please Login or Register  to view this content.

[bimmy80]

I kept only "Reminder" workbook open. When i tested the code it takes me back to the ''Reminder'' workbook.

I tested the code by keeping 2 more workbooks opened - Book1 and Book2, the code does not take me back to the "Reminder" workbook. It takes me to the first active workbook which is Book2.

Can a code be added to the above code which will make "Reminder" workbook as the active workbook no matter how many workbooks are opened.

[JapanDave]

I kept only "Reminder" workbook open. When i tested the code it takes me back to the ''Reminder'' workbook.

I tested the code by keeping 2 more workbooks opened - Book1 and Book2, the code does not take me back to the "Reminder" workbook. It takes me to the first active workbook which is Book2.

Can a code be added to the above code which will make "Reminder" workbook as the active workbook no matter how many workbooks are opened.

OK, I see what you want now.

[JapanDave]

OK, with a bit of help I think I have it.
This should bring the workbook to the front regardless of what application is running.

Please Login or Register  to view this content.

[bimmy80]

JapanDave... Its works PERFECTLY.

You have LOTS of Patience.... and I mean Lots.

Thanks a TONNE for sticking with me to the end.

[JapanDave]

No problem, don't forget to rep anyone that has helped you.

Dave

[bimmy80]

By rep you mean rating the thread... Right.

I did that by clicking on "Rate this Thread" , clicked on 1st option and clicked on vote.

When I tried again it's giving me the message as " You have already rated this thread"

What did I do wrong.

[JapanDave]

Add reputation, click the little star (*) in the bottom left hand corner of the users post you want to rep. Next to report post in the users post.

Dave

[bimmy80]

Understood...Rep added