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

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

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

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