k0st4din
04-23-2014, 09:26 AM
Hello to all
I asked in another forum but did not get a response to my inquiry and therefore want to ask you if you can help me. It's an "ProgressBar" which is placed in several macro but performs the same action in a "UserForm"
Colleague helped me to implement , but we could not deal with the problem - when you complete your action to hide this "ProgressBar"
Shows a macro ( as the others are similar ) and show what I've tried must complete it to hide it "ProgressBar", but obviously wrong somewhere and I hide all "UserForm".
I would be grateful if we can find a solution or tell me what I must complete .
Private Sub CommandButton2_Click()
Dim x As Long, Cell As Range, CellText As String, ws As Worksheet, lTotalRows As Long
Dim Words As Variant, Replacements As Variant
Const TableSheetName As String = "Sheet1"
Application.Volatile
Words = Sheets(TableSheetName).Range("AH2", Sheets(TableSheetName).Cells(Rows.Count, "AH").End(xlUp))
Replacements = Sheets(TableSheetName).Range("AI2", Sheets(TableSheetName).Cells(Rows.Count, "AI").End(xlUp))
For Each ws In Worksheets
lTotalRows = ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp)).Rows.Count
For Each Cell In ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp))
ProgressBar1.Value = Int(100 * (Cell.Row - 1) / lTotalRows)
DoEvents
CellText = ""
For x = 1 To UBound(Words)
If InStr(1, Cell.Value, Words(x, 1), vbTextCompare) Then CellText = CellText & "+" & Replacements(x, 1)
Next
Cell.Offset(, 4).Value = Mid(CellText, 2)
Next
Next
ProgressBar1.Value = 100
End
Unload ProgressBar1 'I Ended this and it works, but it hides all the UserForm, which brings me to the conclusion that something has to inquire, but I do not know what.
End Sub
I asked in another forum but did not get a response to my inquiry and therefore want to ask you if you can help me. It's an "ProgressBar" which is placed in several macro but performs the same action in a "UserForm"
Colleague helped me to implement , but we could not deal with the problem - when you complete your action to hide this "ProgressBar"
Shows a macro ( as the others are similar ) and show what I've tried must complete it to hide it "ProgressBar", but obviously wrong somewhere and I hide all "UserForm".
I would be grateful if we can find a solution or tell me what I must complete .
Private Sub CommandButton2_Click()
Dim x As Long, Cell As Range, CellText As String, ws As Worksheet, lTotalRows As Long
Dim Words As Variant, Replacements As Variant
Const TableSheetName As String = "Sheet1"
Application.Volatile
Words = Sheets(TableSheetName).Range("AH2", Sheets(TableSheetName).Cells(Rows.Count, "AH").End(xlUp))
Replacements = Sheets(TableSheetName).Range("AI2", Sheets(TableSheetName).Cells(Rows.Count, "AI").End(xlUp))
For Each ws In Worksheets
lTotalRows = ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp)).Rows.Count
For Each Cell In ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp))
ProgressBar1.Value = Int(100 * (Cell.Row - 1) / lTotalRows)
DoEvents
CellText = ""
For x = 1 To UBound(Words)
If InStr(1, Cell.Value, Words(x, 1), vbTextCompare) Then CellText = CellText & "+" & Replacements(x, 1)
Next
Cell.Offset(, 4).Value = Mid(CellText, 2)
Next
Next
ProgressBar1.Value = 100
End
Unload ProgressBar1 'I Ended this and it works, but it hides all the UserForm, which brings me to the conclusion that something has to inquire, but I do not know what.
End Sub