Invalid Property Value

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,356
Office Version
  1. 2016
Platform
  1. Windows
I'm getting 'Invalid Property Value' when I try to click into a Combobox on a Userform - the error comes up repeatedly for about 5-6 times before it disappears and the only way I can get out of the loop is to unload the form using the VBE.

I'm using this to populate the Combobox;

Code:
With Combobox1
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
End With

then this from a commandbutton which populates the worksheet with values before clearing the form for more data to be entered;

Code:
Private Sub CommandButton3_Click()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Dim iRow As Long
    Dim r As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ws.Cells(iRow, 1).Value = Me.TextBox1.Value
    If CheckBox1.Value = True Then
        ws.Cells(iRow, 2).Value = "A"
    End If
    If CheckBox2.Value = True Then
        ws.Cells(iRow, 2).Value = "B"
    End If
    If CheckBox3.Value = True Then
        ws.Cells(iRow, 2).Value = "C"
    End If
    If CheckBox4.Value = True Then
        ws.Cells(iRow, 2).Value = "D"
    End If
    If CheckBox5.Value = True Then
        ws.Cells(iRow, 2).Value = "E"
    End If
    If CheckBox6.Value = True Then
        ws.Cells(iRow, 3).Value = "1"
    End If
    If CheckBox7.Value = True Then
        ws.Cells(iRow, 3).Value = "2"
    End If
    If CheckBox8.Value = True Then
        ws.Cells(iRow, 3).Value = "3"
    End If
    If CheckBox18.Value = True Then
        ws.Cells(iRow, 3).Value = "4"
    End If
    If CheckBox19.Value = True Then
        ws.Cells(iRow, 3).Value = "5"
    End If
    ws.Cells(iRow, 4).Value = Me.ComboBox1.Value
    ws.Cells(iRow, 5).Value = Me.txtlocation.Value
    ws.Cells(iRow, 6).Value = Me.ComboBox2.Value
    ws.Cells(iRow, 7).Value = Me.ComboBox3.Value
    If CheckBox17.Value = True Then
        ws.Cells(iRow, 8).Value = "1"
    End If
    ws.Cells(iRow, 9).Value = Me.ComboBox4.Value
    ws.Cells(iRow, 10).Value = Me.ComboBox5.Value
    ws.Cells(iRow, 11).Value = Me.ComboBox6.Value
    ws.Cells(iRow, 12).Value = Me.ComboBox7.Value
    ws.Cells(iRow, 13).Value = Me.ComboBox8.Value
    ws.Cells(iRow, 14).Value = Me.txtcomments.Value
    ws.Cells(iRow, 15).Value = Environ("UserName")
    r = Sheet73.Range("O" & Rows.Count).End(xlUp).Row + 20
    ws.Range("P8:P" & r).Formula = "=IF(A8,MONTH(A8),"""")"
    ws.Range("Q8:Q" & r).Formula = "=IF(A8,YEAR(A8),"""")"
    Me.ComboBox1.Value = ""
    Me.txtlocation.Value = ""
    Me.ComboBox2.Value = ""
    Me.ComboBox3.Value = ""
    CheckBox17.Value = False
    Me.ComboBox4.Value = ""
    Me.ComboBox5.Value = ""
    Me.ComboBox6.Value = ""
    Me.ComboBox7.Value = ""
    Me.ComboBox8.Value = ""
    Me.txtcomments.Value = ""
    CheckBox6.Value = False
    CheckBox7.Value = False
    CheckBox8.Value = False
    CheckBox18.Value = False
    CheckBox19.Value = False

    CheckBox6.BackColor = &H8000000F
    CheckBox7.BackColor = &H8000000F
    CheckBox8.BackColor = &H8000000F
    CheckBox18.BackColor = &H8000000F
    CheckBox19.BackColor = &H8000000F

    ComboBox1.Enabled = False
    ComboBox2.Enabled = False
    ComboBox3.Enabled = False
    ComboBox4.Enabled = False
    ComboBox5.Enabled = False
    ComboBox6.Enabled = False
    ComboBox7.Enabled = False
    ComboBox8.Enabled = False
    ComboBox1.BackColor = &H8000000F
    ComboBox2.BackColor = &H8000000F
    ComboBox3.BackColor = &H8000000F
    ComboBox4.BackColor = &H8000000F
    ComboBox5.BackColor = &H8000000F
    ComboBox6.BackColor = &H8000000F
    ComboBox7.BackColor = &H8000000F
    ComboBox8.BackColor = &H8000000F
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Frame4.SetFocus

End Sub

The first time the form runs the user can enter data fine, it's only when they try to enter data after the form has cleared that the error appears.

For info if it is connected the MatchRequired property for the Combobox is set to False and if I set it to true it makes no difference.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Could be the style of Combobox or that "Mactch Required" is set to true and since the control is empty, you can't match it by making an entry.
 

Forum statistics

Threads
1,176,256
Messages
5,902,182
Members
434,945
Latest member
ngabrieln

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