In Excel, if no value needs to be selected from a listbox in a userform, how can I prevent the contents of a range from being cleared?

0
votes

For example, there is a listbox to select the Sample Type if it needs to be changed in the spreadsheet with existing data. I have no code for the listbox, but have made some changes in the properties to identify the source of the list and leave as a single selection.

There is a command button with code behind it to send all the listbox and textbox values that the user entered to the active sheet.

Private Sub cmdSendPIDdata_Click()
Cells.AutoFilter
 'Advance to last row
 Range("B1").End(xlDown).Offset(1, 0).Select

 'Constants and user data
Range("B" & (ActiveCell.Row)).Value = "'" & SamplePointID
Range("J" & (ActiveCell.Row)).Value = "'PID MEASUREMENT"
Range("N" & (ActiveCell.Row)).Value = "'PPB"
Range("F" & (ActiveCell.Row)).Value = "'" & dtpSampleDate.Value
Range("G" & (ActiveCell.Row)).Value = "'" & txtTime.Value
Range("H" & (ActiveCell.Row)).Value = "'" & lbxSampleType.Value
Range("K" & (ActiveCell.Row)).Value = "'" & txtConcentration.Value
Range("AK" & (ActiveCell.Row)).Value = "'" & lbxSampleLocation.Value
Range("AN" & (ActiveCell.Row)).Value = "'" & lbxSampledBy.Value

Unload Me
frmPIDDataEntry.Show

End Sub

If nothing is selected in the list box, it's clearing the existing value in the corresponding range.

2
excellistboxuserformvba

2 Answers

0
votes

You could add an IF statement to check for the list box value and then Exit if you see it is =nothing. The question is a little vague, so my answer is too.

Private Sub cmdSendPIDdata_Click()

' Add something like this.
If ListBox.value = vbnullstring then Exit Sub

Cells.AutoFilter
 'Advance to last row
 Range("B1").End(xlDown).Offset(1, 0).Select

 'Constants and user data
Range("B" & (ActiveCell.Row)).Value = "'" & SamplePointID
Range("J" & (ActiveCell.Row)).Value = "'PID MEASUREMENT"
Range("N" & (ActiveCell.Row)).Value = "'PPB"
Range("F" & (ActiveCell.Row)).Value = "'" & dtpSampleDate.Value
Range("G" & (ActiveCell.Row)).Value = "'" & txtTime.Value
Range("H" & (ActiveCell.Row)).Value = "'" & lbxSampleType.Value
Range("K" & (ActiveCell.Row)).Value = "'" & txtConcentration.Value
Range("AK" & (ActiveCell.Row)).Value = "'" & lbxSampleLocation.Value
Range("AN" & (ActiveCell.Row)).Value = "'" & lbxSampledBy.Value

Unload Me
frmPIDDataEntry.Show

End Sub
0
votes

Perhaps adding to @twenger's post, you might try something as simple as the following. You'll have to add this to each range that has ListBox entry info, and it might seem a little bulky to a programmer, but it works for me. Hope this gives you some ideas or gets you in the right direction.

myRow = ActiveCell.Row
result1 = ListBox1.Value

If result1 = vbnullstring Then
  Range("B" & myRow).Value = SamplePointID
Else
  Range("B" & myRow).Value = result1 & " " & SamplePointID
End If