Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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.
    VB:
    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,478

    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.

    VB:
    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
    VB:
    [COLOR=#800000][FONT=Courier]If MeetingSpaceBox.ListIndex < 0 Then[/FONT][/COLOR] 
    [COLOR=#800000][FONT=Courier]    MsgBox "Your entry must be part of the list", vbCritical[/FONT][/COLOR] 
    [COLOR=#800000][FONT=Courier]    Exit Sub[/FONT][/COLOR] 
    [COLOR=#800000][FONT=Courier]End If[/FONT][/COLOR] 
    
    
    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,478

    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.
    VB:
    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.

    VB:
    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