Run-time error '380' Could not set the Value property. Invalid property value

A

aabrazil1

New Member
Joined
Feb 11, 2024
Messages
19
Office Version
  1. 2021
Platform
  1. Windows
Hello All,

Everything is working well except the Edit macro. I am writing the macros per this youtube instructions and substituting with my fields.



1707748034843.png



This is the error message I get when the Edit Macro is ran:

1707748236662.png


Here is the Edit Macro.


VBA Code: 
Private Sub cmdEdit_Click()


  If Selected_List = 0 Then
    
    MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
    
    Exit Sub
    
    End If
    
    'Code to update the value to respective controls
    
    Dim sPR As String
    
    Me.txtRowNumber.Value = Selected_List + 1
    
    Me.txtnumber.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 1)
    
    Me.Txttitle.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 2)
    
    Me.lbsystem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 3)
    
    sPR = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 4)
    
    If sPR = "Y" Then
        Me.optyes.Value = True
        
    Else
        Me.optno.Value = True
        
    End If
       
    Me.lbdefectcode.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 5)
    
    Me.lbexpected.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 6)

    Me.lbactual.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 7)
        
    Me.txtproblem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 8)
     
    Me.txtnotes.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 9)
    
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
    

End Sub

Here is all the form's code if this is helpful:

VBA Code: 
Option Explicit


Private Sub cmdDelete_Click()

  If Selected_List = 0 Then
    
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        
        Exit Sub
        
    End If
    
    Dim i As VbMsgBoxResult
    
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "Confirmation")
        
    If i = vbNo Then Exit Sub
    
    ThisWorkbook.Sheets("Database").Rows(Selected_List + 1).Delete
    
    Call Reset
    
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"


End Sub

Private Sub cmdEdit_Click()


  If Selected_List = 0 Then
    
    MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
    
    Exit Sub
    
    End If
    
    'Code to update the value to respective controls
    
    Dim sPR As String
    
    Me.txtRowNumber.Value = Selected_List + 1
    
    Me.txtnumber.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 1)
    
    Me.Txttitle.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 2)
    
    Me.lbsystem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 3)
    
    sPR = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 4)
    
    If sPR = "Y" Then
        Me.optyes.Value = True
        
    Else
        Me.optno.Value = True
        
    End If
       
    Me.lbdefectcode.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 5)
    
    Me.lbexpected.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 6)

    Me.lbactual.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 7)
        
    Me.txtproblem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 8)
     
    Me.txtnotes.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 9)
    
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
    

End Sub

Private Sub cmdreset_Click()
    
    Dim msgValue As VbMsgBoxResult

   msgValue = MsgBox("Do you want to rerest the form?", vbYesNo + vbInformation, "Confirmation")
   
   If msgValue = vbNo Then Exit Sub
    
    Call Reset

End Sub

Private Sub cmdsave_Click()

   Dim msgValue As VbMsgBoxResult

   msgValue = MsgBox("Please check you entries and confirm you want to save the data", vbYesNo + vbInformation, "Confirmation")
   
   If msgValue = vbNo Then Exit Sub
   
   
    Call Submit
    
    Call Reset
    

End Sub


Private Sub Frame1_Click()

End Sub

Private Sub UserForm_Initialize()

    Call Reset

End Sub

Here is the Module code:



VBA Code: 
Option Explicit

Sub Reset()
    
    Dim iRow As Long
    
    iRow = [Counta(Database!A:A)] 'identifying the last row'
    
    With frmform1
    
        
        .txtnumber.Value = " "
        .Txttitle.Value = " "
        
        .lbsystem.Clear
        
          .lbsystem.AddItem "ACS"
        .lbsystem.AddItem "Archive Data"
         .lbsystem.AddItem "ATDS"
        .lbsystem.AddItem "Autocapture Testbed"
         .lbsystem.AddItem "AVN"
        .lbsystem.AddItem "C&DH"
         .lbsystem.AddItem "CBS"
        .lbsystem.AddItem "COMLIDAR"
         .lbsystem.AddItem "COMM"
        .lbsystem.AddItem "COMSEC"
         .lbsystem.AddItem "EGSE"
        .lbsystem.AddItem "EGSE (Flight I&T)"
         .lbsystem.AddItem "EPS"
        .lbsystem.AddItem "FlatSat"
         .lbsystem.AddItem "FSW"
        .lbsystem.AddItem "HFCS"
         .lbsystem.AddItem "L7 Mockups (Flight I&T)"
        .lbsystem.AddItem "Landsat 7"
         .lbsystem.AddItem "LIDAR"
        .lbsystem.AddItem "MECH"
         .lbsystem.AddItem "MGSE (Flight I&T)"
        .lbsystem.AddItem "PCC"
         .lbsystem.AddItem "PROP"
        .lbsystem.AddItem "PSU"
         .lbsystem.AddItem "PTS"
        .lbsystem.AddItem "RDT"
         .lbsystem.AddItem "REU"
        .lbsystem.AddItem "ROBOT"
         .lbsystem.AddItem "RPO"
        .lbsystem.AddItem "RPO Testbed"
         .lbsystem.AddItem "SC"
        .lbsystem.AddItem "SCTHRM"
         .lbsystem.AddItem "Servicing Payload (PYLD)"
        .lbsystem.AddItem "Serviving Testbed"
         .lbsystem.AddItem "Simulators"
        .lbsystem.AddItem "SP/SV/SC SPIDER GSE"
        .lbsystem.AddItem "Spave Vehicle Management"
        .lbsystem.AddItem "SPIDER"
         .lbsystem.AddItem "SPINT"
        .lbsystem.AddItem "STR"
         .lbsystem.AddItem "SVINT"
        .lbsystem.AddItem "Testbeds"
         .lbsystem.AddItem "THRM"
        .lbsystem.AddItem "TOOL"
        .lbsystem.AddItem "VDSU"
        .lbsystem.AddItem "VSS"


        
        .optyes.Value = False
        .optno.Value = False
        .lbdefectcode.Clear
        
            .lbdefectcode.AddItem "10 - Solder Defect"
        .lbdefectcode.AddItem "20 - Contamination"
          .lbdefectcode.AddItem "30 - Shrink Tubing Missing"
        .lbdefectcode.AddItem "40 - Not Built to Specification/Drawing"
          .lbdefectcode.AddItem "50 - Dimensions Out of Tolerance"
        .lbdefectcode.AddItem "60 - Failed Test"
          .lbdefectcode.AddItem "70 - Accept"
        .lbdefectcode.AddItem "80 - Damaged"
          .lbdefectcode.AddItem "90 - Documentation Error"

        
        .lbexpected.Clear
        
          .lbexpected.AddItem ".5"
        .lbexpected.AddItem "1"
        .lbexpected.AddItem "1.5"
        .lbexpected.AddItem "2"
           .lbexpected.AddItem "2.5"
        .lbexpected.AddItem "3"
        .lbexpected.AddItem "3.5"
        .lbexpected.AddItem "4"
           .lbexpected.AddItem "4.5"
        .lbexpected.AddItem "5"
        .lbexpected.AddItem "5.5"
        .lbexpected.AddItem "6"
           .lbexpected.AddItem "6.5"
        .lbexpected.AddItem "7"
        .lbexpected.AddItem "7.5"
        .lbexpected.AddItem "8"
        .lbexpected.AddItem "8.5"
        .lbexpected.AddItem "9"
        .lbexpected.AddItem "9.5"
        .lbexpected.AddItem "10"
        .lbexpected.AddItem "10.5"
        .lbexpected.AddItem "11"
        .lbexpected.AddItem "11.5"
        .lbexpected.AddItem "12"

        
        .lbactual.Clear
        
         .lbactual.AddItem ".5"
        .lbactual.AddItem "1"
        .lbactual.AddItem "1.5"
        .lbactual.AddItem "2"
           .lbactual.AddItem "2.5"
        .lbactual.AddItem "3"
        .lbactual.AddItem "3.5"
        .lbactual.AddItem "4"
           .lbactual.AddItem "4.5"
        .lbactual.AddItem "5"
        .lbactual.AddItem "5.5"
        .lbactual.AddItem "6"
           .lbactual.AddItem "6.5"
        .lbactual.AddItem "7"
        .lbactual.AddItem "7.5"
        .lbactual.AddItem "8"
        .lbactual.AddItem "8.5"
        .lbactual.AddItem "9"
        .lbactual.AddItem "9.5"
        .lbactual.AddItem "10"
        .lbactual.AddItem "10.5"
        .lbactual.AddItem "11"
        .lbactual.AddItem "11.5"
        .lbactual.AddItem "12"

        
        .txtproblem.Value = " "
        .txtnotes.Value = " "
        
        .txtRowNumber.Value = " "
        
        .lbdatabase.ColumnCount = 12
        .lbdatabase.ColumnHeads = True
        
        .lbdatabase.ColumnWidths = "40,40,40,40,20,20,40,40,40,40,40,40"
        
        If iRow > 1 Then
        
            .lbdatabase.RowSource = "Database!A2:L" & iRow
            
        Else
            .lbdatabase.RowSource = "Database!A2:L2"
        End If
        
    End With

End Sub


Sub Submit()
    Dim sh As Worksheet
    Dim iRow As Long
    
    Set sh = ThisWorkbook.Sheets("Database")
    
    If frmform1.txtRowNumber.Value = " " Then
    
    iRow = [Counta(Database!A:A)] + 1
        
    Else
        
    iRow = frmform1.txtRowNumber.Value

    End If
    
    With sh
    
        .Cells(iRow, 1) = iRow - 1
        
        .Cells(iRow, 2) = frmform1.txtnumber.Value
        
        .Cells(iRow, 3) = frmform1.Txttitle.Value
        
        .Cells(iRow, 4) = frmform1.lbsystem.Value
        
        .Cells(iRow, 5) = IIf(frmform1.optyes.Value = True, "Y", "N")
        
        .Cells(iRow, 6) = frmform1.lbdefectcode.Value
        
        .Cells(iRow, 7) = frmform1.lbexpected.Value
        
        .Cells(iRow, 8) = frmform1.lbactual.Value
        
        .Cells(iRow, 9) = frmform1.txtproblem.Value
        
        .Cells(iRow, 10) = frmform1.txtnotes.Value
        
        .Cells(iRow, 11) = Application.UserName
         
        .Cells(iRow, 12) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
        
        
        
      End With
      

End Sub


Sub Show_Form()

    frmform1.Show
    
End Sub

Function Selected_List() As Long

    Dim i As Long
    
    Selected_List = 0
    
    For i = 0 To frmform1.lbdatabase.ListCount - 1
    
        If frmform1.lbdatabase.Selected(i) = True Then
            
            Selected_List = i + 1
            
            Exit For
            
        End If
                      
    Next i

End Function

Thank you very much,
Tony
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Which line is actually causing the error?

BTW, it's a little weird to my mind to use listboxes squashed down to one visible row - a combobox would make more sense.
 
Upvote 0
Step through your code and watch what happens. Check the values of variables as you go (the Watch window is useful for this - see View menu in editor). You might be trying to set a value somewhere that isn't valid for that cell/control according to its format type. I say somewhere because you don't reveal which line triggers the error which always good to know. I see that at one point you attempt to set a single space as a value but I wonder if your intent was to set it to an empty string ("" not " ").

Can I ask why the double and triple line spacing everywhere? It only serves to make code 2 or 3 times taller than it needs to be. Asking because I'm noticing it a lot more lately and wonder if the forum software is injecting blank lines.
 
Upvote 0
RoryA said:
Which line is actually causing the error?

BTW, it's a little weird to my mind to use listboxes squashed down to one visible row - a combobox would make more sense.
Click to expand...

Thank you Rory, I added comment below comment: 'The error is here. I will try to witch to comboboxs. Thank you for the suggestion.

VBA Code: 
Private Sub cmdEdit_Click()
  If Selected_List = 0 Then    
    MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"    
    Exit Sub    
    End If    
    'Code to update the value to respective controls 
    Dim sPR As String   
    Me.txtRowNumber.Value = Selected_List + 1  
    Me.txtnumber.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 1)
    Me.Txttitle.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 2)   
    Me.lbsystem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 3)     'THE ERROR IS ALSO HERE
    sPR = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 4)
    If sPR = "Y" Then
        Me.optyes.Value = True   
    Else
        Me.optno.Value = True        
    End If    
    Me.lbdefectcode.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 5) 
    Me.lbexpected.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 6)    'THE ERROR IS HERE
    Me.lbactual.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 7)    
    Me.txtproblem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 8)    
    Me.txtnotes.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 9)   
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"   
End Sub
 
Upvote 0
Micron said:
Step through your code and watch what happens. Check the values of variables as you go (the Watch window is useful for this - see View menu in editor). You might be trying to set a value somewhere that isn't valid for that cell/control according to its format type. I say somewhere because you don't reveal which line triggers the error which always good to know. I see that at one point you attempt to set a single space as a value but I wonder if your intent was to set it to an empty string ("" not " ").

Can I ask why the double and triple line spacing everywhere? It only serves to make code 2 or 3 times taller than it needs to be. Asking because I'm noticing it a lot more lately and wonder if the forum software is injecting blank lines.
Click to expand...

Sorry for the spacing. The guy in the Youtube video did it so I copied the spacing. I agree it makes it too long. I think you can see my reply to Rory above but if not here is where the error is happening:

VBA Code: 
Private Sub cmdEdit_Click()
  If Selected_List = 0 Then    
    MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"    
    Exit Sub    
    End If    
    'Code to update the value to respective controls 
    Dim sPR As String   
    Me.txtRowNumber.Value = Selected_List + 1  
    Me.txtnumber.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 1)
    Me.Txttitle.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 2)   
    Me.lbsystem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 3)     'THE ERROR IS ALSO HERE
    sPR = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 4)
    If sPR = "Y" Then
        Me.optyes.Value = True   
    Else
        Me.optno.Value = True        
    End If    
    Me.lbdefectcode.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 5) 
    Me.lbexpected.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 6)    'THE ERROR IS HERE
    Me.lbactual.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 7)    
    Me.txtproblem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 8)    
    Me.txtnotes.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 9)   
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"   
End Sub


Thank you,
Tony
 
Upvote 0
I suspect the issue is that the values don't actually match anything in the list. For example, you added ".5" to one list, but the other contains "0.5" which is not the same. I'd suspect the system value also doesn't quite match - eg a leading/trailing space on the worksheet data.
 
Upvote 0
aabrazil1 said:
Thank you Rory, I added comment below comment: 'The error is here. I will try to witch to comboboxs. Thank you for the suggestion.

VBA Code: 
Private Sub cmdEdit_Click()
  If Selected_List = 0 Then   
    MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"   
    Exit Sub   
    End If   
    'Code to update the value to respective controls
    Dim sPR As String  
    Me.txtRowNumber.Value = Selected_List + 1 
    Me.txtnumber.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 1)
    Me.Txttitle.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 2)  
    Me.lbsystem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 3)     'THE ERROR IS ALSO HERE
    sPR = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 4)
    If sPR = "Y" Then
        Me.optyes.Value = True  
    Else
        Me.optno.Value = True       
    End If   
    Me.lbdefectcode.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 5)
    Me.lbexpected.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 6)    'THE ERROR IS HERE
    Me.lbactual.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 7)   
    Me.txtproblem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 8)   
    Me.txtnotes.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 9)  
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"  
End Sub
Click to expand...


aabrazil1 said:
Thank you Rory, I added comment below comment: 'The error is here. I will try to witch to comboboxs. Thank you for the suggestion.

VBA Code: 
Private Sub cmdEdit_Click()
  If Selected_List = 0 Then   
    MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"   
    Exit Sub   
    End If   
    'Code to update the value to respective controls
    Dim sPR As String  
    Me.txtRowNumber.Value = Selected_List + 1 
    Me.txtnumber.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 1)
    Me.Txttitle.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 2)  
    Me.lbsystem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 3)     'THE ERROR IS ALSO HERE
    sPR = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 4)
    If sPR = "Y" Then
        Me.optyes.Value = True  
    Else
        Me.optno.Value = True       
    End If   
    Me.lbdefectcode.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 5)
    Me.lbexpected.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 6)    'THE ERROR IS HERE
    Me.lbactual.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 7)   
    Me.txtproblem.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 8)   
    Me.txtnotes.Value = Me.lbdatabase.List(Me.lbdatabase.ListIndex, 9)  
    MsgBox "Please make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"  
End Sub
Click to expand...

Thank you Rory. It is solved. I changed from listbox to combobox and now it works. Thank you very much
 
Upvote 0
RoryA said:
I suspect the issue is that the values don't actually match anything in the list. For example, you added ".5" to one list, but the other contains "0.5" which is not the same. I'd suspect the system value also doesn't quite match - eg a leading/trailing space on the worksheet data.
Click to expand...

Thank you, The combobox swap solved the issue. Thank you very much
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Autofilter code based on username
Replies
1
Views
173
aabrazil1
A
A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
132
Anthony47
A
A
  • Question
Ambigious Name Detected: Reset
Replies
6
Views
250
aabrazil1
A
TVBurke_18
  • Question
Trying to Edit Form, and the data is adding "6" and moving data around.
Replies
5
Views
290
TVBurke_18
TVBurke_18
J
  • Question
VBA Issue with Searching on a form
2
Replies
12
Views
1K
JayDogUK
J

Forum statistics

Threads
1,215,069
Messages
6,122,951
Members
449,095
Latest member
nmaske

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