Two problems with ProgressBar

K0st4din

Well-known Member
Joined
Feb 8, 2012
Messages
501
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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.
7228817m.jpg

or
7228818R.jpg

Link to file
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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?
 
Upvote 0
Do you have any ideas friends?
 
Upvote 0
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.

Code:
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
 
Upvote 0
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
Code:
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
7239267q.jpg
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
7241804K.jpg
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top