On a userform I have two lisboxes that are populated from named ranges. The listboxes are populated correctly when the form is initiated and I can scroll through the choices in both listboxes but their respective cells do not get filled and .value for both is “NULL”.
Listbox setup:
Private Sub UserForm_Initialize()
'list the departments
With lstEmplDept
.List = Range("Lists_Departments").Value
End With
'list the work environments
With lstWrkEnv
.List = Range("Lists_Work_Environments").Value
End With
End Sub
Populating cells (after duplicating the last row of data
Private Sub cbn_Enter_Data_Click()
Dim lastRow As Integer
Dim emplRng As Range
Dim atndCnt As Integer
Dim varAnswer As String
'freeze panes
Range("C2").Select
ActiveWindow.FreezePanes = True
Set emplRng = Range(Range("b2"), Range("b2").End(xlDown))
lastRow = Range(Range("b2"), Range("b2").End(xlDown)).Count + 1
Rows(lastRow).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Selection.ClearContents
'Fill Cells
'Employee Name
Cells(Selection.Row, 2).Value = tbxEmplNm.Value
'Id
Cells(Selection.Row, 3).Value = tbxEmplId.Value
'PROBLEM START--------------------------------------------------------------------------------------------
'Department
Cells(Selection.Row, 4).Value = lstEmplDept.Value
'Work Environment
Cells(Selection.Row, 5).Value = lstWrkEnv.Value
'PROBLEM END
'Relocate userform
With ActiveWindow
Me.Left = 120 '.PointsToScreenPixelsX(ActiveCell.Left) + 250 + topicSel
Me.top = 250 '.PointsToScreenPixelsY(ActiveCell.top) + 90 'ActiveCell.Height
End With
End Sub
My guess is that I cannot use the value property in this way but reading through the help and on line I cannot find a simple way to extract a single value from a listbox.
Any help will be appreciated.
Robert
Bookmarks