Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Handy macro suddenly not working (Excel 2000)

    I picked up this handy macro which gives an expanded listbox for swithching between workbooks from one of my tip-mail services. It worked fine over the weekend on my Excel97 and I recall testing it OK on my office laptop, but today when I run it I get a "Runtime error 9, subscript out of range" error at the line If Windows(wkb.Name).Visible Then (yellow highlighted-but not the rest of the statement). I am "sure" I copied it correctly, so I cannot figure out the issue. Would being hooked up to my office network affect this? Would being in Excel2000 and not 97 be the cause? Thanks. I hope we can get this to work.. our filenames are long and the nicel listbox would make reading easier.

    UserForm code:

    Option Explicit

    Private Sub ListBox1_Click()
    Windows(ListBox1.Value).Activate
    Unload Me

    End Sub

    Private Sub UserForm_Activate()
    Dim wkb As Workbook
    For Each wkb In Workbooks
    If Windows(wkb.Name).Visible Then ListBox1.AddItem wkb.Name
    Next
    End Sub

    Module code
    Option Explicit


    in a Module, the code:
    Sub All_Windows()
    UserForm1.Show
    End Sub

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

    Re: Handy macro suddenly not working (Excel 2000)

    The UserForm_Activate procedure will fail if you have more than one window on the same workbook (you can open new windows on an open workbook by selecting Window | New Window). Try the following modification:

    Private Sub UserForm_Activate()
    Dim wkb As Workbook
    Dim i As Integer
    ' Loop through open workbooks
    For Each wkb In Workbooks
    If wkb.Windows.Count > 1 Then
    ' More than one window on workbook, so loop through windows
    For i = 1 To wkb.Windows.Count
    If wkb.Windows(i).Visible Then
    ListBox1.AddItem wkb.Name & ":" & i
    End If
    Next i
    ElseIf wkb.Windows(1).Visible Then
    ' Only one window open
    ListBox1.AddItem wkb.Name
    End If
    Next
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handy macro suddenly not working (Excel 2000)

    Thanks...I will enter the code and try again. I rarely have two windows open on one workbook, however. I'm sure this thing worked over the weekend. They always do.

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

    Re: Handy macro suddenly not working (Excel 2000)

    For the record: I tested the original code on Excel XP SP-2; it worked fine if I didn't have two windows open on the same workbook. But perhaps there are other special circumstances that cause it to fail.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handy macro suddenly not working (Excel 2000)

    Private Sub UserForm_Activate()
    Dim wkb As Workbook
    Dim i As Integer
    'loop through open workbooks
    For Each wkb In Workbooks
    If wkb.Windows.Count > 1 Then
    'More than one workbook window open, so loop through windows
    For i = 1 To wkb.Windows.Count
    If wkb.Windows(i).Visible Then
    ListBox1.AddItem wkb.Name & ":" & i
    End If
    Next i
    ElseIf wkb.Windows(1).Visible Then
    ListBox1.AddItem wkb.Name
    End If
    Next
    End Sub

    Private Sub UserForm_ddActivate()
    Dim wkb As Workbook
    Dim i As Integer
    ' Loop through open workbooks
    For Each wkb In Workbooks
    If wkb.Windows.Count > 1 Then
    ' More than one window on workbook, so loop through windows
    For i = 1 To wkb.Windows.Count
    If wkb.Windows(i).Visible Then
    ListBox1.AddItem wkb.Name & ":" & i
    End If
    Next i
    ElseIf wkb.Windows(1).Visible Then
    ' Only one window open
    ListBox1.AddItem wkb.Name
    End If
    Next
    End Sub

    I'll be da*ned if I can find why I get a End if without Blockif error when I meticulously type your code into my code block. I have one If / End if and one If/Elseif/Endif as you do...but I get the error. When I copied your code into the module it worked. I've looked at every word in the code and I do not see why mine would not work. Are there any hidden characters or something?

    I have determined that when I have excel display the full pathway to the file in the titlebar, the code doesn't work. How would one add the full pathway to the file into the lisstbox, so that wouldn't happen? The macro to display shows the activeworkbook.fullname property...Maybe I can figure out how to add it in...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handy macro suddenly not working (Excel 2000)

    Is that all of the code in the module? If I copy your code from this message and paste it into my XL2K, I do not get the End If error.

    Also, shouldn't the name of the second Sub be:

    Private Sub UserForm_Deactivate()
    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handy macro suddenly not working (Excel 2000)

    I just named the second set of code to avoid two sub names. So at least my code does work when you copied it...I am not blind yet. Yet I still do not understand that If businesss. Someday I will, meanwhile, thanks to all. I may work on that titlebar business and get back to the forum if (or rather, when) it doens't work.

  8. #8
    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: Handy macro suddenly not working (Excel 2000)

    If you display the FULLNAME rather than the workbook name,
    ListBox1.AddItem wkb.FullName & ":" & i

    Or in the second instance
    ListBox1.AddItem wkb.FullName

    The list box will display the full name.

    BUT
    the line in listbox click event:

    <pre>Windows(ListBox1.Value).Activate</pre>


    will no longer work, since the listbox.value is no longer the filename but the Fullname and you will need something like the following which removes the path (that you added) for the list box:

    <pre>Private Sub ListBox1_Click()
    Dim sWkbName As String
    Dim x As Integer
    sWkbName = ListBox1.Value
    x = 0
    Do Until Right(sWkbName, 1) = ""
    sWkbName = Mid(sWkbName, 1, Len(sWkbName) - 1)
    x = x + 1
    Loop

    Windows(Right(ListBox1.Value, x)).Activate
    Unload Me
    End Sub</pre>


    Steve

Posting Permissions

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