selecting open workbooks

bkelly

Active Member
Joined
Jan 28, 2005
Messages
465
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

skull_eagle

Board Regular
Joined
Mar 25, 2011
Messages
52
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

Active Member
Joined
Jan 28, 2005
Messages
465
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

Active Member
Joined
Jan 28, 2005
Messages
465
Maybe this will help. The subroutine that opens the workbook runs a bit of code that looks something like this:
Code:
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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:
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]
 
Last edited:

bkelly

Active Member
Joined
Jan 28, 2005
Messages
465
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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).

ξ
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,030
Messages
5,856,935
Members
431,841
Latest member
jaybeem

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
Top