VBA InputBox Issue

Your Comments: And I tried to implement progress bar in Scenario 1 code

I imported the form into the macro

Both Scenario 1 code and Scenario 2 code are integrated into the one project. Both Scenarios run one after the other and produce the full Output Workbooks. Therefore, importing the Userform into an earlier version of the code will not work because many changes have been made since Scenario 1. Simply use the new code macro as is.

• The modified code has only been done for the Progress Bar project example.
• Code modified to work with the new Category codes.
• Valid Category Codes in a lookup table on Main Menus sheet. Additional categories can be added but then additional examples of the headers will be required in Categories Templates.
• First up the code validates all of the category codes in wsSource.xlsx against the table in columns Q:R of main Menu sheet.
• If any codes in wsSource are not found in the Lookup table then the user is notified so that they need to be fixed in the source file. The code copies the Category codes from wsSource to Column "E" in Unique Lists worksheet and "Remove duplicates" is applied to the list. Then the code loops through the Unique List and tests if they are in the Lookup Table on Main Menu Sheet. If any do not exist in the Lookup then "Invalid" is inserted in column "F" and the code halts with a message to the user who must then fix the codes in the Source Data file.
• Suggest for testing to change some Category codes in the source file so they do not match the table in Main Menu and then run the code and observe result with instructions to correct the source data.
• It is essential to ensure that only valid Category Codes appear in the wssource data otherwise you will run into problems with missing data.

The initial "Create Output Workbooks" button has been left on Main Menu but you will see that it opens the Userform and then the code is run from the button on the Userform. If you want then you can delete the button on Main Menu and then go to ThisWorkbook VBA module and un-comment the code "Call ShowUserform" and the Userform will automatically open when the workbook is opened.

After testing you might want to apply your artistic abilities to create an artistic Main Menu worksheet and userform etc that contains your work logo etc but I will leave that to you.

Feel free to get back to me if any more questions.

I am hoping after this exercise that you will appreciate the importance of "System Analysis". This is a process of getting the Users and Programmers together and work out exactly what is required for the system. It should take into account the source data plus examples of how the data is to be presented after automating. In addition it should take into account how the initial data will be entered together with how it can be validated at the earliest stage. In fact, the "System Analysis" normally is some 80% of the cost of the project and the actual writing of the code is the easy part once the programmer fully understands what is required.

Latest copy of the workbooks with the code at the following link. Note that wsSource.xlsx is now named wsSource2.xlsx because I have changed the categories to match the latest categories that you provided.

Latest Update with Progress Bar

It's okay to hardcode them in the array. If necessary I can edit the code. I feel hardcoding is the best option.

As per my knowledge those four headers may not change that often. If creating a small table is the best option we could do that.

And I tried to implement progress bar in Scenario 1 code 

I imported the form into the macro

Copied all the necessary code into the respective places within the sub procedure module. 

Sub ShowUserform()

    frmProgress.Show

End Sub

lngTotLoops = rngUnique.Columns(1).Cells.Count  'Total Unique Numbers in Worksheet "Unique List"

        strMsge1 = "Processing data." & vbCrLf & _

                "Please wait until completion." & vbCrLf & _

                "or Cancel to terminate processing."

    With frmProgress    'Initialize the Userform with required information.

        With .cmdClose

            .Caption = "Cancel"

            .Visible = True     'Makes the Cancel/Finished button visible on the Userform

        End With

        .lblMsge1 = strMsge1  'Display the message in the Progress Bar Userform

    End With

r = r + 1   'Count of Number of loops to be processed

        dteElapseTime = Now() - dteStartTime

        Call Progress(lngTotLoops, r, dteElapseTime)

Sub Progress(lngTotal As Long, lngProcessed As Long, dteProgress As Date)  'Note: In VBA Times are assigned to Date Variables

    Dim dblTxtWidth  As Double

    With frmProgress

        .txtProgress.Value = Format(lngProcessed / lngTotal, "00%")     'Calculate percentage processed

        .txtProgress.Width = .frameProg.Width * (lngProcessed / lngTotal)    'Calculate length of TextBox within Frame.

      .lblMsge2 = "Elapsed time = " & Format(dteProgress, "n") & " Mins " & Format(dteProgress, "s") & " Secs"

    End With

    DoEvents 'Force update Userform progress bar

