 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2007Create "a thing" that shows vba code running
Dear all, Is it possible to create "a thing" that shows for the user that some vba codes are running? For example, I have four vba codes and it takes a few minutes to process, but the user can get confused if it is working or not. Thanks in advance!!! Try a progress bar. Robin Hammond's Progress Bar http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Dear all, > [quoted text clipped - 5 lines] > > Thanks in advance!!! Dear Bob, This is the main code: Sub Test() Dim PB As clsProgBar Dim nCounter As Integer Dim lWaitCount As Long Set PB = New clsProgBar With PB .Show For nCounter = 0 To 100 .Progress = nCounter For lWaitCount = 0 To 1000000 If UserCancelled = True Then GoTo EndRoutine Next lWaitCount Next nCounter EndRoutine: .Finish End With Set PB = Nothing End Sub But how can the excel calculate the progress of some codes? Is it possible? I think it's necessary to adapt this code, isn't it? Thanks in advance!!! Andr? That is only a demo, you would need to integrate it into your code. What does your code look like, hopefully not too much, not too complex? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) Dear Bob, This is the main code: Sub Test() Dim PB As clsProgBar Dim nCounter As Integer Dim lWaitCount As Long Set PB = New clsProgBar With PB .Show For nCounter = 0 To 100 .Progress = nCounter For lWaitCount = 0 To 1000000 If UserCancelled = True Then GoTo EndRoutine Next lWaitCount Next nCounter EndRoutine: .Finish End With Set PB = Nothing End Sub But how can the excel calculate the progress of some codes? Is it possible? I think it's necessary to adapt this code, isn't it? Thanks in advance!!! André > Is it possible to create "a thing" that shows for the user that some > vba codes are running? > > For example, I have four vba codes and it takes a few minutes to > process, but the user can get confused if it is working or not. Maybe you can make use the following. I have more familiarity with using this code in a compiled VB program, but it seems to work okay on an Excel spreadsheet. The code will change the cursor to the "busy" icon (the spinning hourglass for Windows versions before Vista and that spinning circle for Vista) before your code executes and back to the default icon afterwards. Simply copy/paste the following into the applicable Worksheet code window and call your macro (Sub Test() for this example) from the spreadsheet itself (via Alt+F8 or as the assigned macro for a control, such as a button, located on the sheet). Private Declare Function LoadCursor Lib "user32" _ Alias "LoadCursorA" _ (ByVal hInstance As Long, _ ByVal lpCursorName As Long) As Long Private Declare Function SetCursor Lib "user32" _ (ByVal hCursor As Long) As Long Dim HoldCursor As Long ' Receives handle of default cursor Dim NewCursor As Long ' Newly created cursor Const IDC_WAIT = 32514& Sub Test() NewCursor = LoadCursor(ByVal 0&, IDC_WAIT) HoldCursor = SetCursor(NewCursor) ' ' Your code goes here -- the MsgBox statement is just an example ' MsgBox "Hello" SetCursor HoldCursor End Sub Note: I am pretty sure that the spinning icon will only be spinning over the spreadsheet itself (so if you pop a MessageBox, the cursor won't be busy icon while over the MessageBox even though your code popped it up). Rick Andy Pope has a number of different progress bars (meters), available here: http://andypope.info/vba/pmeter.htm Hope this helps. Pete On Nov 9, 6:38 pm, gataro...@ig.com.br wrote: > Dear all, > [quoted text clipped - 5 lines] > > Thanks in advance!!! You can put a modeless userform on the screen. In it, you can put messages (via a label) that say what the program is doing at the time ("Creating user profiles," "Formatting drive C," etc.), or just "Running." This is for Excel 2000 and up, I think. SignatureRegards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- > Dear all, > [quoted text clipped - 5 lines] > > Thanks in advance!!! gatarossi - Maybe the simplest way, but without creating "a thing," is to use Application.DisplayStatusBar = True Application.StatusBar = "Some VBA code is running. Please be patient..." - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel > Dear all, > [quoted text clipped - 5 lines] > > Thanks in advance!!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.