Results 1 to 5 of 5

Thread: Hide ProgressBar After Process Loop Is Complete

  1. #1
    Member
    Join Date
    Nov 2012
    Posts
    47
    Rep Power
    0

    Hide ProgressBar After Process Loop Is Complete

    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 .
    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
        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

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try
    Code:
    ProgressBar1.Visible = False
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Nov 2012
    Posts
    47
    Rep Power
    0
    Hello, thanks a lot, but again not working and again hides all "UserForm"
    If we could try something else?
    Attached Images Attached Images

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Just noticed that you are using this line in your code
    Code:
    End
    Why would you ever use the End statement in your code. Please remove and try again.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Member
    Join Date
    Nov 2012
    Posts
    47
    Rep Power
    0
    Yes yes yes now works without problem.
    Many thanks from me to you.
    And with regard to this "End" - I wrote it because I myself do not know how to do it and tried different options and it did not work and I was desperate.
    Once again thank you warmly.

Similar Threads

  1. Auto Complete for Data Validation
    By IJC in forum Excel Help
    Replies: 1
    Last Post: 05-15-2013, 09:30 AM
  2. VBA To Hide And Unhide Rows
    By paul_pearson in forum Excel Help
    Replies: 10
    Last Post: 05-08-2013, 03:14 AM
  3. How To Protect Hide Formula In Excel
    By paul_pearson in forum Excel Help
    Replies: 1
    Last Post: 04-10-2013, 08:33 AM
  4. Replies: 22
    Last Post: 03-19-2013, 07:57 AM
  5. Hide Ribbon and Formula Bar
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-21-2011, 07:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •