Invalid Property Value

D

dualhcsniatpac

Board Regular
Joined
Feb 18, 2009
Messages
126
OK. I have a form with 5 different comboboxes. I have done some research on this error and it seems to pop up when the combobox is returning a value that is not in its list. I saw something else about MatchRequired affecting it which i do have set to TRUE for all cb's. Also all of my data gets transferred to the data file as it should so there are no problems there. Its just that this pesky error pops up.

Here is the code.

Code: 
Private Sub submit_Click()
    SheetName = Format(Date, "mmmm")
    ' Open the file
        
        Call CreateOpenFile
        
        Sheets(SheetName).Select
        
        Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
        
        ActiveCell.Value = Now()
        ActiveCell.Offset(0, 1) = todaysdate.Value
        ActiveCell.Offset(0, 2) = coverunit.Value
        ActiveCell.Offset(0, 3).NumberFormat = "@"
        ActiveCell.Offset(0, 3).Value = Format(coverdate1.Value, "00") & "/" & Format(Coverdate2.Value, "00")
        'ActiveCell.Offset(0, 3) = coverdate1.Value & "/" & Coverdate2.Value
        'ActiveCell.Offset(0, 3).NumberFormat = "mm/dd"
        ActiveCell.Offset(0, 4) = covertime.Value
        ActiveCell.Offset(0, 5) = juliandate.Value
        ActiveCell.Offset(0, 6) = juliantime.Value
        
        If tacknone.Value = True Then
            ActiveCell.Offset(0, 7).Value = 1
        Else
            ActiveCell.Offset(0, 7).Value = 0
        End If
        
        If tack1.Value = True Then
            ActiveCell.Offset(0, 8).Value = 1
        Else
            ActiveCell.Offset(0, 8).Value = 0
        End If
        
        If tack2.Value = True Then
            ActiveCell.Offset(0, 9).Value = 1
        Else
            ActiveCell.Offset(0, 9).Value = 0
        End If
        
        If tack3.Value = True Then
            ActiveCell.Offset(0, 10).Value = 1
        Else
            ActiveCell.Offset(0, 10).Value = 0
        End If
        
        If tack4.Value = True Then
            ActiveCell.Offset(0, 11).Value = 1
        Else
            ActiveCell.Offset(0, 11).Value = 0
        End If
        
        ' EB Welder outputs
        ''''''''''''''
        If Yellow0.Value = True Then
            ActiveCell.Offset(0, 12).Value = 0
        End If
        If Yellow1.Value = True Then
            ActiveCell.Offset(0, 12).Value = 1
        End If
        If Yellow2.Value = True Then
            ActiveCell.Offset(0, 12).Value = 2
        End If
        If Yellow3.Value = True Then
            ActiveCell.Offset(0, 12).Value = 3
        End If
        ''''''''''''''
        If Blue0.Value = True Then
            ActiveCell.Offset(0, 13).Value = 0
        End If
        If Blue1.Value = True Then
            ActiveCell.Offset(0, 13).Value = 1
        End If
        If Blue2.Value = True Then
            ActiveCell.Offset(0, 13).Value = 2
        End If
        If Blue3.Value = True Then
            ActiveCell.Offset(0, 13).Value = 3
        End If
        ''''''''''''''
        If Orange0.Value = True Then
            ActiveCell.Offset(0, 14).Value = 0
        End If
        If Orange1.Value = True Then
            ActiveCell.Offset(0, 14).Value = 1
        End If
        If Orange2.Value = True Then
            ActiveCell.Offset(0, 14).Value = 2
        End If
        If Orange3.Value = True Then
            ActiveCell.Offset(0, 14).Value = 3
        End If
        '''''''''''''
        If Violet0.Value = True Then
            ActiveCell.Offset(0, 15).Value = 0
        End If
        If Violet1.Value = True Then
            ActiveCell.Offset(0, 15).Value = 1
        End If
        If Violet2.Value = True Then
            ActiveCell.Offset(0, 15).Value = 2
        End If
        If Violet3.Value = True Then
            ActiveCell.Offset(0, 15).Value = 3
        End If
        If mass1.Value = True Then
            ActiveCell.Offset(0, 17).Value = 1
        End If
        If mass2.Value = True Then
            ActiveCell.Offset(0, 17).Value = 2
        End If
        If mass3.Value = True Then
            ActiveCell.Offset(0, 17).Value = 3
        End If
        If mass5.Value = True Then
            ActiveCell.Offset(0, 17).Value = 5
        End If
       
        ActiveCell.Offset(0, 16) = pumpcolor.Value
        ActiveCell.Offset(0, 18) = typeofdefect.Value
        
    ActiveWorkbook.Close True
            
    Call UserForm_Initialize
        
End Sub
 

