Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:

    VB:
     
    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
    7,780

    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..):
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  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.

    VB:
     
    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, 16:20
  2. Replies: 2
    Last Post: July 7th, 2010, 21:46
  3. Code Multicolumn Listbox On Worksheet
    By MNTye in forum EXCEL HELP
    Replies: 27
    Last Post: August 7th, 2007, 01:06
  4. Code not working if no selection in Listbox
    By sonsailer in forum EXCEL HELP
    Replies: 3
    Last Post: June 16th, 2006, 20:33
  5. Replies: 15
    Last Post: January 28th, 2005, 17: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