Fixing run-time error 381 and 13 with a UserForm

[Keojey]

Hello,

I've been having an issue in trying to resolve a couple of issues I've with a UserForm I'm trying to create.

Currently I have two ComboBoxes that change their list data depending on what the other ComboBox's value is :

Top is ComboBox1, bottom is ComboBox2.

Private Sub UserForm_Initialize()

ComboBox1.List = Application.Transpose(Range(Cells(40, 1), Cells(40, Columns.Count).End(xlToLeft)).Value)

ComboBox2.List = Application.Transpose(Range(Cells(40, 1), Cells(40, Columns.Count).End(xlToLeft)).Value)

End Sub

Private Sub ComboBox1_Change()

Dim x As Date
Dim y As Range
Dim z As Long

If ComboBox1.Value = vbNullString Then
Exit Sub
End If

x = Chart.ComboBox1.Value

With Sheets("Chart")
 Set y = .Range("40:40").Find(x, LookIn:=xlValues)
End With

z = y.Column

ComboBox2.List = Application.Transpose(Range(Cells(40, z), Cells(40, Columns.Count).End(xlToLeft)).Value)

End Sub

Private Sub ComboBox2_Change()

Dim x As Date
Dim y As Range
Dim z As Long

If ComboBox2.Value = vbNullString Then
Exit Sub
End If

x = Chart.ComboBox2.Value

With Sheets("Chart")
 Set y = .Range("40:40").Find(x, LookIn:=xlValues)
End With

z = y.Column

ComboBox1.List = Application.Transpose(Range(Cells(40, 1), Cells(40, z)).Value)

End Sub

The only values that are in these ComboBoxes are dates. Currently, the data that it's trying to pull from has every date in a list going from 5-01-2022 to 6-01-2022.
As an example of how this is working:
If ComboBox1 has the date 5-11-2022 entered, then ComboBox2 will only show 5-11-2022 to 6-01-2022 rather than 5-01-2022 to 6-01-2022.
If ComboBox2 has 5-11-2022 entered, ComboBox1 would only should 5-01-2022 to 5-11-2022.

Now, the two issues I'm facing :

1.) When I try to enter any data in the UserForm that isn't a "5" to try and select one of the dates, it crashes because the value of 'x' isn't a date.. When I try to change Dim x to something else like 'Variant', it allows me to add a new value, but then the 'y' value stops working because 'x' isn't a date anymore? I think? My objective to to allow a user to make any typos they may make without having everything break and not knowing what to do. So I'd like to have the UserForm function as if the user only used the drop-down and never actually typed. But when they inevitably do type, it'd be great if this run-time 13 error didn't appear.

2.) When entering the last date in the list, in this case it's 6-01-2022, into ComboBox1 then the UserForm crashes with the run-time 381 error.. same thing if the first date is entered into ComboBox2.

Any help would be appreciated!

Thanks!

 

[dmt32]

Hi
untested but see if this update to one of your codes resolves the issue

Private Sub ComboBox2_Change()
    Dim x           As Variant
    Dim y           As Range
    Dim z           As Long
    Dim wsChart     As Worksheet
   
    x = Me.ComboBox2.Value
    If Not IsDate(x) Then Exit Sub Else x = DateValue(x)
   
    Set wsChart = ThisWorkbook.Worksheets("Chart")
   
    Set y = wsChart.Range("40:40").Find(x, LookIn:=xlValues)
   
    If Not y Is Nothing Then
        z = y.Column
        With wsChart
            Me.ComboBox1.List = Application.Transpose(.Range(.Cells(40, 1), .Cells(40, z)).Value)
        End With
    End If
   
End Sub

if ok, then apply same to other code.

Dave

 

[Keojey]

Hi
untested but see if this update to one of your codes resolves the issue

Private Sub ComboBox2_Change()
    Dim x           As Variant
    Dim y           As Range
    Dim z           As Long
    Dim wsChart     As Worksheet
  
    x = Me.ComboBox2.Value
    If Not IsDate(x) Then Exit Sub Else x = DateValue(x)
  
    Set wsChart = ThisWorkbook.Worksheets("Chart")
  
    Set y = wsChart.Range("40:40").Find(x, LookIn:=xlValues)
  
    If Not y Is Nothing Then
        z = y.Column
        With wsChart
            Me.ComboBox1.List = Application.Transpose(.Range(.Cells(40, 1), .Cells(40, z)).Value)
        End With
    End If
  
End Sub

if ok, then apply same to other code.

Dave

Hey Dave!

This works perfectly for fixing the main issue I had! Such a simple solution just passed right over my head.. Thank you ?

Do you have any idea what I can do to the only other issue I was having? :

2.) When entering the last date in the list, in this case it's 6-01-2022, into ComboBox1 then the UserForm crashes with the run-time 381 error.. same thing if the first date of the range is entered into ComboBox2.

 

[dmt32]

Hey Dave!

This works perfectly for fixing the main issue I had! Such a simple solution just passed right over my head.. Thank you ?

Do you have any idea what I can do to the only other issue I was having? :

Hi,
glad resolved at least, in part

As a guess, maybe values being returned to List is not an array

Not tested but see if this update resolves

Private Sub ComboBox2_Change()
    Dim x           As Variant, arr As Variant
    Dim y           As Range
    Dim z           As Long
    Dim wsChart     As Worksheet
   
    x = Me.ComboBox2.Value
    If Not IsDate(x) Then Exit Sub Else x = DateValue(x)
   
    Set wsChart = ThisWorkbook.Worksheets("Chart")
   
    Set y = wsChart.Range("40:40").Find(x, LookIn:=xlValues)
   
    If Not y Is Nothing Then
        z = y.Column
        With wsChart
            arr = Application.Transpose(.Range(.Cells(40, 1), .Cells(40, z)).Value)
        End With
        If Not IsArray(arr) Then arr = Array(arr)
        Me.ComboBox1.List = arr
    End If
   
End Sub

You will need to apply the same to other combobox

Dave

 

[Keojey]

Hi,
glad resolved at least, in part

As a guess, maybe values being returned to List is not an array

Not tested but see if this update resolves

Private Sub ComboBox2_Change()
    Dim x           As Variant, arr As Variant
    Dim y           As Range
    Dim z           As Long
    Dim wsChart     As Worksheet
  
    x = Me.ComboBox2.Value
    If Not IsDate(x) Then Exit Sub Else x = DateValue(x)
  
    Set wsChart = ThisWorkbook.Worksheets("Chart")
  
    Set y = wsChart.Range("40:40").Find(x, LookIn:=xlValues)
  
    If Not y Is Nothing Then
        z = y.Column
        With wsChart
            arr = Application.Transpose(.Range(.Cells(40, 1), .Cells(40, z)).Value)
        End With
        If Not IsArray(arr) Then arr = Array(arr)
        Me.ComboBox1.List = arr
    End If
  
End Sub

You will need to apply the same to other combobox

Dave

Hey Dave,

It works perfectly! I definitely need to catch myself up with some more Excel lingo though.. the solution to this would have been a lot easier to achieve if I understood how arrays functioned.

Thanks for all the help!

 

[dmt32]

most welcome & appreciate the feedback

Dave