Integrating Microsoft ProgressBar Control 6.0 (SP6) to a user form macro

Neltu

New Member
Joined
Jan 28, 2009
Messages
25
Hello,

I am trying to make the Progress bar control work while my macro is running in the workbook, but i am not sure where i should put the code, and if i need to add anything. I have looked at several web pages with examples, but could not for the life of me integrate them without severely disasterous results to the original data.

Basically the meat of the code for my macro is as follows (With expert help from these forums of course ;)):

Code:
Public Sub TSGen(IDKey As String)
        Sheets(2).Activate
        Range("A12").Activate
        shIndex = 2
        If CheckBox37 Then
            shIndex2 = Sheets.Count - 1
        Else: shIndex2 = Sheets.Count - 3
        End If
        Do Until shIndex = shIndex2
 
            If ActiveCell.Value = IDKey Then
                ActiveCell.EntireRow.Copy
                Sheets(Sheets.Count).Activate
                Range("A12").Select
                Do
                    If IsEmpty(ActiveCell) = False Then
                        ActiveCell.Offset(1, 0).Select
                    End If
                Loop Until IsEmpty(ActiveCell) = True
                ActiveCell.PasteSpecial
            End If
            Sheets(shIndex).Activate
            ActiveCell.Offset(1, 0).Select
            If CheckBox37 Then
                If IsEmpty(ActiveCell) = True And shIndex < Sheets.Count - 1 Then
                    shIndex = shIndex + 1
                    Sheets(shIndex).Activate
                    Range("A12").Activate
                End If
            Else
                If IsEmpty(ActiveCell) = True And shIndex < Sheets.Count - 3 Then
                    shIndex = shIndex + 1
                    Sheets(shIndex).Activate
                    Range("A12").Activate
                End If
 
        Loop
 
End Sub

I drew a progress bar using the ProgressBar control from the toolbox and added it to the UserForm that the user will select the checkboxes on and hit the button to activate the macro and it inputted the following code:

Code:
Private Sub ProgressBar1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
End Sub

This code was inserted automatically after my Sub for CommandButton1 but before the Sub for CommandButton 2

I am still completely novice when it comes to VBA so this seemed like a simpler way to add a progress bar.

What code do i need to add to the ProgressBar to integrate it?

or

Do i need break the code down into bits and integrate it into the macro as i have seen others suggest in other posts for the from scratch progress bar codes?

Is it possible to just add this to the bottom before Loop or End Sub?

Any suggestions will be greatly appreciated and thanks for your time in advance.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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