End Sub

When I assign user form macro to a button and run it I see a blank screen only after completion of the run I see the user form as below. Can't see the progress in real time. If I click on Macro sheet in taskbar during the run I can see real time progress when it switches back to wsSource during the run again I see blank screen how to make it stay on top until completion. This does not happen in Scenario 2

Blank Screen

User Form

PS. I missed your replies before. I have gone through them now one of them mentions you are working on the files and other about HafizUsman4.

That is not my account and I am not sure who that is.

Are the category names ever likely to change in the future?

If they will always be fixed then we can hard code them into an array in the code.

However, if there is only a remote possibility that they will be changed in the future then we must set up a small table in the Macro workbook (probably on the Main Menu worksheet) so they can be edited. Will be like the screen shot below.

If required, can be password protected to restrict who can change. 

PS. In Category 4 is "Declrable" a typo error and should be "Declarable"

Great! Scenario 2 is completed. I may want to clear some question in the code before marking the thread as answered. 

While processing column W in wsSource. Code introduces line breaks. But in the categories I use Wrap? I feel there is no need to introduce line breaks only remove the leading part from Column W.  

And I don't have numbers in my Category. For sample data set I made up 1 to 4.

Those Categories have strings names. Like below.

RUOHS-0008 = Caterogy_1

RUACH-0260 = Caterogy_2

Rpo_70_sub = Caterogy_3

Sub_Restricted-Declrable = Caterogy_4

I have to edit the code now. For the loop part For lngCatNo = 1 To 4

Did not think you would use those numbers to loop lngCatNo = 1 To 4

Not sure if this effects the rest of the code. 

Instead of changing the whole code can I assign those strings to the 1 to 4

Caterogy_1 = RUOHS-0008 

Caterogy_2 = RUACH-0260

Caterogy_3 = Rpo_70_sub 

Caterogy_4 = Sub_Restricted-Declrable

Not sure if that helps. Will I be able to filter with RUOHS-0008 from wsSource if I assign Caterogy_1 = RUOHS-0008 

I tried 

    Dim Category_1 As Variant

    Dim Category_2 As Variant

    Dim Category_3 As Variant

    Dim Category_4 As Variant

    Category_1 = "RUOHS - 0008"

    Category_2 = "RUACH - 0260"

    Category_3 = "Rpo_70_sub"

    Category_4 = "Sub_Restricted-Declrable"

and changed the wsSource as well 

It did not work.

Could you suggest a better way to loop them.

Need to edit the below code.  

strCatCol = "Category_" & lngCatNo 'Creates string to match column header (eg "Category_1" with underscore)

'        strCatHeader = Replace(strCatCol, "_", " ")    'The Report header templace has a space in lieu of an underscore

       lngLastUsedRow = LastRow(wbOut.Worksheets("Categories").Cells)

        If lngLastUsedRow > 1 Then      'If Row 1 then no existing Category existing (First Category)

            lngLastUsedRow = lngLastUsedRow + 2 'Move 2 rows down from last used row (one row gap)

        End If

        Set rngCatTemplate = FindCatHeader(wbThis.Worksheets("Categories").Cells, strCatHeader) 'Finds the individual template header

        Set rngCatTemplate = rngCatTemplate.CurrentRegion   'Increase the range to include the entire current region

        rngCatTemplate.Copy Destination:=wbOut.Worksheets("Categories").Cells(lngLastUsedRow, "A")

In the Categories worksheets I have inserted N/A and merged the single blank row if no Category records exist.

View for the templates in Macro workbook changed to "Page Layout". Therefore, the View in the created Output workbooks is now "Page Layout".

In all workbooks, the first cell is selected in all worksheets and the first worksheet activated before saving.

Link below to zipped file containing 3 workbooks.

1. The older version of macro code without the Progress Bar,
2. The newer version of macro with the Progress Bar
3. The Source Workbook.

Zipped Workbooks with updated code