VBA: Can't transfer list box value after adding data validation

Wednesday, July 2, 2014

I have a fully functioning userform that transfers values to a worksheet when hitting a 'Submit' button. This has worked fine when data validation was applied to all textboxes (a message box appears asking for a value to be entered), and with no data validation on the list box.


However, since adding in data validation for the list box (see code below), the 'Submit' button transfers only the pre-selected value in the form builder, rather than the selection. E.g. if nothing is selected by default, then I make a selection, it transfers an empty cell. If 'Alex' is selected by default and I select 'Hannah' once the form is open, it will transfer 'Alex'.


Does anyone have any ideas as to why the selected item in the list box isn't being picked up? All other values transfer fine (i have about 10 other textboxes with no issues). Is this an issue with the data validation?


My code looks like this:



Private Sub cbSubmit_Click()

'Name Selection Data Validation
If Not IsAnythingSelected(lbName) Then
MsgBox "Please select your name"
Exit Sub
End If
Unload Me

'Begin Transfer Information and Change Workbook
Dim nwb As Workbook
Set nwb = Workbooks.Open("G:\Test Dataset.xlsx")

'Determine emptyRow
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(nwb.Sheets("daily_tracking_dataset").Range("A:A")) + 1

'Transfer Information
With nwb.Sheets("daily_tracking_dataset")
'Date
.Cells(emptyRow, 1).Value = CDate(txtDate.Text)
'Name
.Cells(emptyRow, 2).Value = lbName.Value
'Reference Checkss
.Cells(emptyRow, 3).Value = txtROT.Value
.Cells(emptyRow, 4).Value = txtROP.Value

End With

ActiveWorkbook.Save
ActiveWindow.Close
UserForm1.Hide
ActiveWindow.Close
End Sub


With the following function to determine if there is an item in the list box is selected:



'Function to loop through Name list box and find a selection
Function IsAnythingSelected(lbName As Control) As Boolean
Dim i As Long
Dim selected As Boolean
selected = False
For i = 1 To lbName.ListCount
If lbName.selected(i) Then
selected = True
Exit For
End If
Next i
IsAnythingSelected = selected
End Function
End If






http://ift.tt/1qpftHg