PDA

View Full Version : Progress bar indicater



BENSON
04-30-2007, 08:05 AM
I know this is a big ask, but I am trying to link the following two codes together.The first code I downloaded from another site (the progess bar ).The second code is for a macro that runs on my spread sheet.The time taken for the marco to to run is 39 Secs.I was hoping to combine the two to show the rate of progress of the Macro. If any body could help me I would appreicate it.

Thks


Private Sub UserForm_Activate()
'CALL PROGRESS METER AS SOON AS BOOK LOADS
Call RunMeter
End Sub
Private Sub RunMeter()
'USE-GENERIC PROGRESS METER
'DEC VARS
Dim intTotalOps As Integer 'TOTAL OPERATIONS
Dim intBumpMeter As Variant '1 OPERATION CYCLE (1 / intTotalOps) ->Running Total
Dim intBumpMeter2 As Variant '1 OPERATION CYCLE (Non Changing Constant)
Dim X As Long 'LOOP VALUE

'SET VARS
intTotalOps = 10000 'CAN BE ANY NUMBER OF ROWS
intBumpMeter = 1 / intTotalOps ' CALCULATE VALUE OF 1 OPERATION
intBumpMeter2 = intBumpMeter ' SET VALUE OF SECOND METER BUMP TO FIRST
'SET UP CODE LOOP
For X = 1 To intTotalOps
'CODE GOES HERE
'--------------------------------


'--------------------------------
'END CODE

Call MeterUp(intBumpMeter) 'CALL PROGRESS METER
intBumpMeter = intBumpMeter + intBumpMeter2 'INCREMENT METER BY ONE TO CREATE A RUNNING TOTAL

Next X

MsgBox "Operation completed successfully!", vbInformation, "User Message"
Call MeterUp(0) 'OPTIONAL - PUSH METER BACK TO ZERO IF DESIRED

Unload UserForm1

End Sub
Private Sub MeterUp(meterval2)
meterval = Format(meterval2, "0%")
ProgBar1.Value = Val(meterval)
DoEvents
End Sub


Application.ScreenUpdating = False
Dim strAnswer As VbMsgBoxResult
strAnswer = MsgBox("DO YOU WANT TO IMPORT LAST YEARS SALES FOR PERIOD 11 ", vbQuestion + vbYesNo, "Decision time!")
If strAnswer = vbYes Then
UserForm1.Show (0)
DoEvents
Range("E1").Select
ActiveCell.FormulaR1C1 = "'PERIOD 11"
Range("B5").Select
ActiveCell.FormulaR1C1 = "12/4/2007"
Range("D5").Select
Cells.Replace What:="Period ending ????????", Replacement:= _
"Period ending 01012007", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False
Sheets("SEA POINT").Select
Range("D5").Select
Cells.Replace What:="Period ending ????????", Replacement:= _
"Period ending 01012007", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False
Sheets("WARTER FRONT").Select
Range("D5").Select
Cells.Replace What:="Period ending ????????", Replacement:= _
"Period ending 01012007", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False
Sheets("REGENT RD").Select
Range("D5").Select
Cells.Replace What:="Period ending ????????", Replacement:= _
"Period ending 01012007", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
:=False, SearchFormat:=False, ReplaceFormat:=False
Sheets("Gardens").Select
Unload UserForm1
Application.ScreenUpdating = True
End If
End Sub

Bob Phillips
04-30-2007, 09:14 AM
Can you post your workbook, it should be trivial to insert it in there.

BENSON
05-01-2007, 02:25 AM
the file sixe is to big to attach