CODEWITHSUNDEEP
vbaexcel
Sam WB
Sam WB

Reputation: 195

Excel VBA: Could not set the list property error for coworkers comp.. However it works on mine?

All,

I've recently built a worksheet mostly run by VBA macros (built in Excel 2007). One of the functions of this sheet is that when a user clicks a button, it runs a macro which populates a listbox in a form, then shows the form and allows them to do a couple things related to that list.

This macro works fine on my computer and also for 3 or 4 other users' computers that I've tested it on in my office.

However, when a user located in Poland tries to use it, he receives a 'Could not set the list property. Type Mismatch' error when he clicks the button.

He's using Office 2010 Plus. I've tested this on a coworkers computer in my office who's also using 2010 and it worked fine.

The user getting the error does appear to be using a Polish version of excel, so I'm not sure if this is contributing to the problem? The following is the code that's returning the error:

Sub AccountAssignmentChange()
count = 0
AssignAccounts.ExecName.Caption = "Account Management for " _
    & ActiveSheet.Range("D4").Value
For i = 2 To 9
    If Not Sheet1.Cells(i, 33) = "" Then
        With AssignAccounts.RegionBox
            .AddItem
            .List(count) = Sheet1.Cells(i, 33)
            count = count + 1
        End With
    End If
Next
AssignAccounts.Show
End Sub

For clarification, Activesheet.Range("D4").value is an employee name. Sheet1.Cells(i,33) (for 2 to 9) is a range containing region names that get populated into the list box.

The employee getting the error has access to one region (this means the list box should be populated with one item). I've changed my access to only that region and had no problems. Also did so for the user using 2010 in my office and it worked fine for him.

Also, here is a screenshot of the error the employee sent me.

Any ideas? Pretty lost with this error.

EDIT: I now have an (I'm assuming) unrelated development. This particular employee is now receiving an error stating "Microsoft Excel is waiting for another application to complete an OLE action".

I'm also stumped on why this error is getting thrown. For info: There's a macro that runs on workbook open that opens another worksheet that sits on a shared drive. The shared drive is already mapped to this employee's computer, so that's not an issue.

The workbook being accessed remotely is a shared workbook because multiple people may be using this worksheet at the same time.

This employee is receiving the OLE error when the macro tries to open the workbook. When he hits 'OK' the notification stays there and effectively hangs.

I have no idea why this is happening. I thought there might be a problem with two people trying to access the shared workbook at the same time, but I tested this with a couple coworkers located in my office, and it worked without issue.

Any ideas on this? This particular employee is having issues that totally stump me.

Upvotes: 1

Views: 4434

Answers (1)

Brian B.
Brian B.

Reputation: 160

Use the below (edited) code. You need to keep in mind that ListBoxes are like grids, the .List property takes a Column and Row parameter... i.e. .List(1, 0)...

ALSO - make sure that the user in Poland has the reference (Microsoft Office 14.0 Object Library) added to his workbook...

Sub AccountAssignmentChange()
count = 0
AssignAccounts.ExecName.Caption = "Account Management for " _
    & ActiveSheet.Range("D4").Value
For i = 2 To 9
    If Not Sheet1.Cells(i, 33) = "" Then
        With AssignAccounts.RegionBox
            .AddItem 0 'Speficy the list reference (top) 
            .List(count, 0) = Sheet1.Cells(i, 33) 'Call the correct list...
            count = count + 1
        End With
    End If
Next
AssignAccounts.Show
End Sub

Upvotes: 0

Related Questions