'Run Time Error '380': Could not set the value property. Invalid value property'

[Peter Thompson]

Hello,

I am currently developing a user form and am getting a very strange error message.

When it goes to retrieve data it gives me the message 'Run Time Error '380': Could not set the value property. Invalid value property'. When i hit the debug the following peiece of code is causing the issue:

'lst4Complexity = lngComplexity'

I cant understand what the problem could be as lngComplexity, in this instance, has a value of 5 and lst4Complexity is a listbox with options 1 to 5.

Has anyone had similar issues? Or can anyone suggest a solution?

 

[Norie]

I think you need to tell us what you ate doing when this happen.

You have a listbox somewhere but where?

Userform? Worksheet?

Also how did you populate the listbox?

One thing I did notice us that you don't actually use Value anywhere.

 

[Peter Thompson]

Norje,

The lstbox is on a userform and it is populated by a macro that uses a named range in the worksheet.

The userform is used to both input data to a worksheet, as well as retrieing data from the worksheet for viewing/editing. When the macro retrieves the data from the sheet it assigns it to a variable 'lngComplexity'
and then should use this to select the option from the listbox.

I have also tried setting it directly using 'lst4Complexity = 5'

I have also tried using lst4Complexity.value = 5' and it made no difference.

I just cant understand why an option that shows in the listbox cant be applied by the macro.

 

[Andrew Poulsom]

I can reproduce your error if the ListBox's MulktiSelect property is set to something other than fmMultiSelectSingle.

 

[Peter Thompson]

Andrew,

Ive looked at it and it appears my option is set correctly to fmMultiSelectSingle .

Have you got any other ideas?

Thanks,

Peter

 

[Norie]

Peter

You mention a macro is populating the listbox?

How is it doing that?

Is it setting the RowSource of the listbox based on some criteria?

 

[Peter Thompson]

Norje,

Firstly, the macro call another sub called Populate list boxes passing it the name of the named range which contain the value to populate the listbox with and the name of the lstbox.

Call PopulateListBoxes("Complexity", frmBriefing.lst4Complexity)

This sub then populates the list box as below.

Sub PopulateListBoxes(ByVal strName As String, ByRef listbox As MSForms.listbox)

Dim vListSource As Variant
Dim rngRepSheet As Range
Set rngRepSheet = ThisWorkbook.Names(strName).RefersToRange
With rngRepSheet
vListSource = .Value
End With
listbox.List = vListSource

End Sub

 

[Andrew Poulsom]

For some reason the error occurs only if the ListBox is filled using the List property. It doesn't occur if the ListBox is filled using the AddItem method or the RowSource property. Sorry, I don't know why that should be.

 

[Norie]

Peter

I get the same error.

I came up with this workaround:

Dim lngComplexity As Long
Dim lngIndex As Variant
     lngComplexity = 5
     
    lngIndex = Evaluate("=MATCH(" & lngComplexity & ",Complexity,0)")
    
    If Not IsError(lngIndex) Then
        lst4Complexity.ListIndex = lngIndex - 1
    End If

If finds the index of the value you want to select within the range using MATCH and then uses that to select it in the listbox.

 

[Peter Thompson]

Norje,

Thanks for the suggestion. Ive tried it and it works. I was wondering if you could give an explanation as to how it works.

For example when I retrieve one record the value of complecity for that record is 5, which the code populates in the listbox, but I dont understand how it does this as when I step through the code lngindex = 5 and the -1 then takes it down to 4.

Thanks,

Peter