Excel-VBA: Form List Box Multiselect To Excel
How do I set property or what is the code I should put for getting the multiselected items from ListBox on form to range of excel cells?
I.e. I want to add list to excel after required list is selected in the ListBox.
The selection in the listbox may be, say, 1,2,3,4,5.. or at any, say, 1,2,4.. (3,5 not required) etc.
Or should I use some other controls or else?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Multiselect Into Excel Spreadsheet
Hello All,
after many months of being VB free im trying to jump back into it, but sadly having a few problems. I would like a user to select numbers from a multiselect list box . these numbers are than matched through an sql query to hardcoded tables within that datadase and corresponding info is extracted and put into an excel spreadsheet. problem is the sql query only picks up one of the many selected numbers from the list box and drops the rest! very frustrating. im sure its a loop out of order, but cannot find where! Any help would be greatly appreciated as i have run out of ideas!
This is a combination of posted code ive picked up and lines i've added myself - heres what i have so far:
Private Sub Command1_Click()
Dim i, J, rtot, m, TL
Dim db As Database
Dim rs As DAO.Recordset
Dim ctot(1 To 4)
Dim objExcl As Excel.Application
Set db = OpenDatabase(txtDbname.Text)
Set objExcl = New Excel.Application
objExcl.Visible = True
objExcl.SheetsInNewWorkbook = 1
objExcl.Workbooks.Add
objExcl.ActiveSheet.Name = "Inventory Summary"
For i = 0 To List1.ListCount - 1
If List1.Selected(i) = True Then
List2.AddItem List1.List(i)'to error check
Set rs = db.OpenRecordset("select polygon_id from polygon where polygon_id = " & List1.List(i))
i = i + 1
End If
'i = i + 1
Next i
For i = 0 To rs.Fields.Count - 1
objExcl.ActiveSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
Next
J = 3
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
objExcl.ActiveSheet.Cells(J, i + 1).Value = rs.Fields(i)
Next i
'J = J + 1
rs.MoveNext
Loop
rs.MoveFirst
End Sub
Listbox Multiselect Drag && Drop In Excel
I've finally gotten the code down to drag an item from one list box and drop it into another listbox, but only for one item at a time. The list boxes are on a user form and I am using Excel 2002.
Can anyone help with how to select multiple items in ListBox1, drag them to and drop them into ListBox2?
Any suggestions are greatly appreciated.
It seems the DataObject used in the sample code provided by Excel Help [see code below] to carry info from one listbox to the next is limited to one item at a time. Can something else be used in its place that doesn't have this limitation?
Code:
Private Sub ListBox2_BeforeDragOver(ByVal Cancel As _
MSForms.ReturnBoolean, ByVal Data As _
MSForms.DataObject, ByVal X As Single, _
ByVal Y As Single, ByVal DragState As Long, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
Cancel = True
Effect = 1
End Sub
Private Sub ListBox2_BeforeDropOrPaste(ByVal _
Cancel As MSForms.ReturnBoolean, _
ByVal Action As Long, ByVal Data As _
MSForms.DataObject, ByVal X As Single, _
ByVal Y As Single, ByVal Effect As _
MSForms.ReturnEffect, ByVal Shift As Integer)
Cancel = True
Effect = 1
ListBox2.AddItem Data.GetText
End Sub
Private Sub ListBox1_MouseMove(ByVal Button As _
Integer, ByVal Shift As Integer, ByVal X As _
Single, ByVal Y As Single)
Dim MyDataObject As DataObject
If Button = 1 Then
Set MyDataObject = New DataObject
Dim Effect As Integer
MyDataObject.SetText ListBox1.Value
Effect = MyDataObject.StartDrag
End If
End Sub
Private Sub UserForm_Initialize()
For i = 1 To 10
ListBox1.AddItem "Choice " _
& (ListBox1.ListCount + 1)
Next i
End Sub
Edited by - doofusboy on 8/20/2007 6:16:07 AM
How To Get Selected Items From Multi-select List Box On Form In Excel.
Hello... I am having some difficulty getting the selected values from a multi-select list box in Excel. Here is my set up:
A sheet contains a button. The button envokes the form:
frmName.Show
On this form the user selects several values in a list box and hits another button. Here is my problem. In the code behind the _click button action of the form... I am unable to retrieve the values selected by the user... I have tried serveral things I found on the net but none of them seem to work...
does anyone have any example code or ideas here?
Thanks for looking,
brazilnut
Copy Excel Sheet To A Form's Excel Control
I have an Excel workbook. In this workbook I have a VBA form which contains an Excel Spreadsheet control. I want to copy a sheet from the actual workbook to the workbook of this control in my form. I've searched the forums and learned how to copy, but it doesn't seem to cooperate with the control. Any other methods? Thanks.
Looking For Excel OCX To Have Excel Viewed && Controled In My VB Application Form
I am looking for viewing excel application in my VB application form, is there any OCX that can help me having this functionality, I tried OLE but did not meet what Iam looking for.
I used OCX for PDF and ocx for viewing scanned document MAX and now I am looking to have Excel as well.
Appreciate your help in advance.
Many Thanks
Excel Warning - How To Avoid This Excel Form From Appearing
I have an application with an Excel Add-in that communicates with a VB Client (The client communicate with a Server -SQLdB.) This application do calculations. Sometimes when a big report is created in the server (the report is initilized from the Excel Client (Add-in)) an Excel Warning dialog pop up saying :
"Microsoft Excel is waiting for another application to complete an OLE action"
The report is only appearing after clicking on the OK button in this form. I've attached apicture of this.
Is it possible to skip this form to appear when Excel is waiting for the resulting data from the server to arrive?
Excel Issues - "Excel Cannot Determine Which Row In Your List Contains Column Labels"
Hello again,
I am working on a program that imports various Excel spreadsheets into one one workbook. The data is then sorted and calculations are made which are then summarized into one sheet. I have created a form that will enable the user to select which kind of charts they would like to generate and for what, all based on the summary of the data.
The issue I am faced with is an Excel prompt that comes up whenever I use the subtotal function for my calculations. The prompt states "Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command." I use the subtotal function on a number of different worksheets, so this prompt is coming up 5 times.
The imported data does have bold column headings as well. Is there a way to force these prompts to say OK which tells Excel to use the first line of data as my column headings without these prompts coming up? Or is there any other quick fix for this? Any advice is very much appreciated.
Show A Form On Startup In Excel(use To Be:vb And Excel)
Could someone tell me how I can set up a vb macro to run in excel when you open the spreadsheet.
I have a navigation form for the spreadsheet and I want it to be the first thing that is viewed when the user opens the spreadsheet.
Cheers
Excel - Dropdown Showing List 2 Dependant On What Was Chosen In List 1
I'm programming VB in Excel. I've created a series of simple lists in a worksheet. I would like to select one object from list 1 in a cell in column A and then have the cursor cell move to the right neighbouring cell and present me with a dropdown menu showing the contents of another list dependant on what was chosen in the first cell. Is there a specific command for this?
Many thanks for any help.
Multiselect From List
I have a list box that is by populated by looping through a dataenvironment that is connected to a table called "tblRecords". Each record can have more than one Name attached to it, so I have created a separate table called "tblWO_Name". The common link between the two tables is the generic "ID" field. On a form, I am using an adodatacontrol to scroll through records, I want to have my list box highlight the multiple names that come from the tblWO_Name table. I cannot use the adodatacontrol as the datasource because then it rules out using multiselect on the list box. So far I have tried this:
Module:
Public Sub HighlightNames(ID As Long)
Dim nentrynumber As Long
Set oConnection = New ADODB.Connection
oConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\TRAMPMFG - ENGMaintenance DatabasesMean_Between_Downs.mdb;Persist Security Info=False"
oConnection.Open
Set orecordset = New ADODB.Recordset
orecordset.Open "Select * from tblWO_Name where ID = " & ID, oConnection, adOpenDynamic, adLockOptimistic
orecordset.MoveFirst
Do Until orecordset.EOF = True
nentrynumber = Form1.ListRepaired_By.ListCount
Do While nentrynumber > 0
nentrynumber = nentrynumber - 1
If Form1.ListRepaired_By.List(nentrynumber) = orecordset.Fields("RepairedBy").Value Then
Form1.ListRepaired_By.Selected(nentrynumber) = True
End If
Loop
orecordset.MoveNext
Loop
End Sub
Then I call this subroutine from the form. This sub is scrolling through an ado connection that selects only the records whose ID matches the ID that is input onto the form (Form1).Right now, it scrolls through and temporarily highlights each name correctly, but they do not stay highlighted. When it gets to the last name in the recordset, it keeps that one highlighted. Any ideas on how to make it keep all the correct names highlighted?
Help With Multiselect And List
ok i need some help i want a command that reads the first selected and the second selected in a listbox. like say theres 1000 things in the listbox i want to be able to select 2 and display then in another listbox(list2) how would i do?
Added green "resolved" checkmark - Hack
List Box Multiselect
I have a multiselect listbox that's populated with words and each word is assigned a number through the itemdata property like this:
strSql = "SELECT * FROM Vocational ORDER BY vocdesc"
Set rstTemp = fCnn1.Execute(strSql)
Do While Not rstTemp.EOF
lstVocational.AddItem rstTemp!vocdesc
lstVocational.ItemData(lstVocational.NewIndex) = rstTemp!vocid
rstTemp.MoveNext
Loop
What I want to do is select certain indexes in the listbox based on the itemdata property without loooping through the listbox. Is that possible?
I know this is wrong but Something like this:
strSql = "SELECT internalvoc.vocid FROM internalvoc "
strSql = strSql & "WHERE referralid = " & strReferralID
Set rstTemp = fCnn1.Execute(strSql)
Do While Not rstTemp.EOF
'THIS IS WHAT I WANT TO CHANGE
lstVocational.Selected(lstVocational.ItemData(rstTemp!vocid)) = True
rstTemp.MoveNext
Loop
Thanks in advance,
Multiselect List Box
I have this code for my multiselect list box. If I select more than one item
the code goes through each of the items twice before it gets out.
Code:
If lstCategory.ListIndex = -1 Then Exit Sub
For intCounter = lstCategory.ListCount - 1 To 0 Step -1
If lstCategory.Selected(intCounter) = True Then
'MsgBox lstCategory.List(intCounter)
Select Case lstCategory.ListIndex
Case 0 ''Ambulance Stations
Call DrawFunction
Case 1 ''Apartments
Call DrawFunction
Case 2 ''Arenas/Stadiums
Call DrawFunction
Case 3 ''Bingo Halls
Call DrawFunction
End Select
End If
Next intCounter
End If
If my list is:
Station
Apartments
Arena/Stadiums
Bingo Halls
If I select
Arena/Stadiums
Bingo Halls
It will loop through Bingo Halls twice before it goes to Arena/Stadiums, which it will do once.
How can I stop this from happening?
Multiselect In List Box
hi,
i have a list box (multi select) i have a btn next to it and anotherlist box.
once i select item from the first list (may be more than one) and pressthe btn, the selected item goes to next list box.
now i have to unselect the first list. what should i do...
reply urgent
Thanx in advance
A. Gijesh
Multiselect List Box
Hey guys,
I have a listbox with multiselect = True. If I have several values selected in the list box
how do I iterate through them to perform a function with the values selected?
I can get one value but not all of them.
Here is what I was thinking:
For I = 0 To lstNeedPublish.ListCount - 1
If lstNeedPublish.Selected(I) = True Then
' GET THE VALUE AND CALL A FUNCTION
End If
Next
Thanks for your help!!!
Mike
Software Developer by day - MAN OF GOD all the time.
www.mikeross.tv/webdev.asp
List Box - Multiselect
I have a bounded listbox on my form. the probelm is that on design tme i set the multiselect option to 2 that is extended but when I run the form the listbox's multiselect property changes to 0 that is none. Can anyone tell me why this happens? PLs help
Lloyd Dsouza
MultiSelect From A Drop-Down List
Hello all,
Can I MultiSelect from a Drop-Down List? From what I find, I can only MultiSelect from a List Box. The Drop-Down List is generated in the Combo Box. Please help!
Multiselect List Boxes
Still can't get this to work. please help
Open FileName For Random As #1 Len = Len(P)
For counter = 1 To lstemployeeonproject.ListIndex(Selected)
If lstemployeeonproject.Selected(counter) = True Then
P.projectemployees = lstemployeeonproject.Selected(counter)
Put #1, numberofrecords + 1, P
End If
Next counter
Close #1
numberofrecords = numberofrecords + 1
Multiselect List Box With A Date Range
I attach an MDB that the has the multiselect listbox
control. I would however, like to include on the same form
a criteria for a date range; start date, end date and then
build that into the sql when I run the query.
I woiuld also like to make the "All" stay at the top of the
list box.
Can anyone please help me on these 2 items.
Many thanks
CommonDialog1.ShowOpen Multiselect + Detail List
By default the CommonDialog ShowOpen is set to single select and arranges the listing accross the dialog view area.
I want it to be multiselect and the list of files to be in a single column.
How can I do that?
Multiselect In Common Dialog Open (.FileName Not Returning List)
I am using a common dialog open control to open multiple files. Here is my code.
VB Code:
'On Error GoTo EndIt CD1.Flags = cdlOFNAllowMultiselect + cdlOFNExplorer CD1.InitDir = App.Path CD1.Filter = "Text Files (*.txt)|*.txt|All Files (*.*)|*.*" 'CD1.MaxFileSize = 32767 CD1.ShowOpen MsgBox CD1.FileName
When I select 1 file I get a msgbox with it's full path, but if I select multiple files all that is in the msgbox is the path to the folder of the selections (and not the file names).
If I remove the cdlOFNExplorer flag then I get the ugly Win95 dialog, but I get the proper value for CD1.Filename.
Any ideas?
VB To Excel (list Box)
oSheet.Cells(z, 1).Value = Form1.List1.List(z)
oSheet.Cells(y, 2).Value = Form1.List2.List(y)
How do i get it to loop down...if i have x amount of things in a list box...
But it wont list them all with a z - 1 function or anything...
I woul dhave to do x amount of osheet.cells = etc.
Anyone have a tip for me?
Thank
Excel List
Hi,
Where can i find a EXCEL discussion list? somthing like VB-WORLD?
Regards
How Do I Use A Drive List Box In Excel?
I am trying to add a drive list box into an Excel macro.
What I want to do is to click a command button on the spreadsheet and have a drive list box open to allow me to chooses and load a file into an excel spreadsheet. From this spreadsheet I then want to block-copy all the data back into the one with the macro.
Is this possible with VBA? I can't figure it out and the help files are worse than useless.
Barry
PS any idea why I can't use my avatar on this forum? It works on others.
Problems To Get A List In Excel
Hi there i want to get a list in VB to get in excel. Like the first item on cell 2,2 en the second on cell 2,3 enz. What's wrong?
Dim objExcel As Object
Dim objBook As Object
Dim objSheet As Object
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.open(App.Path & "17.xls")
Set objSheet = objBook.Worksheets("inschrijving")
objSheet.Activate
objExcel.Visible = True
With objExcel.activesheet
.cells(2, 2).Value = List1.Text
.cells(3, 2).Value = List1.Text
.cells(4, 2).Value = List1.Text
End With
End Sub
Drive List Box In Excel??
In excel is there a control for a drivelist and folderlist etc... If so what control is it?
Regards
JCD
Excel Arguement List
Hi.
When i write a function in VB like:
Code:
Private Function OOO(mystringshouldbehere as string, mynumberhere as long)
end function
Then when i inside VB write "OOO(" Then it says to me what the names of the arguements are and what type they should be.
The problem is when calling a function made in VBA from excel document: "=OOO(" it doesn't show any help.
Is there anyway to make that appear?
Excel VB List And Filter
I would like to create a list of data, similar to what would be in rows and columns of a table in excel - but purely in VBA. I assume there is a way to do this in an array or something? Hope someone can help.
What I would like to do is create this database and then be able to filter a specific column of information and then display the remaining items and their corresponding adjacent columns of data. All this using only VBA, no excel spreadsheets.
Can someone post code for this as an example, this is how I learn best.
Combo List In Excel Using VB
Hi
I am having data in a Flex grid
I transfered those data to Excel at run time
But i need to have one combo box in excel
How can i create combo box in excel in run time
Please reply
Thank you
Excel List Box Help Please (RESOLVED)
hello all. I use the following code to add to a multi column list box but how do I get the information in to seperate txt boxes.
VB Code:
Private Sub lststationery_Click()txtdes.Text = lststationery.Valuetxtcode.Text ' how do I get the second and third columns of data pleasetxtprice.TextEnd Sub Private Sub UserForm_Initialize()For n = 2 To 59 'I always use n cos on the ZX Spectrum, next n could be typed by pressing n twice. It's my convention, most people use counter or suchlike Set curcell = Worksheets(2).Cells(n, 10) EqDesc(n) = curcell.Value Set curcell = Worksheets(2).Cells(n, 11) EqCode(n) = curcell.Value Set curcell = Worksheets(2).Cells(n, 12) EqPrice(n) = curcell.Value With lststationery .AddItem (EqDesc(n)) 'column 1 .List(.ListCount - 1, 1) = EqCode(n) 'column 2 .List(.ListCount - 1, 2) = EqPrice(n) 'column 3 End WithNext n End Sub
help me im stumped.
cheers
chrisio
List Indexing (VB In Excel)
Hi I am populating a text box with values from a number of excel cells, however the number of cells that contain data is not always the same i.e. I may have 10 values in one row and 12 in another, up to say a maximum of 50, so in order to avoid adding each cell one at a time, I am wondering if I could put a range in e.g. column 2 row 2 to column 51 row 2. Please see attachment. in my current code below I am using & to add all the row and column data together, I would like to avoid this approach.
Thanks.
VB:
--------------------------------------------------------------------------------
Private Sub ComboBox1_Change()
With Me.ComboBox1
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("DATA LOADER.xls", _False, True)
Header.Value = SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 2).Value & " " _
& SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 3) & " " _
& SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 4) & " " _
& SourceWB.Worksheets(2).Cells(Me.ComboBox1.ListIndex + 1, 5)
End With
End Sub
Excel: Sorted List
Hi all,
I try to find if a list in a range of cells is sorted. For example:
I have datalist in range(A3:F21) and I want to see if this list is sorted by column A (A3:A21) or not.
I appreciate if you can help on this
Thanks in advance
List Of Constants In Excel
Does anyone know where I can find the constants, (think that's right?), for excel and vba?
For example:
I use php and com to run excel. I want to do a graph, no problem, but to do formatting of the graph, I need to know the number value of certain attributes because the text way doesn't work.
If I want to do a 3d pie chart.
ChartType = xl3dPie does not work but ChartType=-4102 does.
Where do I find a listing of these values for this and other attributes such as:
Legend.Position="xlBottom" etc...
Help?
Getting The List Of Files From A Dir In Excel
I want to get the list of files in a single directory so I can then open them one after the other. How do I get the list without using the Microsoft directory and fileList controls (since I have no form)? I want to load an array with the names then I'll cycle through that to open each file.
Thanks a lot everyone.
Scott
The worst thing about wearing glasses is them being on your face.
Nothing is impossible, it just looks that way.
Help: I Need To Print A List (in Excel)
Hi !
Could somebody help me to print a ListBox ?
I´m displaying a UserForm that contains a ListBox with non constant data; i.e. it depends on the user the number of
rows displaying on the List; so my problem is, if I use the PrintForm function it prints a part of the List and if the List it´s
too long (scrollbars appear ) then last part of the list doesn´t prints.
I hope you can help me.
Thanks !
Can Excel Populate A List Via VBA?
Hi, I'm good with VB.NET but not very well versed in VBA. I have an excel 2007 spreadsheet which has a list in one of it's cols. As the contents of items in the list will change from time to time, is there a way I can simply have VBA read a simple text file and populate the list? Or do the items I want in the list need to be pasted in a sheet and then set manually?
Thank you. Gordon
List Of Excel Commands On COM
How can I get a list of all available commands for automating Excel thru VB...
For Example, oExcel in the following...
------------------------------------------------------
oExcel = CreateObject("Excel.Application")
...
oExcel.DisplayAlerts = False
...
oExcel.visible = True
-------------------------------------------------------
Two calls for oExcel are DisplayAlerts and visible.
Where can I find a list of other calls
Thanks in advance!
Need Help With Printing Address List In Excel
I have this code which prints 8 lines of address from a worksheet.
What I need help with is to leave 3 blank lines after each address.
Any help is much appreciated, thank you all!
Code:
Dim TheRow As Long, TheSource As Long
TheRow = 2
TheSource = 3
Ref = MsgBox("Send filtered results to printing format now?", vbOKCancel)
If Ref = vbCancel Then
Exit Sub
Else
Sheets("Contacts").Activate
Do
For j = 1 To 8
For k = 1 To 3
If Not Sheets("Contacts").Rows(TheSource).Hidden Then
Range(Cells(TheSource, 9), Cells(TheSource, 16)).Copy
With Sheets("Print Addresses")
.Range(.Cells(TheRow, k).Address & ":" & Cells(TheRow, k).Address).PasteSpecial Transpose:=True
End With
Else
k = k - 1
End If
TheSource = TheSource + 1
Next k
TheRow = TheRow + 8
Next j
Loop Until Cells(TheSource, 1).Value = ""
End If
Need To Show List Of EXCEL Macros In VB6
I have a VB6 application which interacts with EXCEL spreadsheets. The spreadsheets (actuually a PERSONAL.XLS spreadsheet which stores many macros) have macros (VBA) and I would like to be able to present my user with a list of the available macros where they can choose the one they want to run.
I cannot seem to find any way to get the list of macros associated with a spreadsheet.
Any sugestions?
DOTJake
Excel Sheets And List Boxes
Hi, I have some code that finds all available sheets in a workbook and puts them all into a list box. How do I make it so that the sheet that's highlighted in the listbox is the one that VB will write data to? thanks for any help you can give me. Heres my code for getting the sheets if you need it.
Dim SheetName As String
Dim Sheetcount As String
Dim i As Integer
With xlsApp.ActiveWorkbook
Sheetcount = .Worksheets.Count
For i = 1 To Sheetcount
SheetName = .Worksheets(i).Name
lstSheets.AddItem SheetName
Next i
End With
Excel VBA Sort An Array List
Im not even sure where to start with this coding, perhaps someone can post me a sample code for an example. Please note, I have never worked with array's before, so feel free to speak to me in n00b language.
The facts:
I have 8 text boxes on a form (in Excel VBA) each contains a time in format hh:mm AM/PM. Not all of these times will be in order, so textbox1 might be 08:00 AM, textbox2 10:00 AM, textbox3 09:00 AM... and so on.
What I need:
I need excel to sort these for me... so the array would pull from the textboxes and list first textbox1, then textbox3, then textbox2... and so on.
I assume I also need to know how to create the array, using Dim? and how to pull each value from the array to use in a formula in another procedure?
Thanks
- Desperate!
Data Transfer From A List Box To Excel
Hi. I was wondering if anyone could help me with something which is probably quite simple but I can't seem to find on MSDN.
I am basically wanting to transfer data from a VB list box to a column in an Excel worksheet.
Thanks for any help you can give.
Creating List Of Values In Excel From VB
i have to create a execel sheet from Vb
I have created the code.
But i have to add a list to values to a column Say A, so that the user can any one in the list of values.
After a value is selected in the column the next column say B should have a value based on Value in A
Eg
AB
YesY
NoN
When Yes is selected in Column A then the value in Column B should be Y
When No is selected in Column A then the value in Column B should be N
Please help me in the same
Thanks in Advance
|
TRACKER
