Remove MsgBox, display status bar

[vbacoder]

Hi everyone,

When I run one of my macros, I display a message box at the start to warn the user to close some files before running. When the user clicks OK, the macro continues, but the message box remains. #1 Is there a way of removing this so it doesn't display throughout the run?

Also, #2 if anyone could point me to a good and light procedure to display a macro run time status bar, I would be grateful. I have created a basic one in the past, but it required quite a lot of additional code to be inserted into the original macro. It was also quite non-linear, which made it difficult to reasonably gauge time to completion.

Many thanks,

vcoder

 

[Rekd]

Hi everyone,

When I run one of my macros, I display a message box at the start to warn the user to close some files before running. When the user clicks OK, the macro continues, but the message box remains. #1 Is there a way of removing this so it doesn't display throughout the run?

Also, #2 if anyone could point me to a good and light procedure to display a macro run time status bar, I would be grateful. I have created a basic one in the past, but it required quite a lot of additional code to be inserted into the original macro. It was also quite non-linear, which made it difficult to reasonably gauge time to completion.

Many thanks,

vcoder

Your code probably gets into hypermode before the display refreshes. Put this after the msgox

do events

Instead of displaying time to complete (which would require to you querry every few milliseconds and calculate the time to keep it current) you should consider a percentage to completion.

I use something like this:

sSuccess = FormatPercent(i / LastRow, 1)
Application.StatusBar = "Adding Blue Data Links. Working on row " & i & " of " & LastRow & "  (" & sSuccess & ")"

Then at the end I clear the status bar with:

Application.StatusBar = False

 

[Smitty]

1) Can you post your code? If it's a VBA MsgBox it sounds like it may be a system issue, if it's a user form are you unloading it prior to continuing the code?

2) http://www.eggheadcafe.com/software/aspnet/30081943/how-do-i-create-a-progess-bar.aspx

HTH,

 

[vbacoder]

I use something like this:

sSuccess = FormatPercent(i / LastRow, 1)
Application.StatusBar = "Adding Blue Data Links. Working on row " & i & " of " & LastRow & "  (" & sSuccess & ")"

Then at the end I clear the status bar with:

Application.StatusBar = False

Hi Rekd, many thanks for your reply. I just want to clarify if "LastRow" is a variable that you have defined in this case for your application? Or, does it automatically pick up the last line of the code?

 

[Rekd]

Hi Rekd, many thanks for your reply. I just want to clarify if "LastRow" is a variable that you have defined in this case for your application? Or, does it automatically pick up the last line of the code?

It's a variable derived from a function I pilfered (prolly from here) to find the last row...

LastRow = Range(whatColumn & Rows.Count).End(xlUp).Row ' Get number of rows in worksheet

whatColumn is the same, a variable. I use an input box to ask the user what column he wants to work with.

 

[vbacoder]

1) Can you post your code? If it's a VBA MsgBox it sounds like it may be a system issue, if it's a user form are you unloading it prior to continuing the code?

HTH,

It's not a user form. The code is similar to:

Sub test()
'Starting code...
MsgBox("Please check files")
'Continuing code...

Placing Do Events after the messagebox generates a runtime error, unfortunately.