Private Sub UserForm_Initialize()
    
    'Initialize Text Boxes Here
        coverunit.Value = ""
        coverdate1.Value = ""    
        Coverdate2.Value = ""
        covertime.Value = ""
        
        juliandate.Value = ""
        juliantime.Value = ""
        
        comments.Value = ""
        
        todaysdate.Value = Format(Now, "mm/dd/yyyy")
    
    ' Initialize drop down menus
        
        With coverunit
            .AddItem "1"
            .AddItem "2"
            .AddItem "3"
            .AddItem "4"
        End With
        
        With coverdate1
            .AddItem "01"
            .AddItem "02"
            .AddItem "03"
            .AddItem "04"
            .AddItem "05"
            .AddItem "06"
            .AddItem "07"
            .AddItem "08"
            .AddItem "09"
            .AddItem "10"
            .AddItem "11"
            .AddItem "12"
        End With
        
        With Coverdate2
            .AddItem "01"
            .AddItem "02"
            .AddItem "03"
            .AddItem "04"
            .AddItem "05"
            .AddItem "06"
            .AddItem "07"
            .AddItem "08"
            .AddItem "09"
            .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"
            .AddItem "31"
        End With
            
        With pumpcolor
            .AddItem "Pink"
            .AddItem "Orange"
            .AddItem "Green"
            .AddItem "Blue"
        End With
    
        pumpcolor.Value = ""
    
        With typeofdefect
            .AddItem "Pie Crust"
            .AddItem "Porocity"
            .AddItem "Wavy"
            .AddItem "Thin"
            .AddItem "Wavy"
            .AddItem "Rope-like"
            .AddItem "Fall in / Holes"
            .AddItem "Start-Up"
            .AddItem "Rope-like"
            .AddItem "Gap"
            .AddItem "Visable Wire"
            .AddItem "Low Tac"
            .AddItem "Dropped / Damage"
            .AddItem "Other / Unknown"
        End With
    
        typeofdefect.Value = ""
    'Initialize Option Buttons
    
        mass1 = True
    
        tacknone = True
    
        Yellow0 = True
        Violet0 = True
        Blue0 = True
        Orange0 = True
    
    ' Put the cursor on Cover Unit Text Box
    
        coverunit.SetFocus

End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
I should also add that after it says the error it doubles everything in my comboboxes. EX. 1,2,3,4 becomes 1,2,3,4,1,2,3,4

Also I turned off all of the MatchRequired and it does not give me the error anymore. I would like this on. Can anyone give any insight. Thanks.
 
Upvote 0
dualhcsniatpac said:
OK. I have a form with 5 different comboboxes. I have done some research on this error and it seems to pop up when the combobox is returning a value that is not in its list. I saw something else about MatchRequired affecting it which i do have set to TRUE for all cb's. Also all of my data gets transferred to the data file as it should so there are no problems there. Its just that this pesky error pops up.

Here is the code.

Code: 
Private Sub submit_Click()
    SheetName = Format(Date, "mmmm")
    ' Open the file
        
        Call CreateOpenFile
        
        Sheets(SheetName).Select
        
        Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
        
        ActiveCell.Value = Now()
        ActiveCell.Offset(0, 1) = todaysdate.Value
        ActiveCell.Offset(0, 2) = coverunit.Value
        ActiveCell.Offset(0, 3).NumberFormat = "@"
        ActiveCell.Offset(0, 3).Value = Format(coverdate1.Value, "00") & "/" & Format(Coverdate2.Value, "00")
        'ActiveCell.Offset(0, 3) = coverdate1.Value & "/" & Coverdate2.Value
        'ActiveCell.Offset(0, 3).NumberFormat = "mm/dd"
        ActiveCell.Offset(0, 4) = covertime.Value
        ActiveCell.Offset(0, 5) = juliandate.Value
        ActiveCell.Offset(0, 6) = juliantime.Value
        
        If tacknone.Value = True Then
            ActiveCell.Offset(0, 7).Value = 1
        Else
            ActiveCell.Offset(0, 7).Value = 0
        End If
        
        If tack1.Value = True Then
            ActiveCell.Offset(0, 8).Value = 1
        Else
            ActiveCell.Offset(0, 8).Value = 0
        End If
        
        If tack2.Value = True Then
            ActiveCell.Offset(0, 9).Value = 1
        Else
            ActiveCell.Offset(0, 9).Value = 0
        End If
        
        If tack3.Value = True Then
            ActiveCell.Offset(0, 10).Value = 1
        Else
            ActiveCell.Offset(0, 10).Value = 0
        End If
        
        If tack4.Value = True Then
            ActiveCell.Offset(0, 11).Value = 1
        Else
            ActiveCell.Offset(0, 11).Value = 0
        End If
        
        ' EB Welder outputs
        ''''''''''''''
        If Yellow0.Value = True Then
            ActiveCell.Offset(0, 12).Value = 0
        End If
        If Yellow1.Value = True Then
            ActiveCell.Offset(0, 12).Value = 1
        End If
        If Yellow2.Value = True Then
            ActiveCell.Offset(0, 12).Value = 2
        End If
        If Yellow3.Value = True Then
            ActiveCell.Offset(0, 12).Value = 3
        End If
        ''''''''''''''
        If Blue0.Value = True Then
            ActiveCell.Offset(0, 13).Value = 0
        End If
        If Blue1.Value = True Then
            ActiveCell.Offset(0, 13).Value = 1
        End If
        If Blue2.Value = True Then
            ActiveCell.Offset(0, 13).Value = 2
        End If
        If Blue3.Value = True Then
            ActiveCell.Offset(0, 13).Value = 3
        End If
        ''''''''''''''
        If Orange0.Value = True Then
            ActiveCell.Offset(0, 14).Value = 0
        End If
        If Orange1.Value = True Then
            ActiveCell.Offset(0, 14).Value = 1
        End If
        If Orange2.Value = True Then
            ActiveCell.Offset(0, 14).Value = 2
        End If
        If Orange3.Value = True Then
            ActiveCell.Offset(0, 14).Value = 3
        End If
        '''''''''''''
        If Violet0.Value = True Then
            ActiveCell.Offset(0, 15).Value = 0
        End If
        If Violet1.Value = True Then
            ActiveCell.Offset(0, 15).Value = 1
        End If
        If Violet2.Value = True Then
            ActiveCell.Offset(0, 15).Value = 2
        End If
        If Violet3.Value = True Then
            ActiveCell.Offset(0, 15).Value = 3
        End If
        If mass1.Value = True Then
            ActiveCell.Offset(0, 17).Value = 1
        End If
        If mass2.Value = True Then
            ActiveCell.Offset(0, 17).Value = 2
        End If
        If mass3.Value = True Then
            ActiveCell.Offset(0, 17).Value = 3
        End If
        If mass5.Value = True Then
            ActiveCell.Offset(0, 17).Value = 5
        End If
       
        ActiveCell.Offset(0, 16) = pumpcolor.Value
        ActiveCell.Offset(0, 18) = typeofdefect.Value
        
    ActiveWorkbook.Close True
            
    Call UserForm_Initialize
        
