[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel VBA issue

Posted on 2014-11-21
6
Medium Priority
?
98 Views
Last Modified: 2014-11-21
I created a form with two listboxes.  When running the following codes, I expect to get data from sheet2 to one listbox (ListBox_LostTime) and and get data from sheet3 to another listbox (ListBox_NetPen).  But I always got data from sheet2 to both listboxes.  Can anyone help me on this?  The workbook is attached.  Thanks.

Sub KE_RTW_init()
    Load KE_RTW
    colcnt = Sheet2.Range("A1:I4").Columns.Count
    With KE_RTW.ListBox_LostTime
        .ColumnCount = colcnt
        .RowSource = Sheet2.Range("A1:I4").Address
        cw = ""
       For c = 1 To .ColumnCount
            cw = cw & Sheet2.Range("A1:I4").Columns(c).Width & ";"
       Next c
        .ColumnWidths = cw
        .ListIndex = 0
    End With
   

   
   
    colcnt2 = Sheet3.Range("A1:H5").Columns.Count
    With KE_RTW.ListBox_NetPen
        .ColumnCount = colcnt2
        .RowSource = Sheet3.Range("A1:H5").Address
        cw2 = ""
       For c2 = 1 To .ColumnCount
            cw2 = cw2 & Sheet3.Range("A1:H5").Columns(c2).Width & ";"
        Next c2
        .ColumnWidths = cw2
        .ListIndex = 0
    End With
   
    KE_RTW.Show
End Sub
KE.xlsm
0
Comment
Question by:jjxia2001
  • 5
6 Comments
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40458462
Try sheets("sheet3").Activate before the second part of the macro.
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40458471
Just checked and that will do it.
0
 

Author Comment

by:jjxia2001
ID: 40458487
I got a run time error 438 "Object doesn't support this property  or method" on the line of sheets("sheet3").Activate
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 51

Expert Comment

by:Martin Liss
ID: 40458494
See line  

Sub KE_RTW_init()
    Load KE_RTW
    colcnt = Sheet3.Range("A1:I4").Columns.Count
    With KE_RTW.ListBox_LostTime
        .ColumnCount = colcnt
        .RowSource = Sheet3.Range("A1:I4").Address
        cw = ""
       For c = 1 To .ColumnCount
            cw = cw & Sheet3.Range("A1:I4").Columns(c).Width & ";"
       Next c
        .ColumnWidths = cw
        .ListIndex = 0
    End With
    

    Sheets("sheet3").Activate
    
    colcnt2 = Sheet2.Range("A1:H5").Columns.Count
    With KE_RTW.ListBox_NetPen
        .ColumnCount = colcnt2
        .RowSource = Sheet2.Range("A1:H5").Address
        cw2 = ""
       For c2 = 1 To .ColumnCount
            cw2 = cw2 & Sheet2.Range("A1:H5").Columns(c2).Width & ";"
        Next c2
        .ColumnWidths = cw2
        .ListIndex = 0
    End With
    
    KE_RTW.Show
End Sub

Open in new window

0
 
LVL 51

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40458499
I should have said

see line 16 which works for me in Excel 2010. Sheets("sheet3").Select also works.
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40458522
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question