Could not set the List property. Permission denied - Run time error 70.

V

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
171
Office Version
  1. 365
Platform
  1. Windows
I am missing assigning the list property for a class module to work. Hoping someone can point out what I need to add.

I lifted a module from the internet to align columns independently in a listbox of a userform. I will show the initialize code first and the class module code second, as the problem seems to lie in the initialization.

I get the error on the bottom lines of code : MyListBoxClass.Center Me.ListBox1, 2

VBA Code: 
Option Explicit

Dim DatePickerX_Ctrls() As cDatePickerX
Private MyListBoxClass As CListboxAlign


Private Sub UserForm_Initialize()

Call Clear
DatePickerX_Ini
'This assigns the drop downs to all the actionable fields on the userform

    Dim Fields As Worksheet
    Dim UF As Worksheet
    Dim SourceTable As Worksheet
    Dim i As Integer
    Dim lngRow As Long
    Dim lngIndex As Long
    Set MyListBoxClass = New CListboxAlign
    
    Set Fields = ThisWorkbook.Sheets("Fields Lists")
    Set UF = ThisWorkbook.Sheets("User Form")
    Set SourceTable = ThisWorkbook.Sheets("Tables")
    
    Me.CustomerBox.Clear
    Me.CustomerBox.List = SourceTable.Range("CustomerTable").Columns(1).Value
             
    Me.UserList.Clear
    For i = 2 To Fields.Range("A" & Application.Rows.Count).End(xlUp).Row
        Me.UserList.AddItem Fields.Range("A" & i).Value
    Next i
    
    Me.TimeBox.Clear
    For i = 2 To Fields.Range("C" & Application.Rows.Count).End(xlUp).Row
        Me.TimeBox.AddItem Fields.Range("C" & i).Text
    Next i
    
    Me.ShipMethodBox.Clear
    For i = 2 To Fields.Range("D" & Application.Rows.Count).End(xlUp).Row
        Me.ShipMethodBox.AddItem Fields.Range("D" & i).Value
    Next i
    
    Me.BoxLabelBox.Clear
    For i = 2 To Fields.Range("J" & Application.Rows.Count).End(xlUp).Row
        Me.BoxLabelBox.AddItem Fields.Range("J" & i).Value
    Next i
    
    Me.ProductBox.Clear
    Me.ProductBox.List = SourceTable.Range("ProdTable").Columns(1).Value
                      
    Me.EntryDate.Clear
    Me.EntryDate.Value = UF.Range("A1")
    Me.EntryDate.Value = Format(Me.EntryDate.Value, "[$-en-CA]d-mmm-yyyy;@")
    
    CoverBox.Enabled = False
    OrnamentBox.Enabled = False
    UPCBox.Enabled = False
    PackSizeBox.Enabled = False
    ColourBox.Enabled = False
    AssBox.Enabled = False
    StageBox.Enabled = False
    PackSizeBox.Enabled = False
    BoxLabelBox.Enabled = False
           
    
    MyListBoxClass.Center Me.ListBox1, 2
    MyListBoxClass.Center Me.ListBox1, 3
           
           
           
End Sub

And the class module that it pulls from named CListboxAlign:
VBA Code: 
Option Explicit
'----------------------------------------------------------------------------------------------
'---Developer: Ryan Wells (wellsr.com)---------------------------------------------------------
'---Date: 05/2017-------------------------------------------------------------------------
'---Class: CListboxAlign-------------------------------------------------------------------
'---Purpose: Align the text in different columns in a UserForm ListBox differently-----------
'--- This has been adapted from a few sources I stumbled across many moons ago but---
'--- I don't recall the sources.-----------------------------------------------------
'----------------------------------------------------------------------------------------------

Public Sub Center(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 0)
'
' PURPOSE: Center align the text in a listbox column
' HOW TO USE:
' - First argument is the listbox you want to adjust, the second optional argument is which
' column in the listbox you want to align.
' - To use this procedure, you would place a statement like the following in your UserForm_Initialize routine:
' MyListBoxClass.Center Me.ListBox1, 1
'
    Dim labSizer As MSForms.Label
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    Dim vntColWidths As Variant
    
    ' get label control to help size text
    Set labSizer = m_GetSizer(LBox.Parent)
    If labSizer Is Nothing Then Exit Sub
    
    ReDim sngWidth(LBox.ColumnCount) As Single
    If Len(LBox.ColumnWidths) > 0 Then
        ' decode column widths
        vntColWidths = Split(LBox.ColumnWidths, ";")
        ' fudge for gap between cols
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = Val(vntColWidths(intColumn - 1)) - 5
        Next
    Else
        ' assume default sizes
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If
    
    ' generic font attributes
    With labSizer
        With .Font
            .Name = LBox.Font.Name
            .Size = LBox.Font.Size
            .Bold = LBox.Font.Bold
        .Italic = LBox.Font.Italic
        End With
        .WordWrap = False
    End With
    
    ' begin processing column width to center align
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then
            ' if you say to center align this column or center align all columns
            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                labSizer.Width = LBox.Width
                labSizer.Caption = Trim(LBox.List(lngIndex, intColumn - 1))
                labSizer.AutoSize = True
                Do While labSizer.Width < sngWidth(intColumn)
                    labSizer.Caption = " " & labSizer.Caption & " "
                Loop
                LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    
    LBox.Parent.Controls.Remove labSizer.Name
    Set labSizer = Nothing

