Solved

Excel VBA issue

Posted on 2014-11-21
6
91 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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.

809 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