Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is There an ActiveWorkbook? (Office 97 SR2)

    I have a macro that I use that runs as long as there is an active workbook.
    My macro is in personal.xls
    To use the macro, I open 1 or more workbooks and then click on tools, macro, macro.
    It works fine on one pc and crashes on the other.

    My intention is to run the code as long as there is an active workbook (other than personal.xls)
    I used the following:
    Do
    ' ....................... is there an active workbook ? ...............
    If (Workbooks.Count = 1) And (ThisWorkbook.Name = "personal.xls") Then
    Exit Sub
    End If
    With ActiveWorkbook.ActiveSheet
    ..... some code.......
    end with

    Loop
    Why does it work on one PC and bumb out on the other
    The point where it fails is when it reaches the with ActiveWorkbook.ActiveSheet --
    and the only file open is my hidden personal.xls ---- which is when I want it to EXIT

    What am I doing wrong?
    Phyl

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    Perhaps the other person doesn't have a personal.xls. You have to check for conditions of zero open workbooks, one which may not be personal.xls, and more than one. See if this works:

    If Workbooks.Count = 0 Or (Workbooks.count = 1 And ActiveWorkbook.Name = "personal.xls")Then
    Exit Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    The other PC is also mine (laptop) and yes - personal.xls is open and hidden.
    Thanks though,
    Phyllis

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    My personal.xls is "PERSONAL.XLS" not "personal.xls"
    And these text string are NOT Equal!!

    Try:
    If (Workbooks.Count = 1) And (ucase(ThisWorkbook.Name) = "PERSONAL.XLS") Then

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    Steve is on the right track. But if PERSONAL.XLS is always loaded and hidden, it isn't active, so wouldn't:

    If Workbooks.Count <2 Then Exit Sub

    work? (Or am I having a lazy Sunday afternoon? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    I guess easiest is to simply count the visible windows:

    <pre>Function HasVisibleWindows() As Boolean
    Dim oWkbk As Workbook
    Dim oWindow As Window
    For Each oWkbk In Application.Workbooks
    For Each oWindow In oWkbk.Windows
    If oWindow.Visible Then
    HasVisibleWindows = True
    Exit Function
    End If
    Next
    Next
    HasVisibleWindows = False
    End Function
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    Hi - I have been pulling my hair out! After reading these helpful replies I discover more questions.
    When I whet to VBE on my laptop, it showed
    Project -PERSONAL.XLS and the properties window showed (Name) PERSONAL
    I then exported each module and deleted the whole thing and restarted EXCEL.
    Next I recorded a macro to create a new Personal.xls, went to VBE to import my old modules and the VBE
    window title is now "Miscrosoft Visual Basic - PERSONAL.XLS" and the list boxes show:
    Project - VBAProject and the properties window showed (Name) VBAProject
    Then I then got the following to work for me
    If (Workbooks.Count = 1) And (UCase(ThisWorkbook.Name = "PERSONAL.XLS")) Then Exit Sub
    I am now thinking life is great so...
    Next I copied the Personal.xls (that's the way it's named under Xlstart) to a zip drive and moved it to
    Xlstart directory on my desktop to make sure everything was OK there.
    Well -- my VBE window title on my desktop now is
    Wellllll- on the desktop the VBE window title is "Miscrosoft Visual Basic - Personal.xls"
    So on the desktop I have to change to the following:
    If (Workbooks.Count = 1) And (UCase(ThisWorkbook.Name = "Personal.xls")) Then Exit Sub
    So I went to explorer and renamed Personal.xls to PERSONAL.XLS and pressed enter.-- It still showed
    as Personal.xls. I sent back and restarted Excel and it now shows as PERSONAL.XLS in VBE and
    the code used on laptop works again..... ???
    What causes this when I move the file?
    Thanks, Phyl

  8. #8
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    How would I call this function from my macro?
    Looks interesting
    Phyl

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    If HasVisibleWindows Then
    Msgbox "Has Visible Windows"
    Else
    Msgbox "Has No Visible Windows"
    End If
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    You've got the parentheses wrong. It should be

    (UCase(ThisWorkbook.Name) = "PERSONAL.XLS")

    in all cases. UCase(ThisWorkbook.Name) converts the name of the workbook to upper case, and this is compared to "PERSONAL.XLS". You had

    (UCase(ThisWorkbook.Name = "PERSONAL.XLS"))

    which means that first the name of the workbook "as is" is compared to "PERSONAL.XLS" and the result of this comparison (TRUE = -1 or FALSE = 0) is converted to upper case, which makes little sense.

  11. #11
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is There an ActiveWorkbook? (Office 97 SR2)

    Thanks - I will try that......
    Phyl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •