Performing code on worksheet selected by user in user form combo box

[Coldfire46]

Hi all,

I've currently written some code to search through all worksheets within a workbook until a certain criteria is met (the criteria is if the title of the worksheet contains the work 'Summary'; when this criteria is met specific values are added to an array. My code works fine, but I would like to remove the element of searching through all the sheets, and add in a user form, so that the user can select the relevant sheet, and the code will search through the sheet that the user has selected and add values to the array. I'm not sure how to do this; can anyone offer any help?

I've included part of the existing code to illustrate the current functionality:

For Each ws In wbk.Worksheets
    ws_string = ws.Name
        If Left(ws_string, 7) = "SUMMARY" Or Left(ws_string, 7) = "Summary" Or Left(ws_string, 7) = "summary" Then
        EndRow = ws.Range("A:A").Find(what:="*", searchdirection:=xlPrevious, after:=[A1]).Row '& Rows.Count
        For y = 15 To EndRow
            For x = 0 To ColOffset
                    If ws.Cells(y, 7).Offset(0, x).Value <> 0 Then
                    Counter = Counter + 1
                    ReDim Preserve InfoAr(1 To 7, 1 To Counter)
                    InfoAr(Desc, Counter) = ws.Cells(y, 3).Value   ' Desc
                    InfoAr(WBS, Counter) = ws.Cells(y, 6).Value     'WBS
                    InfoAr(Resource, Counter) = ws.Cells(13, 7).Offset(0, x).Value  'Resource
                    InfoAr(Total, Counter) = ws.Cells(y, 7).Offset(0, x).Value * ws.Cells(y, 5) 'Total
                    InfoAr(Bidder, Counter) = ws.Cells(EndRow, 2).Offset(4, 0).Value 'Bidder
                    InfoAr(TaskID, Counter) = "OPS" & Counter
                    InfoAr(OBS, Counter) = "OPS"
                End If
            Next x
        Next y
    End If
Next ws

 

[Stubble Kid]

The few lines below may the simplest to get a worksheet name and to validate it before the rest of your code is run. This avoids having to create a specific user form for the job (if you already have a user form as part of your project, obviously a different solution would be appropriate):

LBL_GetName:
    ws_string = InputBox("Provide the name of the Worksheet to be searched:", "Sheet Name", ActiveSheet.Name)
    For Each ws In ThisWorkbook.Sheets
        If UCASE(ws_string) = UCASE(ws.Name) Then GoTo LBL_GotWorksheetName
    Next ws
    If MsgBox("Please provide the correct name of a worksheet", vbRetryCancel Or vbExclamation Or vbDefaultButton1, "Invalid Worksheet Name") = vbRetry Then GoTo LBL_GetName
    Exit Sub
    
LBL_GotWorksheetName:


    ' ...rest of your code .....
    ' ...
    ' ...