Anyone know Visual Basic?

PittSpeed Automotive PittSpeed Off Topic
1

I have some VB code in Excel. Is there a way to tell how many workbooks are currently open, what their names are, and what worksheets are in them?

More specifically, I want to check to see if any of the open workbooks have a certain worksheet in them.

2

Tools…Macro…Visual Basic Editor…Maximize…View…Project Explorer…View…Properties Window

3

Wow… Highschool is all coming back to me now…

I took VB, C++ in HS and have forgotten all of it!

4

I need to write code that checks for the worksheet in all the open workbooks.

Reason is, I made some toolbars that are only used for my program. When you close the program I have it delete the toolbars. Problem is, if two copies of the program are open, and I delete the toolbar, then the 2nd one can’t use it. So I need to see if there are two copies open, and only delete the toolbars when BOTH are closed.

5

Are you running from Excel, or VB application?

6

Running from Excel

7

Just a suggestion, rather than going this route, you could add some code to add some text to your toolbar name that would correspond to the number of open programs. ex(TOOLBARNAME. for 1 open program, TOOLBARNAME… for two open programs) Then just do some IF…THEN when closing to adjust the name and close the toolbar if needed. OR, I know you can use the Window() function to move about and open/close worksheets, If you through in an ON LOCAL ERROR routine, you could trap out any non occurrence in the active workbook. I’m not sure how to determine the names of other open workbooks though, so I can’t help with that.

8

nerds

9

:finger: This is paying for my short block!

10

Do you even know what VB is? :stick:

11

Thats where I was going, to determine the names couldn’t you have very simple Do/While loop “text”?

12

i met him once

13

verbatim?

14

That was not VB, that was VD (venereal disease
). :smiley: :love:

15

ya,thanks for giving it to me! :greddy:

16

:gaysex:

It’s the gift that keeps on giving…

17

Try this:
Edit…just re read what you want, try this one.


    numopen = Workbooks.Count
    For i = 1 To numopen
        If Workbooks.Item(i).Name = "WHATEVER" Then
            numopenws = Workbooks.Item(i).Worksheets.Count
            For j = 1 To numopenws
                If Workbooks.Item(i).Worksheets.Item(j).Name = "WHATEVER" Then
                 '
                 'YOUR COMMANDS HERE
                 '
                End If
            Next j
        End If
    Next i
18

Ya but will that continue to other workbooks, or just stop once that is found?

19

Should keep going, it’s a for loop. If he wants it to stop he can put in a Exit Function or Exit Sub depending on how he’s doing it

20

I’ll try this on Monday. I was on the right track but I couldn’t get the Workbooks.Count working.

Thanks for the help!