How/Where to insert Userforms in Macro Code

[LeanneBG]

Hi All! Anyone here knows how and where should i insert a code so i can add the userform i created? I wanted to include this in the code so that i'm aware of the progress of the macro. For background: the macro is creating new workbooks (or splitting the files perse) depending on the data i have. Thus, i want to include this userform so i will be aware of the progress/status. Below is my code for the splitting part. I have 2 userforms, UserForm1 is for the progress, the other one (Userform2) is for when the macro is done running. ANy help is appreciated. Thanks! Here's the link for the userforms image:

https://www.dropbox.com/s/r8w925655bp3294/userform1.PNG?dl=0 - Userform1
https://www.dropbox.com/s/1tq5nwzrhgrn0yx/userform2.PNG?dl=0 - Userform2

Sub ParseGroups()
'JBeaucaire (11/11/2009)
'Based on column A, data is filtered to individual workbooks
Dim LR As Long, i As Long, MyCount As Long, MyArr
Dim ws As Worksheet, wsNew As Worksheet
Dim Path As String
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Path = Worksheets("Menu").Range("A10")
If Dir(Path & "/Reports", vbDirectory) = "" Then
MkDir Path & "/Reports"
Path2 = Path & "/Reports/"
End If
Set ws = Sheets("Raw") 'edit to your data sheet name
ws.Activate 'insure data sheet is active
'Store the bottom row of data as a variable
LR = Range("A" & Rows.Count).End(xlUp).Row
'Create a unique list of values from column A
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("BB1"), Unique:=True
'Sort the list alphabetically
Columns("BB:BB").Sort Key1:=Range("BB2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Put the list into an array in memory
MyArr = Application.WorksheetFunction.Transpose(Range("BB2:BB" & Rows.Count).SpecialCells(xlCellTypeConstants))
Range("BB:BB").Clear 'clear the column of values created so sheet is pristine
Range("A:A").AutoFilter 'Turn on the autofilter
For i = 1 To UBound(MyArr) 'loop through array values one at a time
'Filter column A by the current value
Range("A:A").AutoFilter Field:=1, Criteria1:=MyArr(i)
'Create a new blank sheet named for the current array value
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MyArr(i)
ws.Activate
'Copy current filtered rows to new sheet, values only and formatting preserved
Range("B1:W" & LR).EntireColumn.Copy
Sheets(MyArr(i)).Range("A1").PasteSpecial xlPasteValues
Sheets(MyArr(i)).Range("A1").PasteSpecial xlPasteFormats
'Count how many rows were moved for message later
MyCount = MyCount + Sheets(MyArr(i)).Range("A" & Rows.Count).End(xlUp).Row - 1
'Tighten up appearance
Sheets(MyArr(i)).Columns.AutoFit
'Move new sheet to workbook of its own
Sheets(MyArr(i)).Move
Call CreatePivots
'Save new workbook with array value as name, then close
ActiveWorkbook.SaveAs Path2 & MyArr(i) & ".xlsx"
ActiveWorkbook.Close False
'reset the autofilter
Range("A:A").AutoFilter Field:=1
'End If
Next i 'Loop to next array value
'Turn off autofilter
ActiveSheet.AutoFilterMode = False
'Compare count of rows copied to rows in database, report the results
LR = LR - 1
MsgBox "Rows with data: " & LR & vbLf & "Rows copied to other sheets: " & MyCount & vbLf & "Hope they match!!"
Application.ScreenUpdating = True
Sheets("Raw").Select
Sheets("Raw").Cells.ClearContents
Sheets("Raw").Cells.ClearFormats
Sheets("Menu").Select
End Sub

 

[ExcelSpeedster]

I've made some changes to your file: https://1drv.ms/x/s!AuOvgkpcE66BgpRoqmCwxOZnEZhEjg

Changes:
1. The Create Reports button now executes a new Sub: Run_ParseGroups.
2. The Run_ParseGroups Sub shows UserForm3.
3. UserForm3 kicks off the ParseGroups Sub on activation.

 

[LeanneBG]

I've made some changes to your file: https://1drv.ms/x/s!AuOvgkpcE66BgpRoqmCwxOZnEZhEjg

Changes:
1. The Create Reports button now executes a new Sub: Run_ParseGroups.
2. The Run_ParseGroups Sub shows UserForm3.
3. UserForm3 kicks off the ParseGroups Sub on activation.

Hi ExcelSpeedster, i cannot open the file because it exceeds the size. Can you please forward it via email instead please? gerodiasleanne@gmail.com

 

[ExcelSpeedster]

I've emailed you the file.

 

[LeanneBG]

I've emailed you the file.

Thanks a lot! Is there a way to just see the userform and not the updating screen? Also, can i insert like a done button in the userform? because after finishing the splitting, userform is not disappearing on my screen..

 

[ExcelSpeedster]

You're welcome.

Is there a way to just see the userform and not the updating screen?

You can disable screen updating by setting

Application.ScreenUpdating = False

at the beginning of your ParseGroups proc, then set it back to True at the end of the proc. The macro should run much quicker like this.

Also, can i insert like a done button in the userform? because after finishing the splitting, userform is not disappearing on my screen..

Yes, it would be good practice to have a Done button on the UserForm to allow the user to close the form. Place the button on the form, double click on it, then in the Click event of the button, paste this statement

Unload UserForm3

 

[LeanneBG]

You're welcome.

You can disable screen updating by setting

Application.ScreenUpdating = False

at the beginning of your ParseGroups proc, then set it back to True at the end of the proc. The macro should run much quicker like this.

Yes, it would be good practice to have a Done button on the UserForm to allow the user to close the form. Place the button on the form, double click on it, then in the Click event of the button, paste this statement

Unload UserForm3

it works! Thanks a lot Excelspeedster! :D