Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Control Box Problems
hi, I wish to link the result in a separate worksheet. Where should I put the code then?? Eg. Cell A2 in worksheet "Sheet2". In addition, instead of Row, can I link the results across columns instead?? How do I do that? Sorry, but I am code-illiterate. Thanks. ========================== Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim oRow As Long oRow = 1 With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) Then oRow = oRow + 1 Me.Cells(oRow, "A").Value = .List(iCtr) End If Next iCtr End With End Sub -- lu_meihua |
#2
|
|||
|
|||
maybe...
Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim oRow As Long oRow = 1 With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) Then oRow = oRow + 1 worksheets("sheetxxxx").Cells(oRow, "A").Value = .List(iCtr) End If Next iCtr End With End Sub Change that sheetxxxx to the correct name. lu_meihua wrote: hi, I wish to link the result in a separate worksheet. Where should I put the code then?? Eg. Cell A2 in worksheet "Sheet2". In addition, instead of Row, can I link the results across columns instead?? How do I do that? Sorry, but I am code-illiterate. Thanks. ========================== Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long Dim oRow As Long oRow = 1 With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If .Selected(iCtr) Then oRow = oRow + 1 Me.Cells(oRow, "A").Value = .List(iCtr) End If Next iCtr End With End Sub -- lu_meihua -- Dave Peterson |
#3
|
|||
|
|||
Hi Dave! thanks! it works!!!!!! thanks thanks thanks! a separate question, i need to create a command button (from control toolbar) to clear some contents in a separate worksheet. however I keep getting run time error. am i missing something? the debug message always highlight at the range. Please help. Many thanks. =================== Private Sub CommandButton2_Click() Sheets("Sheet2").Select Range("G1:IV1").Select Selection.ClearContents Sheets("Sheet3").Select Range("C7").Select End Sub =================== -- lu_meihua |
#4
|
|||
|
|||
You have Range("g1:iv1") behind a worksheet module. Unqualified ranges in these
modules refer to the worksheet that owns the code. So you're trying to select G1:IV1 on the "wrong" worksheet. And you can't select ranges on a worksheet that isn't active. You could do: Private Sub CommandButton2_Click() with sheets("sheet2") .Select .range("G1:IV1").Select Selection.ClearContents end with 'sheets("sheet3" is the sheet that owns the code?? 'if yes, me.select 'if no Sheets("Sheet3").Select 'if sheet3 isn't the sheet that owns the code, then 'fully qualify it sheets("sheet3").Range("C7").Select End Sub But even better is to just clear without all that selecting: Private Sub CommandButton2_Click() Sheets("Sheet2").Range("G1:IV1").ClearContents End Sub lu_meihua wrote: Hi Dave! thanks! it works!!!!!! thanks thanks thanks! a separate question, i need to create a command button (from control toolbar) to clear some contents in a separate worksheet. however I keep getting run time error. am i missing something? the debug message always highlight at the range. Please help. Many thanks. =================== Private Sub CommandButton2_Click() Sheets("Sheet2").Select Range("G1:IV1").Select Selection.ClearContents Sheets("Sheet3").Select Range("C7").Select End Sub =================== -- lu_meihua -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to insert date using a pop up calendar control in a cell i | Excel Discussion (Misc queries) | |||
calendar control - formatting | Excel Discussion (Misc queries) | |||
format control of a list box | Excel Worksheet Functions | |||
Control Tab from Combo box- format control missing!! | Excel Discussion (Misc queries) | |||
Copying list-box control | Excel Discussion (Misc queries) |