Solved

Excel VBA issue

Posted on 2014-11-21
6
84 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 45

Expert Comment

by:Martin Liss
Comment Utility
Try sheets("sheet3").Activate before the second part of the macro.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Just checked and that will do it.
0
 

Author Comment

by:jjxia2001
Comment Utility
I got a run time error 438 "Object doesn't support this property  or method" on the line of sheets("sheet3").Activate
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
I should have said

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

Expert Comment

by:Martin Liss
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

763 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

6 Experts available now in Live!

Get 1:1 Help Now