End Sub
 

Private Sub UserForm_Initialize()
    
    'Initialize Text Boxes Here
        coverunit.Value = ""
        coverdate1.Value = ""    
        Coverdate2.Value = ""
        covertime.Value = ""
        
        juliandate.Value = ""
        juliantime.Value = ""
        
        comments.Value = ""
        
        todaysdate.Value = Format(Now, "mm/dd/yyyy")
    
    ' Initialize drop down menus
        
        With coverunit
            .AddItem "1"
            .AddItem "2"
            .AddItem "3"
            .AddItem "4"
        End With
        
        With coverdate1
            .AddItem "01"
            .AddItem "02"
            .AddItem "03"
            .AddItem "04"
            .AddItem "05"
            .AddItem "06"
            .AddItem "07"
            .AddItem "08"
            .AddItem "09"
            .AddItem "10"
            .AddItem "11"
            .AddItem "12"
        End With
        
        With Coverdate2
            .AddItem "01"
            .AddItem "02"
            .AddItem "03"
            .AddItem "04"
            .AddItem "05"
            .AddItem "06"
            .AddItem "07"
            .AddItem "08"
            .AddItem "09"
            .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"
            .AddItem "31"
        End With
            
        With pumpcolor
            .AddItem "Pink"
            .AddItem "Orange"
            .AddItem "Green"
            .AddItem "Blue"
        End With
    
        pumpcolor.Value = ""
    
        With typeofdefect
            .AddItem "Pie Crust"
            .AddItem "Porocity"
            .AddItem "Wavy"
            .AddItem "Thin"
            .AddItem "Wavy"
            .AddItem "Rope-like"
            .AddItem "Fall in / Holes"
            .AddItem "Start-Up"
            .AddItem "Rope-like"
            .AddItem "Gap"
            .AddItem "Visable Wire"
            .AddItem "Low Tac"
            .AddItem "Dropped / Damage"
            .AddItem "Other / Unknown"
        End With
    
        typeofdefect.Value = ""
    'Initialize Option Buttons
    
        mass1 = True
    
        tacknone = True
    
        Yellow0 = True
        Violet0 = True
        Blue0 = True
        Orange0 = True
    
    ' Put the cursor on Cover Unit Text Box
    
        coverunit.SetFocus

End Sub
Click to expand...
I've had a similar problem except that I was setting the parameters at Design time in Properties. It drove me mad, but we've discovered a workaround. In my situation, I had a form with a combo box. Row source property is set to named range, generally with a blank row in the first cell in the range. With Match required set to true, if the user selects an item from the drop down, no problem, but if he/she deletes the selected item and clicks off the combo, the Invalid Property value error occurs. If you set the ListStyle property to 2-frmstyledropdownlist, no functionality is lost and you cannot delete the value previously selected. If you want the option of "oops i didn't want to select anything in that box, set the first cell in the named range to a blank. I'm pretty sure you can translate this into code, if your need is to populate the combo programmatically.

Thanks to Andy for that one.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
124
Anthony47
A
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
133
Peter_SSs
Peter_SSs
E
  • Question
Search function not finding rows containing the matching value
Replies
1
Views
524
GlennUK
G
I
  • Solved
Populate listbox but omit N/A value
Replies
2
Views
342
ipbr21054
I
brendalpzm
  • Solved
Filter a list based on a text box value
Replies
7
Views
376
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,641
Messages
6,120,694
Members
448,979
Latest member
DET4492

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