Announcement

Collapse
No announcement yet.

Use Listbox selection to reference workbook.worksheet later in code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use Listbox selection to reference workbook.worksheet later in code

    I hope that title makes sense guys. Here's what I'm needing help with:

    I am populating a listbox with the names of all open workbooks.worksheets. The listbox is populating with this format:

    Workbooks("wbname").Sheets("shtname")

    I'm trying to use whatever sheet is selected later in code to compare with another sheet but keep generating different errors in the second code with everything I try. I've already defined the listbox results in my code, but it's still not working. Any help would be much appreciated. Here's a copy of the Userform code:

    Code:
     
    Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim Sht As Worksheet
    Dim Bookname As String
     
    For Each wb In Workbooks
     
    Bookname = "Workbooks" & "(" & Chr(34) & wb.Name & Chr(34) & ")"
        For Each Sht In wb.Worksheets
     
                UserForm2.ListBox1.AddItem Bookname & "." & "Sheets" & "(" & Chr(34) & Sht.Name & Chr(34) & ")"
                UserForm2.ListBox2.AddItem Bookname & "." & "Sheets" & "(" & Chr(34) & Sht.Name & Chr(34) & ")"
        Next Sht
        Next wb
    End Sub
     
    Private Sub CommandButton2_Click()
    Dim sht1 As String
    Dim sht2 As String
    Dim myRange As Range
    Dim rng1 As Range
    Dim rng2 As Range
    Dim cell As Range
    sht1 = UserForm2.ListBox1.Value & ".UsedRange"
    sht2 = UserForm2.ListBox2.Value
     
    Set rng1 = Range("sht1")
    Set rng2 = Range("sht2")
    For Each cell In rng1
     
    If cell.Value <> rng2(cell.Address) Then
    rng2(cell.Address).Interior.Color = 65535
     
     
    End If
    Next cell
    End Sub
    Thank you!

  • #2
    Re: Use Listbox selection to reference workbook.worksheet later in code

    Any ideas anyone?

    Comment


    • #3
      Re: Use Listbox selection to reference workbook.worksheet later in code

      I had a quick look and there's a basic flaw with the way you've coded it... I don't have time to look further, but the line(s like..):
      Code:
      Set rng1 = Range("sht1")
      is trying to set a reference to a range named sht1, not the value for sht1 - remove the double quotes.

      Also, UsedRange returns the address of the last cell, so you want to include the reference to "A1:" at the start and .Address at the end to build a valid address... that's where I don't have the time to look further.

      Sorry I can't be more specific right now. If no-one else replies, I'll come back to this later.

      Comment


      • #4
        Re: Use Listbox selection to reference workbook.worksheet later in code

        I got it guys. I'm attaching the entire working code that resides in the Userform. This macro displays all open workbooks and their worksheets in a userform list box, then compares listbox 1 with listbox 2 and highlights the differences, then give you a total count of the differences. Maybe this will be useful for someone.

        Code:
         
        Private Sub CommandButton1_Click()
        Dim sht1 As String
        Dim sht2 As String
        Dim bk1 As String
        Dim bk2 As String
        Dim sh1 As String
        Dim sh2 As String
        Dim rng1 As Range
        Dim rng2 As Range
        Dim cell As Range
        Dim colorcount As Long
        
        sht1 = UCase(UserForm2.ListBox1.Value)
        sht2 = UCase(UserForm2.ListBox2.Value)
        bk1 = Mid(sht1, InStr(sht1, """") + 1, InStr(InStr(sht1, """") + 1, sht1, """") - InStr(sht1, """") - 1)
        sh1 = Mid(sht1, InStr(sht1, ".WORKSHEETS(") + 13, InStr(InStr(sht1, ".WORKSHEETS(") + 13, sht1, """") - InStr(sht1, ".WORKSHEETS(") - 13)
        bk2 = Mid(sht2, InStr(sht2, """") + 1, InStr(InStr(sht2, """") + 1, sht2, """") - InStr(sht2, """") - 1)
        sh2 = Mid(sht2, InStr(sht2, ".WORKSHEETS(") + 13, InStr(InStr(sht2, ".WORKSHEETS(") + 13, sht2, """") - InStr(sht2, ".WORKSHEETS(") - 13)
         
        Set rng1 = Workbooks(bk1).Worksheets(sh1).UsedRange
        Set rng2 = Workbooks(bk2).Worksheets(sh2).UsedRange
        For Each cell In rng1
        
        If cell.Value <> rng2.Range(cell.Address) Then
        rng2.Range(cell.Address).Interior.Color = 65535
             
            
        End If
        Next cell
        UserForm2.Hide
            For Each cell In rng2
                If cell.Interior.Color = 65535 Then colorcount = colorcount + 1
            Next cell
        MsgBox "There are" & " " & colorcount & " " & "highlighted cells"
        End Sub
         
         
        Private Sub CommandButton2_Click()
        Dim wb As Workbook
        Dim Sht As Worksheet
        Dim Bookname As String
        
        For Each wb In Workbooks
        
        Bookname = "(" & Chr(34) & wb.Name & Chr(34) & ")"
            For Each Sht In wb.Worksheets
           
                    UserForm2.ListBox1.AddItem Bookname & "." & "WorkSheets" & "(" & Chr(34) & Sht.Name & Chr(34) & ")"
                    UserForm2.ListBox2.AddItem Bookname & "." & "WorkSheets" & "(" & Chr(34) & Sht.Name & Chr(34) & ")"
            Next Sht
            Next wb
        End Sub

        Comment

        Working...
        X