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]

Replace the line

lTotalRows = ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp)).Rows.Count - 1

with

[COLOR=#ff0000]lTotalRows = ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp)).Rows.Count[/COLOR]

BTW the code for CommandButton2_Click runs through every sheet but it only seems applicable to the format of data on Sheet2.

 

[K0st4din]

Hello
to the example in the case, but sometimes the actual file is not always in the range of cells and sheets have information but the main macro need to check all sheets .
Thank you very much .
There is one more thing ( I do not know whether it gets to you ) - bar (ProgressBar) is not filled to the end, which in turn led me to a situation to wait 7-8 minutes to figure out that the macro has stopped. Attach a photo

Can you do it to hide (Unload) or when it is done with any action to suggest to me that it is over?
Otherwise there would think that if the tape is not the end so there is something to do.

 

[RoryA]

Please read the forum rules on cross posting and then add links to your posts in other forums (there are at least 2). Thank you.

 

[Teeroy]

It's giving incorrect results because the data format is not as expected. The number of rows is set from Column J and Column J didn't have any data. You'll have to write error traps for things like that.

Alternatively if you just want it to show complete when the macro finishes then add the following line immediately before the End Sub.

ProgressBar1.Value = 100

 

[K0st4din]

What I say to error traps is complicated for me, but with the addition suits me perfectly.
Thank you for your cooperation with all my heart.
Thank you for being there to help us.
Bowing before you.

 

[K0st4din]

Hello friend
Could I ask you (because I tried several ways) but somehow I could not deal with my problem!?
Is there a way to write and what it has finished its operation to hide ProgressBar?

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) 'Terapia_Nereimburs N
    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'll be grateful if you help me and if possible to happen hide progressbar

 

[K0st4din]

Friends I know there's no way you can hide ProgressBar?

 

[RoryA]

Perhaps you could add links to your cross posts as I asked before?

 

[K0st4din]

And you sir, you know what you want or moderator of this title makes you behave so arrogantly?
If a person decides to ask in another forum - why ask.
If a person wait a few days ( because everyone has commitments ) and when you enter the forum to help - again you are not satisfied.
I want to ask you - how to please you, or maybe you think that the world revolves around you?
Are you trying to tell me that if you have any problem as you are sick - go only in one place and only one doctor or hospital to reach the place (for me forum) is going with a doctor if the doctor can not help you, then go to another doctor himself or sends you to another specialist - because in this situation the end result is quite different (if anything happens beyond repair with the patient's life )
Absolutely I have always been respectful to all of you with great respect, but you have to understand that when a person needs some help he sought in the hope of people like you to help him not to do the monkey ( figuratively speaking ).
If you think that what I say is not true - you be alive and well, if you can help me, if you can not - again be alive and well.
Regards

 

[Teeroy]

k0st4din, there is nothing wrong with cross posting but there is an ettiquette involoved. Read the "Guidelines of posting" (2nd sticky message on the forum) item 24, and the hyperlink with it.
RoryA is merely telling you to follow the ettiquette and post a hyperlink from each cross post to the original post. How would you feel if you spent time help solving a problem only to find that it has been solved elsewhere (and the poster didn't re-post to tell you)?