Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Use Listbox selection to reference workbook.worksheet later in code

  1. #1
    Join Date
    25th November 2010
    Posts
    158

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th November 2010
    Posts
    158

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

    Any ideas anyone?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st September 2010
    Posts
    10,362

    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.
    If I ask "What Have You Tried?" - What are you going to say...?

  4. #4
    Join Date
    25th November 2010
    Posts
    158

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: December 21st, 2010, 17:20
  2. Replies: 2
    Last Post: July 7th, 2010, 22:46
  3. Code Multicolumn Listbox On Worksheet
    By MNTye in forum EXCEL HELP
    Replies: 27
    Last Post: August 7th, 2007, 02:06
  4. Code not working if no selection in Listbox
    By sonsailer in forum EXCEL HELP
    Replies: 3
    Last Post: June 16th, 2006, 21:33
  5. Replies: 15
    Last Post: January 28th, 2005, 18:27

Bookmarks

Posting Permissions

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