Run time Error 380, despite the value being calculated correctly.

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Encountered a weird issue today as I added editing features to my UserForm. The fourth ComboBox is giving me a property value error when I try to populate it, despite the previous three working and the VBA editor calculating the value correctly.

Untitled.png

Untitled2.png


The right side is returning a value, but the left side is giving me the invalid property. Code is as follows:
VBA Code:
Private Sub EditButton_Click()

    If Selected_List = 0 Then
        
        MsgBox "No selection.", vbOKOnly + vbInformation, "Edit"
        Exit Sub
        
    End If
    
    Dim cSelect As Variant
    Dim oSelect As Variant
    Dim uSelect As Variant
    Dim ctSelect As Variant
    Dim iSelect As Variant
    Dim sSelect As Variant
        
    'code to update the value to the respective fields
    Me.txtRowNumber.Value = Selected_List + 1
    
    Me.ProductBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
    Me.PID.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
    Me.CaseQty.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
    Me.PackSizeBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
    Me.StageBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
    Me.AssBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
    Me.ColourBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
    
    cSelect = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
    If cSelect = "" Then
        Me.CoverSelect.Value = False
    Else
        Me.CoverSelect.Value = True And Me.CoverBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
    End If
    
    oSelect = Me.ListBox1.List(Me.ListBox1.ListIndex, 8)
    If oSelect = "" Then
        Me.OrnamentSelect.Value = False
    Else
        Me.OrnamentSelect.Value = True And Me.OrnamentBox.Value = IIf(oSelect = "Yes", Me.OrnamentBox.Value = "", Me.OrnamentBox.Value = oSelect)
    End If
    
    uSelect = Me.ListBox1.List(Me.ListBox1.ListIndex, 9)
    If uSelect = "" Then
        Me.UPCSelect.Value = False
    Else
        Me.UPCSelect.Value = True And Me.UPCBox.Value = IIf(uSelect = "Yes", Me.UPCBox.Value = "", Me.UPCBox.Value = oSelect)
    End If
    
    ctSelect = Me.ListBox1.List(Me.ListBox1.ListIndex, 10)
    If ctSelect = "" Then
        Me.CareTagSelect.Value = False
    Else
        Me.CareTagSelect.Value = True
    End If
    
    iSelect = Me.ListBox1.List(Me.ListBox1.ListIndex, 11)
    If iSelect = "" Then
        Me.InsulationSelect.Value = False
    Else
        Me.InsulationSelect.Value = True
    End If
    
    sSelect = Me.ListBox1.List(Me.ListBox1.ListIndex, 12)
    If sSelect = "" Then
        Me.SleeveSelect.Value = False
    Else
        Me.SleeveSelect.Value = True
    End If
    
    Me.NotesBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
    
    MsgBox "Please make necessary changes and click on 'Add' to save changes.", vbOKOnly + vbInformation, "Edit"

End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It works for me - once there is code to actually populate that control (there isn't in your sample).
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
It works for me - once there is code to actually populate that control (there isn't in your sample).
Can you instruct me what to do? I didn't realize there was no code to populate the control. I thought that the code I had was populating it 😅

The following:

VBA Code:
pkSize = Evaluate("=FILTER(PackSize[Pack Size],PackSize[Product ID]= """ & Me.PID.Value & ""","""")")
Me.PackSizeBox.List = pkSize

Under the PID_Change
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, I think you must have removed it at some point. you assign the listbox value to a variable, but never assign that variable to the control.
 
Solution

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
No, I think you must have removed it at some point. you assign the listbox value to a variable, but never assign that variable to the control.
OMG I am the biggest numpty :poop:

I hang my head in shame...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

We've all been there... :)
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
We've all been there... :)
Still don't understand why that is the only combobox that needed to be treated differently. Maybe because the values were numbers? It shall remain a mystery to me.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I think the others were already set to that?
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
I think the others were already set to that?
I meant of the following:
VBA Code:
    Me.ProductBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
    Me.PID.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
    Me.CaseQty.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
    Me.PackSizeBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
    Me.StageBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
    Me.AssBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
    Me.ColourBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)

All done the exact same way, but only the packsizebox wouldn't work. My solution looks like this:
VBA Code:
pSB = Me.PackSizeBox.Value
pSB = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.PackSizeBox.Value = pSB

I don't get the logic behind why that was necessary, when for example the product box only required the one line: Me.ProductBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Once you've made the style change (which is what I was saying the others already had), you should be able to just use:

Code:
Me.PackSizeBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Once you've made the style change (which is what I was saying the others already had), you should be able to just use:

Code:
Me.PackSizeBox.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Ah, I see. I will take your word. Now that it's working I don't want to touch that bit of code anymore !
 

Watch MrExcel Video

Forum statistics

Threads
1,132,704
Messages
5,654,829
Members
418,155
Latest member
demasisi

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