Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Invalid use of property - userform

  1. #1
    Join Date
    14th February 2012
    Posts
    21

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2007
    Posts
    3,688

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th February 2012
    Posts
    21

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2007
    Posts
    3,688

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th February 2012
    Posts
    21

    Re: Invalid use of property - userform

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Show Userform - Invalid Property Value Error
    By irishcheer21 in forum EXCEL HELP
    Replies: 7
    Last Post: December 31st, 2007, 10:20
  2. Invalid Property Value - Lock-up
    By minitman in forum EXCEL HELP
    Replies: 4
    Last Post: May 3rd, 2006, 16:35
  3. invalid property value error
    By jbpyron in forum EXCEL HELP
    Replies: 2
    Last Post: April 14th, 2006, 09:37
  4. Invalid Property Value
    By minitman in forum EXCEL HELP
    Replies: 8
    Last Post: March 30th, 2006, 15:11
  5. Invalid Property Value on Form
    By Ranger in forum EXCEL HELP
    Replies: 4
    Last Post: January 9th, 2004, 11:04

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno