Solved

Display info/data as Macro runs

Posted on 2003-11-19
14
208 Views
Last Modified: 2010-05-01
In VBA with Excel
Have a macro
It loops thru some cells (hundreds)
Would like to have msgbox/userform or whatever, display some values as it processes the data (w/o user having to press OK, or close the window each time - a status window, if you will)
To simplify the q, I want to have it display something like this "Processing Row Number: RowNo"
Where "RowNo" is the number (integer) of the row it is currently processing.

For RowNo = FirstRow To LastRow
'display a message saying "Processing Row Number: RowNo"
Next RowNo

Tried using MsgBox & UserForm but have to press OK or close window each time (hundreds)
Just want it to display the info and move on.
0
Comment
Question by:tlfeet
  • 6
  • 5
  • 2
14 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9783293

Dont use a MsgBox

Use a userform with a label (named Label1 below) such as

Private Sub UserForm_Initialize()
For RowNo = FirstRow To LastRow
Label1 = "Processing Row Number: " & RowNo
DoEvents
Next RowNo
End Sub

Cheers

Dave
0
 

Author Comment

by:tlfeet
ID: 9783490
Hi Dave,
Tried UserForm
Have to close the form/window for each row.
i.e. Instead of clicking on "OK" in a Msg Box for each row [not good], have to close the UserForm window for each row...also not good.
Want to data/info to display for each row, w/o "close window each time (hundreds)"
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 50 total points
ID: 9783557
If you call the userform as VbModeless then the macro runs in the background

the code below runs the userform row update on evrey 100th iteration
ie

Sub mysub()
UserForm1.Show vbModeless
For RowNo = FirstRow To LastRow
If RowNo Mod 100 = 0 Then
UserForm1.Label1 = "Processing Row Number: " & RowNo
DoEvents
End If
Next RowNo
End Sub

Cheers

Dave
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:tlfeet
ID: 9784341
Hi Dave,

Thanks, exactly what I was looking for.
I knew it was something relatively simple.
:-)

Thanks again
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9784608
No problem tlfleet :)

although I'm a little curious why you gave me a "B". I dont usually comment on grades as its part of the game but you seemed pretty happy with the outcome of this one

Cheers

Dave
0
 

Author Comment

by:tlfeet
ID: 9784924
Tokk you 2 tries. ;-)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9784932
LOL :)
0
 

Author Comment

by:tlfeet
ID: 9784934
Shoot, hit submit before I was done.
Misspelled "Took"
Wanted to add: did not know what to put (nor how many points to offer).
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9784969
if its a B for two tries then at this rate I'll be getting Z's on some of my answers..........:)

Points and graded are both awarded at your discretion,  I've never seen an expert argue about points as it is set when they go in, but many don't like "B"s let alone "C"'s. I'm fine with B's myself (although I've never given one) as answers are sometimes partial, badly worded or too short.

But I think you might be getting some sharp words if you give B's for solutions you are obviously happy with.

Happy asking

Cheers

Dave
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 9787317
tlfeet:

It's very difficult to get answers right the first time, particularly in code. Some people are answering your questions during lag time at their full-time jobs, or are just anxious to get you an answer...

From what I can see, you got a VBA solution in less than 2 hours. Regardless of the number of tries, I call that pretty darn good.
:)

The grade you give in no way affects the QUESTION points you pay, but DOES affect the EXPERT points received by the expert.

If you'd like to change that grade to "A", you can make that request at community support. Just click on the CS link at the top of the page.

~Dreamboat
Denmother and Protector of all Experts
LOL!
0
 

Author Comment

by:tlfeet
ID: 9787953
Hey Dave,
Followed Dreamboat's suggestion and asked CS to change the grade to an A.

Sorry about that.
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 9788010
LOL, tlfeet.

That's YOUR job.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9791100
Hi tlfeet

No problem, thanks for the upgrade

Thanks for the post Dreamboat

Cheers

Dave
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA, find a string in a column, update a cell 7 75
Visual Studio 2005 text editor 10 38
IF ELSE Statement in Excel Macro VBA 16 62
VB6 - Scroll Mouse wheel on Picturebox 13 42
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now