End Sub

Public Sub Left(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 0)
'
' PURPOSE: Left align the text in a listbox column
' HOW TO USE:
' - First argument is the listbox you want to adjust, the second optional argument is which
' column in the listbox you want to align.
' - To use this procedure, you would place a statement like the following in your UserForm_Initialize routine:
' MyListBoxClass.Left Me.ListBox1, 1
'
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    Dim vntColWidths As Variant
    ReDim sngWidth(LBox.ColumnCount) As Single
    
    If Len(LBox.ColumnWidths) > 0 Then
        ' decode column widths
        vntColWidths = Split(LBox.ColumnWidths, ";")
        ' fudge for gap between cols
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = Val(vntColWidths(1)) - 5
        Next
    Else
        ' assume default sizes
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If
    
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then
            ' if you say to left align this column or left align all columns
            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                LBox.List(lngIndex, intColumn - 1) = Trim(LBox.List(lngIndex, intColumn - 1))
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    
End Sub

Public Sub Right(LBox As MSForms.ListBox, Optional WhichColumn As Integer = 1)
'
' PURPOSE: Right align the text in a listbox column
' HOW TO USE:
' - First argument is the listbox you want to adjust, the second optional argument is which
' column in the listbox you want to align.
' - To use this procedure, you would place a statement like the following in your UserForm_Initialize routine:
' MyListBoxClass.Right Me.ListBox1, 1
'
    Dim labSizer As MSForms.Label
    Dim lngIndex As Long
    Dim intColumn As Integer
    Dim lngTopIndex As Long
    Dim vntColWidths As Variant
    ' get label control to help size text
    Set labSizer = m_GetSizer(LBox.Parent)
    If labSizer Is Nothing Then Exit Sub
    
    ReDim sngWidth(LBox.ColumnCount) As Single
    If Len(LBox.ColumnWidths) > 0 Then
        ' decode column widths
        vntColWidths = Split(LBox.ColumnWidths, ";")
        ' fudge for gap between cols
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = Val(vntColWidths(1)) - 5
        Next
    Else
        ' assume default sizes
        For intColumn = 1 To LBox.ColumnCount
            sngWidth(intColumn) = (LBox.Width - (15 * LBox.ColumnCount)) / LBox.ColumnCount
        Next intColumn
    End If
    
    With labSizer
        With .Font
            .Name = LBox.Font.Name
            .Size = LBox.Font.Size
            .Bold = LBox.Font.Bold
        .Italic = LBox.Font.Italic
        End With
        .WordWrap = False
    End With
    
    lngTopIndex = LBox.TopIndex
    For intColumn = 1 To LBox.ColumnCount
        If intColumn = WhichColumn Or WhichColumn = -1 Then
            'if you say to right align this column, or right align all columns
            For lngIndex = 0 To LBox.ListCount - 1
                LBox.TopIndex = lngIndex
                labSizer.Width = LBox.Width
                labSizer.Caption = Trim(LBox.List(lngIndex, intColumn - 1))
                labSizer.AutoSize = True
                Do While labSizer.Width < sngWidth(intColumn)
                    labSizer.Caption = " " & labSizer.Caption
                Loop
                LBox.List(lngIndex, intColumn - 1) = labSizer.Caption
            Next lngIndex
        End If
    Next intColumn
    LBox.TopIndex = lngTopIndex
    LBox.Parent.Controls.Remove labSizer.Name
    Set labSizer = Nothing

End Sub

Private Property Get m_GetSizer(Base As MSForms.UserForm) As MSForms.Label
    Set m_GetSizer = Base.Controls.Add("Forms.Label.1", "labSizer", True)
End Property
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Does the listbox have its Rowsource property set?
 
Upvote 0
That's your issue then. You can't modify the list if it's tied to a range. You have to modify the source cells.
 
Upvote 0
RoryA said:
That's your issue then. You can't modify the list if it's tied to a range. You have to modify the source cells.
Click to expand...
My list box ignores the alignment formatting of the source cells. That is why I tried to use this code.
 
Upvote 0
You can't use Rowsource then. You'll need to load the range in using List (you can't have column headers though if you are using those).
 
Upvote 0
Solution
RoryA said:
You can't use Rowsource then. You'll need to load the range in using List (you can't have column headers though if you are using those).
Click to expand...
All good, it was just a preference for aesthetics. I'll leave it as is then. Thanks for your help again! You are a god of excel among us mortals.
 
Upvote 0
You must log in or register to reply here.

Similar threads

WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
118
RoryA
RoryA
M
  • Question Question
Code works however receiving Runtime error 380 - Could not set the list property. Invalid property value
Replies
2
Views
884
Megg
M
B
  • Question Question
Filtered Listbox run-time error 381 "Could not get the list property"
Replies
0
Views
339
Beanie18
B
J
  • Solved
Run-time error '381': Could not set the List property. Invalid property array index. (RowSource)
Replies
4
Views
1K
jeffcdo
J
R
  • Question Question
Listbox Error with only 5 columns (Could not set list property. Invalid property error)
Replies
7
Views
4K
RawlinsCross
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top