Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup a Msg to Prevent Code Interruption
Hello Experts,
I have a macro that's rather long. In order to run it faster, I turned off the screenupdating. But, if another user activates the macro, nothing happens on-screen so they attempt to click on the macro button a few more times. That interrupts the macro which is running in the background. How do you write an on-screen message in code that says "Please wait While the Macro is Running"? And how do you take it off? I would take the msg off just after I turn the screenupdating back On. I'm looking for a msg box only without the "OK" button. Thanks in advance, Ricky *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup a Msg to Prevent Code Interruption
Ricky,
If you are in XL97, have a look at using application.statusbar in the help file. If you are in xl2000 or higher, create a custom userform with only a label on it that says "Macro running..." and show it as modeless. In your code My Sub() frmWait.show(vbmodeless) 'run code here unload frmWait End Sub Alternatively, for a more elegant solution there is a progress bar class on my site (which does the same thing but with progress indicators) at http://www.enhanceddatasystems.com/E...rogressBar.htm. If you use it, for your purposes, just remove the Cancel button and related code for the form. Yours, Robin Hammond www.enhanceddatasystems.com Check out our XspandXL add-in "Ricky Pang" wrote in message ... Hello Experts, I have a macro that's rather long. In order to run it faster, I turned off the screenupdating. But, if another user activates the macro, nothing happens on-screen so they attempt to click on the macro button a few more times. That interrupts the macro which is running in the background. How do you write an on-screen message in code that says "Please wait While the Macro is Running"? And how do you take it off? I would take the msg off just after I turn the screenupdating back On. I'm looking for a msg box only without the "OK" button. Thanks in advance, Ricky *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup a Msg to Prevent Code Interruption
Thanks Robin,
Three questions. 1) How do you show a custom userform as modeless? 2) I have inserted a Userform, and entered the label as "Please wait while Macro is Running". But, I get an error message when I incorporate your code "frmWait.show(vbmodeless)..." So how do I incorporate my userform into your code? 3) I've also looked into your Progress Bar. It's a good tool. I have noticed that I could still click while the macro is running. So, what if disabling the keyboard and mouse function while the macro is running might be another alternative? How would you disable it? Much appreciated, Ricky *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup a Msg to Prevent Code Interruption
Ricky.
1 & 2. Sorry, my mistake. It's frmWait.Show vbmodeless. ie. without the brackets shown in my original response. Note, you need to be in XL2000 or higher for this to work. 3. Yes, you can still click on the progress bar cancel button when the macro is running, which raises an event that ends execution of everything. It's crude but effective if something is taking too long to run - and could have unintended consequences if you aren't careful. If you don't want the user to be able to do that and you are going to use the progress bar class, you can just add one line which disables the ability to cancel. If you look at the Demo module in the example workbook the code would then look like this... PB.Title = "This appears at the top of the bar" PB.Caption1 = "This appears on the first text line" PB.DisableCancel = True 'ADDED THIS LINE PB.Show I don't think you need to disable the keyboard or mouse. Hope that helps, Robin Hammond www.enhanceddatasystems.com Check out our XspandXL add-in "Ricky Pang" wrote in message ... Thanks Robin, Three questions. 1) How do you show a custom userform as modeless? 2) I have inserted a Userform, and entered the label as "Please wait while Macro is Running". But, I get an error message when I incorporate your code "frmWait.show(vbmodeless)..." So how do I incorporate my userform into your code? 3) I've also looked into your Progress Bar. It's a good tool. I have noticed that I could still click while the macro is running. So, what if disabling the keyboard and mouse function while the macro is running might be another alternative? How would you disable it? Much appreciated, Ricky *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup a Msg to Prevent Code Interruption
Hi Robin,
When I added Userform1.Show vbModeless as the first line of my codes, the Userform pops up but my codes just won't execute in the background unless I click the X in the corner to close it first. How can I have the userform show while my codes execute in the background? I do like the progress bar. I notice that it counts to 100 but does this progress bar actually measure length of my code and increments according to what has actually been completed? Thanks again, Ricky *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code to prevent users from adding new worksheets | Excel Worksheet Functions | |||
Code to prevent Remove Split or Unfreeze Panes? | Excel Discussion (Misc queries) | |||
How can I prevent access to code contained within Tab | Excel Worksheet Functions | |||
macro interruption: help!!! | Excel Discussion (Misc queries) | |||
macro interruption | Excel Worksheet Functions |