Progress bar while adding data from Userform

Question

I want a progress bar while I add data from a userform.

When I click the add button the progress bar should run while the "adding data" code runs.
And finally I want to a message box to say the process is successful.

Code to add data from userform to Excel worksheet.

Private Sub cmbAddItem_Click()
    Dim sht As Worksheet
    Dim LastRow As Long
    Set sht = ThisWorkbook.Sheets("ITEMS")
    Dim rng As Range
    Set rng = sht.Range("ITEM_LIST").CurrentRegion
    
    LastRow = rng.End(xlDown).Row + 1
    
    With sht
        .Cells(LastRow, 3) = lblItemId
        .Cells(LastRow, 4) = txtItemname
        .Cells(LastRow, 5) = lblDate
        .Cells(LastRow, 6) = txtItemDes
        .Range("ITEM_LIST").Rows(rng.Rows.Count).Font.Name = "Franklin Gothic Book"
        .Range("ITEM_LIST").Rows(rng.Rows.Count).HorizontalAlignment = xlCenter
    End With
    
    '--------------------------- load listbox or refresh listbox by class
    loaditemlist
    
    Call ItemCode ' update item code label for next item
    
    lblTotItem = Application.CountA(Range("ITEM_CODE")) ' To Refresh
    
    ThisWorkbook.Save
End sub

The answer I gave in this question should work. Although looking at your code it looks like it should execute fast enough not to need a progress bar. [How to display progress bar when userform is loading in VBA excel](https://stackoverflow.com/questions/31052415/how-to-display-progress-bar-when-userform-is-loading-in-vba-excel/31056031#31056031) — Darren Bartrup-Cook, Jul 31 '22 at 15:17
It gives me the error 402 "must close or hide topmost modal from first" ---------------ERR_HANDLE: Err.Clear ProgressBar.Hide — charly1988, Jul 31 '22 at 18:08
Yes, you'll need to set the `ShowModal` property of the main form to false. It does mean the user can then click on the worksheet / other forms while the main form is active. May not work for you in that case. This MrExcel link may help: [How can I change a Modal Userform from Modal to Modeless at run time](https://www.mrexcel.com/board/threads/how-can-i-change-a-modal-userform-from-modal-to-modeless-at-run-time.408356/) — Darren Bartrup-Cook, Aug 01 '22 at 08:05

xlmaster · Answer 1 · 2022-08-01T19:35:35.003

0

Create label in userform, change Name to "progressBar". Clear caption label1.. put whitespace (" " ). You can look for website for further detailed description how to create more colorful https://www.wallstreetmojo.com/vba-progress-bar/

Sub yoursubroutine()
Userform.progressBar.Width=0

'When it will start reading your code width of label equals 0. Gradually, 'line by line you will add up as per you see it properly in ratios.

*your code*
Userform.progressBar.Width=30
Userform.progressBar.BackColor=vbBlue
*your code*
Userform.progressBar.Width=60
Userform.progressBar.BackColor=vbBlue
*your code*
Userform.progressBar.Width=85
Userform.progressBar.BackColor=vbBlue
*your code*
Userform.progressBar.Width=100
Userform.progressBar.BackColor=vbBlue

End Sub

edited Aug 01 '22 at 19:35

answered Jul 31 '22 at 17:19

xlmaster

659
7
23

Thank you for you answer.. but as i am new to this coding world... could you please help me by placing your codes between my work. – charly1988 Jul 31 '22 at 17:55
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 01 '22 at 06:24

Progress bar while adding data from Userform

1 Answers1