VBA to propmt me to manually choose an open workbook

Blunder1

Active Member
Joined
Jun 2, 2010
Messages
250
Hi,

I have a code that looks for an open workbook that contains a certain string in the name. Sometimes this workbook maybe called something random. Can i add to the code so of the wb cant be found then it will prompt me to manually choose a workbook that is currently open?

Below is the code to look for the open workbook

Code:
Private Function SelectWB(ByVal strName As String, ByRef wbSelected As Excel.Workbook) As Boolean
    Dim wb As Excel.Workbook
     
    For Each wb In Application.Workbooks
         
        If InStr(wb.Name, strName) > 0 Then
            Set wbSelected = wb
            SelectWB = True
            Exit Function
        End If
    Next
     
End Function
 
 Application.ActiveSheet.Shapes("button 85").Delete
     
Sub WB()
    wbList = Array("AES")
         For iTemp = 0 To UBound(wbList)
    If SelectWB(wbList(iTemp), wbSelected) Then
    wbSelected.Activate
        End If
    Next
 
End Sub

Thank in advance

Blunder
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,224,270
Messages
6,177,574
Members
452,784
Latest member
talippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top