VB If statement that skips if nothing selected

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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:
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
If CriteriaForm1.DeviceCodeListBox.ListIndex = -1 Then
    Msgbox "No device code(s) selected."
    Exit Sub
End If
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
'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

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

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

original code:
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
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
aka

This would be my take on it.
Code:
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Active Member
Joined
Jan 31, 2006
Messages
438
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?
 

Forum statistics

Threads
1,136,420
Messages
5,675,751
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top