Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Squirrely evaluation of object references (vba excel 2k)

    I get a Type Mismatch error on the second line, but the first line works fine.
    When I hover the mouse over the (UserForm1.CSheet) on the second line,
    it shows that it ="CSheet" which should be the string value that Worksheets wants.

    Clrw% = Worksheets("CSheet").Range("a65536").End(xlUp).Row
    Clrw%= Worksheets(UserForm1.CSheet).Range("a65536").End(x lUp).Row

    Can anyone please give me a clue whats going on?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Squirrely evaluation of object references (vba excel 2k)

    What is CSheet on the UserForm? If it is a TextBox, then use CSheet.Text and then everythig should be OK. VBA tries to read your mind, but sometimes fails, in the latter case, you need to fully qualify the objects.

    BTW, just the teacher in me, but I would prefer to use Option Explicit at the top, and dim'ing Clrw as Long. You'll have less debugging time. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Squirrely evaluation of object references (vba excel 2k)

    What is UserForm1.CSheet? A TextBox? Some other control?

    If it is a TextBox, then try:

    <pre> Clrw%= Worksheets(UserForm1.CSheet.Value).Range("a65536") .End(xlUp).Row
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Feb 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Squirrely evaluation of object references (vba excel 2k)

    Yes, I use option explicit. I show datatype for your info, but all are dim'ed.
    Since there are only 3 worksheets in the listbox, integer works for now, but thanks.

    It is a Textbox created by drawing it in design mode, so it is not dim'ed anywhere.
    It is one column, bound to column 1, with the names of the worksheets it.
    The main procedure puts the names of the sheets into the listbox with additem statements.
    Then it does userform.show.
    When I click on OK, the OKButton routine is trying to pull the sheet name back
    out of the listbox to see which one the user chose (if not just the default).

    Yes, .Text works, as does .Value as does double parens.
    Hovering over each of these 3 formats shows the identical debug value
    as the way that doesn't work.

    Now heres another gotcha.
    Actually there are two listboxes, all done the same way.
    Both have real sheet names in them.
    The next line of code is the same as line2 except it reads the other listbox.

    Ilrw = Worksheets((UserForm1.ISheet).Range("a65536").End( xlUp).Row 'line 3a
    Ilrw = Worksheets(UserForm1.ISheet.Text).Range("a65536"). End(xlUp).Row 'line3b
    Ilrw = Worksheets(UserForm1.ISheet.Value).Range("a65536") .End(xlUp).Row 'line 3c
    Ilrw = Worksheets((UserForm1.ISheet.Text)).Range("a65536" ).End(xlUp).Row 'line 3d

    Now: this one fails in all 4 configurations and the debug value when I hover is "".
    But it works if I do this in two lines of code:

    Isht$ = UserForm1.ISheet.List(UserForm1.ISheet.ListIndex) 'line4
    Ilrw$ = Worksheets(Isht).Range("a65536").End(xlUp).Row 'line5

    Now, I don't really know how to use the object browser, but when I
    show all hiddens, there is an entry for Listbox member of Excel and
    also one for Listbox member of MSforms. Since mine is created by drawing
    and not dim'ing, I don't know how to force it to be MSforms based.

    When I track down the actual Listboxes, they both show
    Member of F3Dynamic.UserForm
    but when I click that link it gives:
    Cannot jump..because library 'F3Dynamic' is not currently referenced.
    and I have no clue how to find it with Tools References,
    or even if it makes any difference since my lines 4 and 5 work fine.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Squirrely evaluation of object references (vba excel 2k)

    Since you created your list boxes on a user form, they are MSForms list boxes. If you type listbox in the search box of the Object Browser, and then select the ListBox class from the MSForms library, you will see the properties, methods and events in the lower right part of the window. By clicking on one of these, you will see information about it in the bottom pane.

    ListBox1.List(ListBox1.ListIndex) is indeed the way to get at the selected item in a single-selection list box. For a multi-select list box, you can use the Selected property to find out which items are selected:

    Dim i As Long
    For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
    Debug.Print ListBox1.List(i)
    End If
    Next i

  6. #6
    New Lounger
    Join Date
    Feb 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Squirrely evaluation of object references (vba excel 2k)

    Thankyou for pointing out the correct way to retrieve the entries from the Listbox.

    Why is it that the other ways work sometimes and not others? (a generic answer is ok).
    My understanding is that VB tries to bridge the gap when something is incompletely
    specified, like when using variants instead of specified data types. But then when it
    gets it right on one line, why wouldn't it get it right on the very next line when trying to
    do the same thing?

    It may be that I misunderstood that BoundColumn=1 was ok for a single column listbox
    even though the data was actually stored in the listbox and not really externally in some cells.
    But this change to =0 did not change the "works for one but not the other" behavior.

    And is there a methodical way to debug such problems, or just exhaustive trial and...?
    (Back in Quickbasic, it was possible to step through just about anything, and even (gag)
    crank up Codeview to step through the assembler level).

    I looked in the Object Browser as you recommended and it looks like a listing
    of the interface definitions or data types for the various methods/properties/events.
    Is the utility of this as a double check on the help file info for these things?

    Regards, George

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Squirrely evaluation of object references (vba excel 2k)

    The Object Browser is very useful because it contains more precise information than the help files, and in some cases you can find out what arguments are needed for methods or events that are not documented in the help files. I use the Dutch version of Excel 97, and the help files are in Dutch. To find out what the named arguments for a method are, I must use the Object Browser.

    Some objects have a default property. When you refer to an object without specifying a property, VBA will silently assume that you mean the default property if there can be no confusion. For instance, ActiveSheet.Range("A1") refers to cell A1 as a range, but in many contexts, you can use this to get the value of cell A1.

    Example1:
    <font face="Georgia">MsgBox ActiveSheet.Range("A1")</font face=georgia>
    will display the value of cell A1.

    Example2:
    <font face="Georgia">Dim rng As Range
    Set rng = ActiveSheet.Range("A1")</font face=georgia>
    will assign cell A1 as a range to the variable rng.

    In some cases, VBA won't use the default property, so referring to the object without specifying a property raises an error. If you specify the property you need in all cases, even if it is the default property, you will avoid this.

Posting Permissions

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