Solved

Excel VBA issue

Posted on 2014-11-21
6
88 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 46

Expert Comment

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

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

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 46

Accepted Solution

by:
Martin Liss earned 500 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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now