Can a macro select items in another macro's UserForm's ListBox ?

[MarieBocc]

Hello everyone,

I have a macro that I wrote that duplicates a selected range at some position in my worksheet. The position is selected based on informations given by the user through an UserForm's ListBox. This macro works well and I would now like to determine its runtime depending on the insertion position in the worksheet.
I would like to write a second macro that would run the first one, select a different item in the ListBox of the UserForm each time and then write down the execution's duration in a worksheet to calculate the average and check the evolution as the insertion position increases.

But is this even possible ? How would the second macro know the first's UserForm is active and take control over it ?

Any ideas ?

Thank you for reading,

Marie

 

[pbornemeier]

If I understand what you are asking, I believe this would work:
The second macro would actually have to run first, it would call the first macro which would show the input form. Once the user clicked OK on the form (or filled the required input boxes), the second macro would read the input boxes from the form, process the collected data, save the duration and either call the first macro again to collect more info or pull other info from the from the user form.

 

[MarieBocc]

Thank you very much for your answer,

This is what I am curently doing.
My problem is that the user intervention in this method is very repetitive. In fact, there is a listbox of X items and my test consists of runing T * X times the first macro (the one with the userform). This way I'll be able to select T times each Item in the listbox and get enough values for my calculations to be pertinent.

macro2 goes like this :

For t in 1:T
For x in 1:X
[run macro1
'''manually select the x-th item in listBox
macro1 finishes running]
cells(x,t).value = macro1's duration
code to cancel what was changed by macro1
x++
t++

So it really feels like this "'''

manually select the x-th item in listBox" could be automated and it would save me an awfull lot of time and efforts considering X and T. But I have no idea how macro 2 could select an Item in another macro's userform nor it it is even possible since
[run macro1
'''manually select the x-th item in listBox
macro1 finishes running]
Is actually a single lign of code in my macro2. Or should I maybe write a third macro to select it ?

Marie​

 

[pbornemeier]

UserForm1 containing ListBox1, CommandButton1 (to start processing)
Workbook with listbox info in Sheet1!A1:Ax and blank Sheet2 to hold timer data

Edit:
Sub ProcessSelect() to hold your processing code

'Standard Module
Option Explicit

Sub ShowUserForm()
    
    UserForm1.Show
    
End Sub

Sub ProcessSelected(varInput As Variant)

    'Listbox select processing code here

End Sub

'This code in UserForm1 Module
Option Explicit

Private Sub UserForm_Initialize()
    
    'Populate listbox from Sheet1!Range(A1).CurrentRegion, show form
    
    Dim rngInput As Range
    Dim rngCell As Range
    Dim lIndex As Long
    
    Debug.Print "init"
    
    Set rngInput = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion.Cells
    ReDim aryInput(0 To rngInput.Cells.Count - 1)
    For Each rngCell In rngInput
        aryInput(lIndex) = rngCell.Value
        lIndex = lIndex + 1
    Next
    
    UserForm1.ListBox1.List = aryInput   '0:ListCount-1 array
    
    'UserForm_Activate code runs
    
End Sub

Private Sub CommandButton1_Click()
    'Select listbox items; call processing code lTimesToRun times; send timing results to sheet2
    
    Const lTimesToRun As Long = 100
    Dim lXIndex As Long
    Dim lTIndex As Long
    Dim dteStart As Date
    Dim varListBoxItem As Variant
    
    Debug.Print "act"

    For lXIndex = 0 To UserForm1.ListBox1.ListCount - 1
        'Select item in listbox
        UserForm1.ListBox1.Selected(lXIndex) = True
        varListBoxItem = UserForm1.ListBox1.List(lXIndex)
        Application.StatusBar = "Processing: " & varListBoxItem
        For lTIndex = 1 To lTimesToRun
            dteStart = Now()
            '------------------
            ProcessSelected varListBoxItem
            '------------------
            ThisWorkbook.Worksheets("Sheet2").Cells(lXIndex + 1, lTIndex).Value = Now() - dteStart
        Next
        UserForm1.ListBox1.Selected(lXIndex) = False
    Next
    
    
    Application.StatusBar = False
    
    MsgBox "Processing completed"
    
    Me.Hide

End Sub

'If you need more accurate deltas for your times, review this page:
' https://docs.microsoft.com/en-us/windows/win32/sysinfo/acquiring-high-resolution-time-stamps
'This shows one vba implementation of the above
' https://www.mrexcel.com/forum/excel-questions/826673-high-res-vba-timer.html

 

[pbornemeier]

If you want to run the code on selected cells rather than those about Sheet1!Range(A1) then
Change

Set rngInput = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion.Cells

To

 Set rngInput = Selection.Cells

 

[pbornemeier]

Ensure the cells to be processed are selected before starting the code.