list box selection

[tubar]

i use a button to select a few things on a form. Although the correct record is highlighted in the list box, i still need to select it before i can input a record. even if i set focus to the list box. is there something else i could try?

Me.lst0.Selected(11) = True
Me.Combo17.Value = 14
Me.lst0.SetFocus

 

[vbaInet]

I don't understand your query tubar. What is the problem here?

 

[tubar]

when the user clicks on the button, a value is selected in the list box. i then hit another button to input a record with a value from the previous mentioned list box...when the user hits this input record button, the data base says that nothing is selected in the list box... the code i mentioned, highlighted the value in the list box...but the user still had to select record in the list box...sorry im new

 

[vbaInet]

Alright, use this:

Me.lst0.Value = Me.lst0.Selected(11)

By the way, you need to work on your naming convention.

 

[tubar]

the record gets inputed but the value fromthe list box isnt selected. and i agree i really need to do a better job with naming...it will make life much easier

 

[tubar]

Me.lst0.Value = 11

that worked....thanks man

 

[vbaInet]

No problemo!

 

[Samantha]

VbaInet,

I came across your post and it helped me return the value of the selected, any idea how to return multiple values. This is my code, I want it to return multiple addresses into my merged Excel template.

XLSheet.Range("ServiceAdd") = Me!ServiceAddress.Value

Thanks for any advise!

Samantha

 

[boblarson]

Multiple values require iterating through the selected items in code. The listbox, when in multi-select mode, doesn't have a value - it is always null.

Our own pbaldy has this on his site to show you how:
http://www.baldyweb.com/multiselect.htm

 

[Samantha]

Thanks for the response Bob, although I am lost. Just to clarify Me!ServiceAddress.Value returned only the first selection of the multi-list box. I set the varibles to build the string, and inserted portions of the code from the link. I get an error message at the red line?

Private Sub cmdMergeXLbttn_Click()
'Declare a variable named MySheetPath as string
Dim MySheetPath As String
'set file path to actual sheet
MySheetPath = "O:\Access\ProjectSheet.xltx"
'Set up object variables to refer to Excel and Objects
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strServiceAddress As String
Dim ctl As Control
Dim varItem As Variant
'Open an instance of Excel, open the workbook.
Set XL = CreateObject("Excel.Application")
Set XLBook = GetObject(MySheetPath)
'Make sure everything is visible
XL.Visible = True
XLBook.Windows(1).Visible = True
'Build service address string from multi list box
Set ctl = Me!ServiceAddress
For Each varItem In ctl.ItemsSelected
   strServiceAddress = strServiceAddress & ctl.ItemData(varItem) & ","
   'Use this line if your value is text
   strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
  Next varItem
  'trim trailing comma
  [COLOR=red]strServiceAddress = Left(strServiceAddress, Len(strServiceAddress) - 1)[/COLOR]
 
'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)
XLSheet.Range("DateGen") = Date
XLSheet.Range("ProjectNumber") = JobNumber
XLSheet.Range("CompanyAdd") = [Company] & vbCrLf & ([sfrmContacts].[Form]![Address]) & vbCrLf & ([sfrmContacts].[Form]![City]) & ", " & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
XLSheet.Range("Contact") = ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
XLSheet.Range("Phone") = ([sfrmContacts].[Form]![Phone])
XLSheet.Range("Fax") = ([sfrmContacts].[Form]![BusinessFax])
XLSheet.Range("ProjectDescription") = ProjectDescription
XLSheet.Range("ServiceAdd") = strServiceAddress 'Me!ServiceAddress.Value -returned only first selection
XLSheet.Range("City") = City
XLSheet.Range("State") = State
 
End Sub

Thanks!

 

[boblarson]

I don't see anything wrong at the moment with that code. What you should do is put a breakpoint just before the loop that builds that string and F8 through checking values at each step.