+ Reply to Thread
Results 1 to 7 of 7

Listbox.value is null

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Listbox.value is null

    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

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Listbox.value is null

    Robert

    Are the listboxes multiselect?
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Listbox.value is null

    Quote Originally Posted by Norie View Post
    Robert

    Are the listboxes multiselect?
    Norie,
    No, they are single.
    Attaching the file. the Form is initiated by the "Add Employee" button at the bottom of the data range on the first sheet.
    Thanks for helping
    Attached Files Attached Files
    Last edited by Hammer_757; 12-31-2012 at 09:16 AM.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Listbox.value is null

    that works for me as long as you actually select (ie highlight) an item in each listbox-which I suspect you aren't
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Listbox.value is null

    Your right Joseph, I wasn't actually highlighting the choice. Which means, my users probably will make the same mistake. I need to rethink this approach.
    Thanks for setting me strait!
    Robert

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Listbox.value is null

    Robert

    Wouldn't it make more sense to use comboboxes if you are only going to display one value?

  7. #7
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: Listbox.value is null

    Yes Norie, I'm shifting them of to comboboxes

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1