PDA

View Full Version : How to display updated process on userform for user



VISHAL120
09-15-2011, 11:02 PM
Hi All,

I have actually the following codes which runs and this takes approx. 2 secs to run.

i wanto display a userform as an information in front of the user that updating is in process like this:

that is start to end row updating : example

row 1 to 500 rows updated, row 2 to 500 rows updated that is the start row changes and the last row ( 500) stay fix. the last row shall be read from the last used row on the sheet.

here is the code that is running actually and actually i have only put the timer that is after update it shows on how many secs it has run.
i have been trying a lot on that but has not been able to put it together with the actual loop.

Loading_Start_Date = Range("shadow_k_mins!A1").Offset(Calendar_Row - 1, Loading_Column_Start - 1)
Loading_End_Date = Range("shadow_k_mins!A1").Offset(Calendar_Row - 1, Loading_Column_End - 1)
Loading_Row_Start = Shadow_Km_Header_row + 1

Application.ScreenUpdating = False

' If Range("Shadow_k_Mins!A1").Offset(loading_row_start - 1, Shade_Km_Pos_Col - 1) = "" Then
'
' MsgBox "No data for Loading", vbInformation, "Fleche Loading Reports"
' Exit Sub
' End If
'



'determine loading size for loop


'loading_row_end = Range("Shade_k_mins!A65536").Offset(0, shade_km_Pos_col - 1).End(xlUp).Row


Do While Range("Shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Pos_Col - 1) <> ""


Total_Gmts_Loaded = Range("Shadow_K_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Load_cells_Col - 1)

MIns_data = Range("Shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Timing_Col - 1)

efficiency = Range("Shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Eff_Col - 1)

Block_start_date = Range("Shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Block_Date_Col - 1)

Module_col = Range("Shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Module_Col - 1)
Group_Col = Range("Shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Group_Col - 1)

If Block_start_date > Loading_End_Date _
Then GoTo SKIP_ROW


If MIns_data = "" _
Then GoTo SKIP_ROW

If Module_col = "" _
Then GoTo SKIP_ROW

If Group_Col = "" _
Then GoTo SKIP_ROW






Range("shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_KM_Total_Mins_Load_Cells_Col - 1) _
= WorksheetFunction.Round(Total_Gmts_Loaded * MIns_data, 2)



Daily_Gmt_Cap = Range("Shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Daily_Gmt_Col - 1)

Range("shadow_k_mins!A1").Offset(Loading_Row_Start - 1, Shade_Km_Mins_Prod_As_Per_Cap_Col - 1) _
= WorksheetFunction.Round(Daily_Gmt_Cap * MIns_data * efficiency, 0)


SKIP_ROW:
Loading_Row_Start = Loading_Row_Start + 1


Loop

thanks help on that please.

mikerickson
09-16-2011, 11:04 PM
Dim rowStart as Long, rowEnd as Long, rowWorking as Long

rowStart = Loading_Row_Start = Shadow_Km_Header_row + 1
rowEnd = Range("Shade_k_mins!A65536").Offset(0, shade_km_Pos_col - 1).End(xlUp).Row

rowWorking = rowStart

Do While rowWorking <= rowEnd
Label1.Caption = Format((rowWorking-rowStart)/(rowEnd - rowStart), "0 %") & " completed"

Rem do stuff

rowWorking = rowWorking + 1
Loop
Alternatly, you could have a label with a colored background increase its .Width with every loop to make it look like a progress bar.