selecting open workbooks

[bkelly]

If I have made this too succinct, please let me know and I will elaborate.

The user is presented with a form. It has an option to run code that references a particular workbook. That workbook might be open or it might not. The workbook name can change, but the worksheet names cannot. I can deal with the workbook not being open and create an open window. What can I do to provide the user the ability to select one of, say, three workbooks that are already open?

Right now I am running the code in debug. I step into the code that with the dbugger then manually select the workbook making it the active workbook. The code sets a workbook reference to the active workbook then sets worksheet references via that workbook reference. Obviously I don't want the user to mess around in the debugger.

Is this workable?

 

[skull_eagle]

Hi,

I'm relatively new to this game but thought I would kick in.

Could you not allow the user to choose using a check box on the userform which spreadsheet they would like to open?

If you give each workbook it's own tick box then it would just be a case of - if this box is selected then open the corresponding workbook.

 

[bkelly]

The macros have several steps to produce the final product. The first phase validates the user input data and does some preprocessing. The second phase builds the desired workbook. The third phase validates the output. (The produced workbook is read by another vendor product and they do not do a good job of error detection and recovery.)

The user may process some data and my VBA code might detect an error. They can fix the error immediately. The user already has the workbook open. I don't want to make them close the workbook just so they can open it agan.

 

[bkelly]

Maybe this will help. The subroutine that opens the workbook runs a bit of code that looks something like this:

Function DWE( ws_name as string ) as Boolean
‘ DWE = Does Workseet Exist
Dim checksheet
On Error Resume Next
Set checksheet = sheets ( ws_name)
On error GoTo 0
If checksheet is Nothing then
   DWE = TRUE
ELSE 
   DWE = FALSE
END IF
End Function

(I cannot copy from my working computer to here so please excuse any minor errors.)

If the workbook is open and in focus, then it returns a true. If the workbook is not open it returns false. That is good. If the workbook is not in focus, it returns false. How can I change this to find the worksheet of the workbook when it is not in focus?

 

[xenou]

Hi,

I took a quick stab at this. It seems you want to loop through the workbooks and look for the sheet.

I consider this potentially vulnerable to cases where a worksheet of the same name might exist in more than one workbook. My personal preference is *not* to write code for workbooks of unknown filenames - either have the user choose the file with a file picker and thus identify the name precisely, or exercise control over naming.

Another subtle bug could be hidden workbooks with more duplicates of the sheet name being sought. I often have 5 or 6 hidden workbooks/addins going so I'm particularly aware of this problem. But if your sought sheet name is rather unusual or has some dates embedded in it I guess you will be fairly safe.

So, what I figured was that the function should return the Name of the workbook where the sheet is found. It should also give warning of an error if it finds the sheet in more than one workbook... EDIT: forgot to check upper/lower casing. I think it will work if you have the wrong case ("sheet1"/"Sheet1") but perhaps check that ...

Find sheet in workbook(s) code:

[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    
    x = DWE("FooBar")
    [COLOR="Navy"]If[/COLOR] x <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] x <> "#COUNTERROR#" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] x [COLOR="SeaGreen"]'//workbook name[/COLOR]
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "ERROR - sheet found in more than one workbook."
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "Sheet not found."
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------[/COLOR]
[COLOR="Navy"]Function[/COLOR] DWE(sName [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] Workbook
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] iCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ret [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] wb [COLOR="Navy"]In[/COLOR] Workbooks
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ws [COLOR="Navy"]In[/COLOR] wb.Worksheets
            
            s = ""
            [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
            s = wb.Sheets(sName).Name
            [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
            
            [COLOR="SeaGreen"]'Return WB name, or error value if sheet is found in more than one WB[/COLOR]
            [COLOR="Navy"]If[/COLOR] s <> "" [COLOR="Navy"]Then[/COLOR]
                ret = wb.Name
                iCount = iCount + 1
                [COLOR="Navy"]If[/COLOR] iCount > 1 [COLOR="Navy"]Then[/COLOR]
                    ret = "#COUNTERROR#"
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        
        [COLOR="Navy"]Next[/COLOR] ws
    [COLOR="Navy"]Next[/COLOR] wb
    
    DWE = ret
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

 

[bkelly]

I will try that. what is this: "#COUNTERROR#"
Help in Excel and in the VBA dissavow in knowledge of this.
A googl search turns up a bunch of pages but no explanation. Even when I include +"COUNTERROR" it still returns pages without the phrase.

 

[xenou]

Ahh. #COUNTERROR# is an error of my own devising. Since we return a string, it could be anything: #SNICKERDOODLE# or "#@$!&^#"

If you wished to be perfectly accurate, it should be value that includes a character that is invalid in a sheet name, thus leaving three possible return values: an empty string (no sheet found), a sheet name (sheet found), and an error return value (a string that cannot be a sheet name).

ξ