Two problems with ProgressBar

[K0st4din]

Hello to all
I would like to ask is there anyone who can help me with two things in my file is related to the "ProgressBar"?
Attachment with a "UserForm" and a few buttons it with Admissions macros for each of them . I've made one "ProgressBar", but do not know how to do that when I press any button , it activates and shows me as percentages from 0 % to 100 % (ie 100% is assumed that the macro has done its work ) and then hides it (Unload) and so for each button pressed .
Rather delve into the web, but I can not find how to change the color from blue to any other (ProgressBar).
I would be very grateful for assistance from your side.

or

Link to file

 

[Teeroy]

Firstly you don't seen to set ProgressBar1.Value anywhere so it won't change.
Secondly it doesn't seem easy to change the color of the ProgressBar, but you can do it with creating a custom control (see How to create a smooth ProgressBar in Visual Basic .NET or in Visual Basic 2005).

 

[K0st4din]

Okay, so the second problem of discoloration leave him. Be the first problem - can you make yourself a macro, because I can not do it and do not know how? Real buttons are "UserForm", but macros act for all other sheets carry, read (generally perform some action).
Thank you in advance

 

[K0st4din]

The other thing I can think - if you have for each button I will make my own "ProgressBar" and pressing the desired button will be displayed upon completion of the required action to be hidden.
And how can we do to work together on selected macro button?

 

[K0st4din]

Do you have any ideas friends?

 

[Teeroy]

You have to decide how you want to report progress. I've changed one of your subs (lines in red) to give you an example which shows progress through each sheet (untested since I don't have files to process).

One thing to remember, reporting the progress too often can slow the code running.

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
    [COLOR=#ff0000]lTotalRows = ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp)).Rows.Count - 1[/COLOR]
    For Each Cell In ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp))
    [COLOR=#ff0000]ProgressBar1.Value = Int(100 * (Cell.Row - 1) / lTotalRows)
    DoEvents[/COLOR]
    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) 'Terapia_Nereimburs N
    Next
  Next
End Sub

 

[K0st4din]

Hello and thank you very much for your help, but I think in the macro is something you should be fine because it gives me an error in this line

ProgressBar1.Value = Int(100 * (Cell.Row - 1) / lTotalRows)

I do not understand, but including 100 - my lines(or rows) should be? If so - then I at any moment there is no way to know how many rows I will have. If other - then I have no idea why it gives me an error.
There's more - after the macro has done its work is not hidden, and thus figuratively not understood that it is done

 

[Teeroy]

I've just made some dummy data and run it through the code that I gave you and it works. I don't understand your error.

 

[K0st4din]

Hello
I can not tell you what the error or if I'd fix it myself or I would give you an answer.
If you do not tell which line gives me the error (then you will ask me where is the error) if not attached photos (you will want an explanation of what appears to be able to understand).
What I provide to you, you can understand why confused code.
Thank you in advance

 

[K0st4din]

Attach your examples and when you activate "UserForm" select the second button and it will show you why it gives an error. I do not know the answer.
Once again thank you
Link to file
and button: