progress bar userform inside userform not working

**kevinu** · 05-22-2018, 03:33 PM

I have a userform that deletes some data but it takes a long time. I'm trying to use a progress bar to let users know its working. I have a progress bar userform that I'm already using but I cant get it to work inside the userform.

The code inside userform2 that deletes info is: Basically it looks at option buttons then clears content of 6 ranges depending on the option button selected.

Private Sub clear_Click()
Dim i As Integer
 
        
    If OptionButton1.Value = True Then '  clears data 1 if 1 optionbutton clicked
        With Worksheets("import data")
            .Range("a2:a71821").ClearContents
            .Range("w2:ad1947").ClearContents
            .Range("ag2:an1947").ClearContents
            .Range("aq2:ax1947").ClearContents
            .Range("ba2:bh1947").ClearContents
            .Range("Bl2:Bs94").ClearContents
        End With
        Sheet7.Cells.ClearContents
        Sheet14.Range("c5:e10").ClearContents
        Call OptionButton1_Click 'resets text display after clearing data
        
    ElseIf OptionButton2.Value = True Then
        With Worksheets("import data")
            .Range("b2:b71821").ClearContents
            .Range("w1950:ad3895").ClearContents
            .Range("ag1950:An3895").ClearContents
            .Range("aq1950:ax3895").ClearContents
            .Range("ba1950:bh3895").ClearContents
            .Range("Bl97:Bs189").ClearContents
        End With
        Sheet14.Range("c12:e17").ClearContents
        Call OptionButton2_Click
        
            
     ElseIf OptionButton3.Value = True Then
         With Worksheets("import data")
            .Range("c2:c71821").ClearContents
            .Range("w3898:ad5843").ClearContents
            .Range("ag3898:An5843").ClearContents
            .Range("aq3898:ax5843").ClearContents
            .Range("ba3898:bh5843").ClearContents
            .Range("Bl192:bs284").ClearContents
            End With
        Sheet14.Range("c18:e23").ClearContents
        Call OptionButton3_Click
            
    ElseIf OptionButton4.Value = True Then
        With Worksheets("import data")
            .Range("D2:D71821").ClearContents
            .Range("w5846:ad7791").ClearContents
            .Range("ag5846:An7791").ClearContents
            .Range("aq5846:ax7791").ClearContents
            .Range("ba5846:bh7791").ClearContents
            .Range("Bl287:Bs379").ClearContents
        End With
        Sheet14.Range("c24:e29").ClearContents
        Call OptionButton4_Click
       
    
    ElseIf OptionButton5.Value = True Then
        With Worksheets("import data")
            .Range("E2:E71821").ClearContents
            .Range("w7794:ad9739").ClearContents
            .Range("ag7794:An9739").ClearContents
            .Range("aa7794:ax9739").ClearContents
            .Range("ba7794:bh9739").ClearContents
            .Range("Bl382:Bs474").ClearContents
        End With
        Sheet14.Range("c30:e35").ClearContents
        Call OptionButton5_Click
    
    ElseIf OptionButton6.Value = True Then
         With Worksheets("import data")
            .Range("f2:f71821").ClearContents
            .Range("w9742:ad11687").ClearContents
            .Range("ag9742:An11687").ClearContents
            .Range("aq9742:ax11687").ClearContents
            .Range("ba9742:bh11687").ClearContents
            .Range("Bl447:Bs569").ClearContents
        End With
        Sheet14.Range("c36:e41").ClearContents
        Call OptionButton6_Click
        
    
    ElseIf OptionButton7.Value = True Then
        With Worksheets("import data")
            .Range("g2:g71821").ClearContents
            .Range("w11690:ad13635").ClearContents
            .Range("ag11690:An13635").ClearContents
            .Range("aq11690:ax13635").ClearContents
            .Range("ba11690:bh13635").ClearContents
            .Range("Bl572:Bs664").ClearContents
        End With
        Sheet14.Range("c42:e47").ClearContents
        Call OptionButton7_Click
        
    
    ElseIf OptionButton8.Value = True Then
        With Worksheets("import data")
            .Range("H2:H71821").ClearContents
            .Range("w13638:ad15583").ClearContents
            .Range("ag13638:An15583").ClearContents
            .Range("aq13638:ax15583").ClearContents
            .Range("ba13638:bh15583").ClearContents
            .Range("Bl667:Bs759").ClearContents
        End With
        Sheet14.Range("c48:e53").ClearContents
        Call OptionButton8_Click
       
    
    ElseIf OptionButton9.Value = True Then
        With Worksheets("import data")
            .Range("i2:i71821").ClearContents
            .Range("w13638:ad17531").ClearContents
            .Range("ag13638:An17531").ClearContents
            .Range("aq13638:ax17531").ClearContents
            .Range("ba13638:bh17531").ClearContents
            .Range("Bl762:Bs854").ClearContents
        End With
        Sheet14.Range("c54:e59").ClearContents
        Call OptionButton9_Click
        
    
    ElseIf OptionButton10.Value = True Then
        With Worksheets("import data")
            .Range("j2:j71821").ClearContents
            .Range("w17534:ad19479").ClearContents
            .Range("ag17534:An19479").ClearContents
            .Range("aq17534:ax19479").ClearContents
            .Range("ba17534:bh19479").ClearContents
            .Range("Bl857:Bs949").ClearContents
        End With
        Sheet14.Range("c60:e65").ClearContents
        Call OptionButton10_Click
        
    
    ElseIf OptionButton11.Value = True Then
        With Worksheets("import data")
            .Range("K2:K71821").ClearContents
            .Range("w19482:ad21427").ClearContents
            .Range("ag19482:An21427").ClearContents
            .Range("aq19482:ax21427").ClearContents
            .Range("ba19482:bh21427").ClearContents
            .Range("Bl952:Bs1044").ClearContents
        End With
        Sheet14.Range("c66:e71").ClearContents
        Call OptionButton11_Click
        
        
    ElseIf OptionButton12.Value = True Then
       With Worksheets("import data")
           .Range("L2:L71821").ClearContents
           .Range("w21430:ad23375").ClearContents
           .Range("ag21430:An23375").ClearContents
           .Range("aq21430:ax23375").ClearContents
           .Range("ba21430:bh23375").ClearContents
           .Range("Bl1047:Bs1139").ClearContents
       End With
       Sheet14.Range("c72:e77").ClearContents
       Call OptionButton12_Click
       
       
    ElseIf OptionButton13.Value = True Then
        With Worksheets("import data")
            .Range("M2:M71821").ClearContents
            .Range("w23378:ad25323").ClearContents
            .Range("ag23378:An25323").ClearContents
            .Range("aq23378:ax25323").ClearContents
            .Range("ba23378:bh25323").ClearContents
            .Range("Bl1142:Bs1234").ClearContents
        End With
        Sheet14.Range("c78:e83").ClearContents
        Call OptionButton13_Click
        
        
    ElseIf OptionButton14.Value = True Then
        With Worksheets("import data")
            .Range("N2:N71821").ClearContents
            .Range("w25326:ad27271").ClearContents
            .Range("ag25326:An27271").ClearContents
            .Range("aq25326:ax27271").ClearContents
            .Range("ba25326:bh27271").ClearContents
            .Range("Bl1237:Bs1329").ClearContents
        End With
        Sheet14.Range("c84:e89").ClearContents
        Call OptionButton14_Click
       
        
    ElseIf OptionButton15.Value = True Then
        With Worksheets("import data")
            .Range("O2:O71821").ClearContents
            .Range("w27274:ad29219").ClearContents
            .Range("ag27274:An29219").ClearContents
            .Range("aq27274:ax29219").ClearContents
            .Range("ba27274:bh29219").ClearContents
            .Range("Bl1332:Bs1424").ClearContents
        End With
        Sheet14.Range("c90:e95").ClearContents
        Call OptionButton15_Click
        
        
    ElseIf OptionButton16.Value = True Then
        With Worksheets("import data")
            .Range("P2:P71821").ClearContents
            .Range("w29222:ad31167").ClearContents
            .Range("ag29222:An31167").ClearContents
            .Range("aq29222:ax31167").ClearContents
            .Range("ba29222:bh31167").ClearContents
            .Range("Bl1427:Bs1519").ClearContents
        End With
        Sheet14.Range("c96:e101").ClearContents
        Call OptionButton16_Click
        
    
    ElseIf OptionButton17.Value = True Then
       With Worksheets("import data")
            .Range("Q2:Q71821").ClearContents
            .Range("w31170:ad33115").ClearContents
            .Range("ag31170:An33115").ClearContents
            .Range("aq31170:ax33115").ClearContents
            .Range("ba31170:bh33115").ClearContents
            .Range("Bl152:Bs1614").ClearContents
        End With
        Sheet14.Range("c102:e107").ClearContents
        Call OptionButton17_Click
       
        
    ElseIf OptionButton18.Value = True Then
       With Worksheets("import data")
           .Range("R2:R71821").ClearContents
           .Range("w33118:ad35063").ClearContents
           .Range("ag33118:An35063").ClearContents
           .Range("aq33118:ax35063").ClearContents
           .Range("ba33118:bh35063").ClearContents
           .Range("Bl1617:Bs1709").ClearContents
       End With
       Sheet14.Range("c108:e113").ClearContents
       Call OptionButton18_Click
      
       
    ElseIf OptionButton19.Value = True Then
        With Worksheets("import data")
           .Range("S2:S71821").ClearContents
           .Range("w35066:ad37011").ClearContents
           .Range("ag35066:An37011").ClearContents
           .Range("aq35066:ax37011").ClearContents
           .Range("ba35066:bh37011").ClearContents
           .Range("Bl1712:Bs1804").ClearContents
       End With
       Sheet14.Range("c114:e119").ClearContents
       Call OptionButton19_Click
       
    
    ElseIf OptionButton20.Value = True Then
       With Worksheets("import data")
            .Range("T2:T71821").ClearContents
            .Range("w37014:ad38959").ClearContents
            .Range("ag37014:An38959").ClearContents
            .Range("aq37014:ax38959").ClearContents
            .Range("ba37014:bh38959").ClearContents
            .Range("Bl1807:Bs1899").ClearContents
        End With
        Sheet14.Range("c120:e125").ClearContents
        Call OptionButton20_Click
                  
    End If
   
      
End Sub

The progress bar userform1 code is

 UserForm1.Show ' show progress form
    Dim pctCompl As Integer
    pctCompl = 0
    UserForm1.Text.Caption = pctCompl & "% Completed"
    UserForm1.Bar.Width = pctCompl * 2
    UserForm1.Repaint
    
    Application.ScreenUpdating = False
  
   
    
           
        Application.ScreenUpdating = True
        UserForm1.LabelTitle = "Processing " 
        pctCompl = pctCompl + 16.6
        UserForm1.Text.Caption = pctCompl & "% Completed"
        UserForm1.Bar.Width = pctCompl * 2
        UserForm1.Repaint
        Application.ScreenUpdating = False

The progress bar was used in another part of the program so im trying to retrofit it to fit the Clear_click() code above. I know its not correct yet but i cant even start to debug it because it errors on the first line "userform1.show". Any help would be greatly appreciated.

**BadlySpelledBuoy** · 05-22-2018, 03:40 PM

When it errors out on the UserForm1.Show line and highlights it, press F8 repeatedly to step through the code line by line.
It will continue through the code and eventually you will get to the line that causes the issue.

Let us know what that line is and if possible attach a desensitized version of your file. Always much easier to help if we can see that.

BSB

**kevinu** · 05-22-2018, 05:41 PM

Here is the sample workbook. I had to erase the old progress bar code but the userform is there as userform1. when you open it click on the "file station" button and userform2 will open. I would like to have a progress bar that shows progress when the red "Clear data set" and "Clear all data" command buttons are activated\

Edit: Thx Norie that worked.. Still need to make it work but at least it runs now

If you have any suggestions on how to integrate that into the clear data codes i'd love the advise. I need it to update the progress bar after each range has been cleared... 6 or 7 total ranges. Not sure how to do that yet.