Announcement

Collapse
No announcement yet.

Invalid use of property - userform

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Invalid use of property - userform

    I'm using the following code to place data from the userform into cells on a worksheet.
    Code:
    Private Sub CalcButton_Click()
    
    
    'define empty row
    emptyRow1 = WorksheetFunction.CountA(Range("A:A")) + 1
    
    
    'fill rent information in Total Rent Spreadsheet
        Cells(emptyRow1, 1).Value = MeetingSpaceBox.Value
        Cells(emptyRow1, 2).Value = CCDaysText.Value
        Cells(emptyRow1, 3).Value = MIDaysTxt.Value
        Cells(emptyRow1, 4).Value = YearListBox.Value
        Cells(emptyRow1, 5).Value = (DiscountTxt.Value) / 100
    
    
    Call UserForm_Initialize
    
    
    End Sub
    This works fine. However, when I try and add validation before the "define empty row" comment, in the form of an "if statement", I get a compile error: invalid use of property. I'm curious as to why this is happening. I've tried numerous codes (found online and adapted to my needs) and always get the same compile error. Thanks for the help.
    Attached Files

  • #2
    Re: Invalid use of property - userform

    What kind of validation do you want?

    What is the code that won't compile?

    BTW, you might want to add a line to the Initialize event.
    If the box isn't cleared, the list gets longer every time you the Clear button is pressed.

    Code:
    Private Sub UserForm_Initialize()
    '...
        'fill Year List Box
        With YearListBox
            .Clear: Rem new line <<<<<<<<<<<<<
            .AddItem "2011/2012"
            .AddItem "2013/2014"
            .AddItem "2015/2016"
            .AddItem "2017/2018"
            .AddItem "2019/2020"
            .AddItem "2021/2022"
        End With
    '...
    End Sub

    Comment


    • #3
      Re: Invalid use of property - userform

      Ideally it would validate that there is at least a value in every box and then that the textboxes only contain whole numbers. I can probably figure out that code myself but I just can't understand why it won't compile. This one I just tried
      Code:
      If MeetingSpaceBox.ListIndex < 0 Then
          MsgBox "Your entry must be part of the list", vbCritical
          Exit Sub
      End If
      I deleted numerous others because they wouldn't work but they involved using the Len function, and also just a simple if statement to check to see if the value of the textbox = "" but it all gives me a compile error.

      Comment


      • #4
        Re: Invalid use of property - userform

        You need to change the name of the other userform.
        Its current name is MsgBox, which conflicts with the VBA function.
        This worked for me after the other userform was renamed.
        Code:
        Private Sub CalcButton_Click()
        
            If AllDataEntered Then
                'define empty row
                emptyRow1 = WorksheetFunction.CountA(Range("A:A")) + 1
                
                'fill rent information in Total Rent Spreadsheet
                    Cells(emptyRow1, 1).Value = MeetingSpaceBox.Value
                    Cells(emptyRow1, 2).Value = CCDaysText.Value
                    Cells(emptyRow1, 3).Value = MIDaysTxt.Value
                    Cells(emptyRow1, 4).Value = YearListBox.Value
                    Cells(emptyRow1, 5).Value = Val(DiscountTxt.Value) / 100
                
                Call UserForm_Initialize
            Else
                MsgBox "Enter All Fields"
            End If
        End Sub
        
        Function AllDataEntered() As Boolean
                AllDataEntered = (MeetingSpaceBox.ListIndex <> -1)
                AllDataEntered = AllDataEntered And (Me.YearListBox.ListIndex <> -1)
                AllDataEntered = AllDataEntered And (Me.CCDaysText.Text <> vbNullString)
                AllDataEntered = AllDataEntered And (Me.MIDaysTxt.Text <> vbNullString)
                AllDataEntered = AllDataEntered And (Me.DiscountTxt.Text <> vbNullString)
        End Function
        Adding code like this restricted entry into the text boxes to non-negative integers.

        Code:
        Private Sub CCDaysText_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
            KeyCode = -KeyCode * CInt(Chr(KeyCode) Like "[0-9]")
        End Sub
        Private Sub MIDaysTxt_Change()
            KeyCode = -KeyCode * CInt(Chr(KeyCode) Like "[0-9]")
        End Sub
        Private Sub DiscountTxt_Change()
            KeyCode = -KeyCode * CInt(Chr(KeyCode) Like "[0-9]")
        End Sub
        Attached Files

        Comment


        • #5
          Re: Invalid use of property - userform

          Wow. Duh. I will file that one under obvious fixes. Unique name for everything. Thanks for the help!

          Comment

          Working...
          X