VB If statement that skips if nothing selected

[aka_krakur]

I have a userform that for selections made by user there is VB written to send this info to certain fields on the Excel Spreadsheet:

here is an example of that code:

'Fill in Criteria as selected
'Device Code

Dim i As Long, N As Long, a()
With CriteriaForm1.DeviceCodeListBox

For i = 0 To DeviceCodeListBox.ListCount - 1
    If DeviceCodeListBox.Selected(i) Then
        ReDim Preserve a(N)
        a(N) = DeviceCodeListBox.List(i)
        N = N + 1
    End If
Next
End With

Sheets("ReportableQuery").Range("B6").Resize(, N) = a

There came up a scenario that if the user either intentionally or accidentally didn't select anything from the list box the VB Code will bail because it's looking for something to fille in Row 6 (Starting in field B6).

So is there a way to insert another if statment that if value = "" to skip?

 

[aka_krakur]

Or even better yet; if a message box could appear when they click ok (if nothing was selected) that prompts them that they must select some criteria.

 

[Norie]

Try this.

If CriteriaForm1.DeviceCodeListBox.ListIndex = -1 Then
    Msgbox "No device code(s) selected."
    Exit Sub
End If

 

[Joe Was]

'Fill in Criteria as selected
'Device Code

Dim i As Long, N As Long, a()
With CriteriaForm1.DeviceCodeListBox

For i = 0 To DeviceCodeListBox.ListCount - 1
If DeviceCodeListBox.Selected(i) Then
ReDim Preserve a(N)
a(N) = DeviceCodeListBox.List(i)
N = N + 1
End If
Next

If N = 0 Then GoTo myNext

End With

Sheets("ReportableQuery").Range("B6").Resize(, N) = a

myNext:

If CriteriaForm1.DeviceCodeListBox.ListIndex = -1 Then
Msgbox "No device code(s) selected."
End If

'Then any other things you want to run!

 

[aka_krakur]

How do I insert that code in with the original code (I need both in there)

original code:

'Fill in Criteria as selected 
'Device Code 

Dim i As Long, N As Long, a() 
With CriteriaForm1.DeviceCodeListBox 

For i = 0 To DeviceCodeListBox.ListCount - 1 
    If DeviceCodeListBox.Selected(i) Then 
        ReDim Preserve a(N) 
        a(N) = DeviceCodeListBox.List(i) 
        N = N + 1 
    End If 
Next 
End With 

Sheets("ReportableQuery").Range("B6").Resize(, N) = a

 

[Joe Was]

See above for Edit!

 

[aka_krakur]

Thanks, I got yours after reposting to Norie. Thanks.

 

[Norie]

aka

This would be my take on it.

Dim i As Long, N As Long, a()
With CriteriaForm1.DeviceCodeListBox
   
    If .ListIndex = -1 Then
        MsgBox "No device code(s) selected."
        Exit Sub
    End If

    For i = 0 To DeviceCodeListBox.ListCount - 1
        If DeviceCodeListBox.Selected(i) Then
            ReDim Preserve a(N)
            a(N) = DeviceCodeListBox.List(i)
            N = N + 1
        End If
    Next
End With

Sheets("ReportableQuery").Range("B6").Resize(, N) = a

 

[Joe Was]

The main difference between the two sets of code is in Norie's code The "Exit Sub" stops the code right there and my verson continues. So, if you have more code below what you posted then use my version. If you do not have more to do after this test then use Norie's.

 

[aka_krakur]

Got it.

Hey one more question on the same subject line.
I also have a date field for the user to enter the date.
I need some type of IF statement that says if they input the incorrect date (for example 9/31/06 --doesn't exist)
However, I believe the error comes up due to the query that Excel trys to run based off the information. Anyway, just wasn't sure if there was some type of "stop and retry" on the date if it's not a true calendar date.

Is it possible?