Help with Message Box Auto Dismiss code

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
89
Office Version
  1. 365
Platform
  1. Windows
I want a pop up box to automatically close or dismiss after 10 seconds of inactivity. Good has lead me to this code but when i run it the message box does not go away on its own. what could i be missing?

VBA Code:
  Dim AckTime As Integer
  Dim box As Object
  Set box = CreateObject("WScript.Shell")
  AckTime = 10
  Select Case box.Popup("Update Complete", AckTime, "Status", 0)
   Case 1, -1
      Sheets("Complaints").Protect Password:="Secret"
      Sheets("AACT").Protect Password:="Secret"
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      ThisWorkbook.Save
   End Select

This bit of code is in the middle of other code. Original source i got from the interwebs used "Exit Sub" where all my reprotections and other application settings are now because if i exited on click it left my sheet wide open for all sorts of user input destruction =D

Also i tried simply replacing AckTime with number 10 as the normal syntax just requires a number and that did not work
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,798
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The Shell.Popup method is just notoriously unreliable.
 

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
89
Office Version
  1. 365
Platform
  1. Windows
I see, is there anyway possible that i can set up a simple message box that can close on its own?

its basically just letting the user know the macro they just ran is done running becuase if you run it on its own it actually doesnt look like it did anything, not from the user's perspective

The only reason its an issue right now is because i want the same macro to run when i close the workbook and with that pop up message showing up if someone was to close and walk away they would leave the file locked.

ofcourse removing the message altogether would by my last resort. Hoping for a solution
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,798
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Using a userform is the simplest solution. You can then code a loop in it so that it closes after a set time.
 
Solution

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Ooo, ill have to google that then, I've never played with User forms before, Thanks for your advice I'll let you know how it works out
 

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
89
Office Version
  1. 365
Platform
  1. Windows
your right changing it to a User form works perfectly thank you
 

Forum statistics

Threads
1,181,680
Messages
5,931,375
Members
436,788
Latest member
er19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top