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

[Neltu]

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 ):

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:

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.

 

[Bill_Biggs]

I had a similar question once and got help froma particular member. In my case I was not able to get the progress bar to work becasue my original macro had no loops. But since you have a loop, the guy I contacted might be able to help you. Here is the original thread. Truby was the guys name I am pretty sure.

http://www.mrexcel.com/forum/showthread.php?t=337788&highlight=progress

Good luck.

 

[Neltu]

Thanks for the reply.

I'll take a look at that thread.