?
Solved

Excel VBA issue

Posted on 2014-11-21
6
Medium Priority
?
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 49

Expert Comment

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

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

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 49

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 49

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

765 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