Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel programming errors for copying and pasting
I have been trying to use controls ( I am new) in excel. I want to have lists
that users can select (multiple choices from) push a command button and the values will be copied and pasted to another list box. I copied code from a book I bought and named everything as described - but I am getting the following error: invalid qualifier This is the code Sub Button3_Click() Dim i As Integer Dim ListBox1 As Long If ListBox1.ListIndex = -1 Then Exit Sub If Not cbDuplicates Then ' See if item already exists For i = 0 To ListBox2.ListCount - 1 If ListBox1 = ListBox2.List(i) Then Beep Exit Sub End If Next i End If ListBox2.AddItem ListBox1.Value End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel programming errors for copying and pasting
This can't be right:
Dim ListBox1 As Long Take it out and take it from there. RBS "gbpg" wrote in message ... I have been trying to use controls ( I am new) in excel. I want to have lists that users can select (multiple choices from) push a command button and the values will be copied and pasted to another list box. I copied code from a book I bought and named everything as described - but I am getting the following error: invalid qualifier This is the code Sub Button3_Click() Dim i As Integer Dim ListBox1 As Long If ListBox1.ListIndex = -1 Then Exit Sub If Not cbDuplicates Then ' See if item already exists For i = 0 To ListBox2.ListCount - 1 If ListBox1 = ListBox2.List(i) Then Beep Exit Sub End If Next i End If ListBox2.AddItem ListBox1.Value End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel programming errors for copying and pasting
Saved from a previous post. It may give you an idea you can use...
Are these controls on a UserForm on directly on a worksheet? If it's on a userform, then you can read this: http://groups.google.com/groups?selm...BDD4%40msn.com If they're on a worksheet, then I used controls from the Control Toolbox toolbar. I put 4 commandbuttons and two listboxes on the worksheet. Then I named the Commandbuttons: BTN_moveAllLeft BTN_moveAllRight BTN_MoveSelectedLeft BTN_MoveSelectedRight Right click on the worksheet tab and select view code. Paste this in: Option Explicit Private Sub BTN_moveAllLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 Me.ListBox1.AddItem Me.ListBox2.List(iCtr) Next iCtr Me.ListBox2.Clear End Sub Private Sub BTN_moveAllRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 Me.ListBox2.AddItem Me.ListBox1.List(iCtr) Next iCtr Me.ListBox1.Clear End Sub Private Sub BTN_MoveSelectedLeft_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox2.ListCount - 1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox1.AddItem Me.ListBox2.List(iCtr) End If Next iCtr For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1 If Me.ListBox2.Selected(iCtr) = True Then Me.ListBox2.RemoveItem iCtr End If Next iCtr End Sub Private Sub BTN_MoveSelectedRight_Click() Dim iCtr As Long For iCtr = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox2.AddItem Me.ListBox1.List(iCtr) End If Next iCtr For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1 If Me.ListBox1.Selected(iCtr) = True Then Me.ListBox1.RemoveItem iCtr End If Next iCtr End Sub Private Sub Worksheet_Activate() Dim myCell As Range Me.ListBox1.Clear Me.ListBox2.Clear With Me.ListBox1 .LinkedCell = "" .ListFillRange = "" For Each myCell In Me.Range("g7:g19").Cells If Trim(myCell) < "" Then .AddItem myCell.Value End If Next myCell End With Me.ListBox1.MultiSelect = fmMultiSelectMulti Me.ListBox2.MultiSelect = fmMultiSelectMulti End Sub The bad news is I wasn't sure when to populate the listbox. I chose to do it when you activated the worksheet. I don't think you'd want this--if you click off the sheet and come back, then the listboxes are reset. Maybe have it populated when the workbook opens???? gbpg wrote: I have been trying to use controls ( I am new) in excel. I want to have lists that users can select (multiple choices from) push a command button and the values will be copied and pasted to another list box. I copied code from a book I bought and named everything as described - but I am getting the following error: invalid qualifier This is the code Sub Button3_Click() Dim i As Integer Dim ListBox1 As Long If ListBox1.ListIndex = -1 Then Exit Sub If Not cbDuplicates Then ' See if item already exists For i = 0 To ListBox2.ListCount - 1 If ListBox1 = ListBox2.List(i) Then Beep Exit Sub End If Next i End If ListBox2.AddItem ListBox1.Value End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programming around Vlookup Errors in VBA | Excel Programming | |||
Programming around vlookup errors in vba | Excel Programming | |||
Copying from Excel - pasting in Publisher | Excel Worksheet Functions | |||
copying from word and pasting into excel | Excel Programming | |||
Need Help with Programming-Syntax/Compile Errors | Excel Programming |