create combobox & set listfillrange within same macro

[erik.van.geit]

Hi, gurus,

strange problem
this is working fine

Sub create_combobox()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=ActiveCell.Left, Top:=ActiveCell.Top, _
        Width:=ActiveCell.Width, Height:=ActiveCell.Height).Name = "test"
Application.OnTime Now + 0.01 / 60 / 60 / 24, "fillit"
End Sub

Sub fillit()
ActiveSheet.test.List() = Array("date1", "date2", "date3")
End Sub

but this not

Sub create_combobox()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=ActiveCell.Left, Top:=ActiveCell.Top, _
        Width:=ActiveCell.Width, Height:=ActiveCell.Height).Name = "test"

ActiveSheet.test.List() = Array("date1", "date2", "date3")
End Sub

error 438 "property or method not supported by this object

why ???

to tell this in words:
creating a combobox works fine with this code
setting its listfillrange:
1. afterwards (using a macro even called 0.01 second later) = working fine
2. within the same macro it doesn't work

kind regards,
Erik

 

[erik.van.geit]

Thank you for this "warm" thread, guys!!

Norie,
I know it's not the listfillrange which is used, but I prefered to put it that way in the title.

Tom,
thank you for this simple explanation
perhaps in a few days I will grasp the "deeper" sense of your responses

this will be about my code

Option Explicit

Sub create_combobox2()
Dim ctrl As OLEObject

With ActiveCell
Set ctrl = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
End With

Dim r As Range, dic As Object, x
Set dic = CreateObject("Scripting.Dictionary")
With ActiveSheet
For Each r In .Range("data")
    If Not IsEmpty(r) And Not dic.exists(r.Value) Then
        dic.Add r.Value, Nothing
    End If
Next
End With
x = dic.keys
With ctrl
.Object.List = x
.Name = "test"
End With

End Sub

Sub delete_it()
ActiveSheet.Shapes("test").Delete
End Sub

Trying to put Public ctrl as OleObject on top of the module
and use
ctrl.Delete in the macor delete_it
didn't succeed ...
(nor did ctrl.Cut)
is this again some kind of VBA-rule ?

kind regards,
Erik

 

[gwkenny]

Thank you for taking the time to reply

Thanks for taking the time to reply.

Unfortunately I do find it disheartening when a knowledgable professional takes the line of excusing compile errors as "VBA rule(s)".

I did read your previous arguments and it doesn't logically exlain why one set of code will work and the other doesn't when there is NO change in sequence or syntax.

You stated, "Whatever the case, the point is that OLE's do not respond well when they are programmatically created and / or when their Name property is programmatically changed".

That's just buggy. "Does not respond well"? Doesn't that sound just a bit buggy to you?

I appreciate your explanation of YOUR code. I understand that. BUT that doesn't explain why the OP's code doesn't work in one instance, but broken up into 2 instances, does work.

I'm not ARGUING why the OP's first code doesn't work. I'm arguing that if it doesn't work in his original code, it shouldn't work when you break the code up into two separate pieces.

The fact that it does is not logical.

You can these bugs as "VBA rules", but that doesn't change the fact that Microsoft compiler is giving illogical results which is the definition of a program bug: Anything that gives an illogical result.

***************

Having re-read your post again, maybe it's the definition of what is a "bug" is where we truly differ.

"a true bug when no run time error occurs and hence no detection mechanism is in place, but a wrong result is yielded, which is not the case here."

That's way too narrow. My profs at CMU wouldn't agree with that nor folks who I've worked with in the past including Microsoft.

What do you call something that gives a run time error but is supposed to give a valid result? Cause that's exactly the case we have here. A rule?

 

[erik.van.geit]

gwkenny,

I appreciate your posts because it opened an intresting discussion
but if you differ on the definition of "bug" you will not get on the same wave-length
perhaps you can continue to PM with each other ?

my goal is to help someone else with his project: I would like to get reponse on my additional question (see my previous post)
so it would be fine to finish it with the adequate background-knowledge

best regards,
Erik

 

[Norie]

Erik

Sorry for saying that but I thought the title of your thread was a bit misleading.

 

[gwkenny]

Eric

Eric:

1) I agree wholeheartedly with Norie. Your title is very misleading because LISTFILLRANGE is in the Excel object model.

Phrasing and word choice is important because if you can't communicate accurately we won't necessarily understand what you mean (and I'll get back to that shortly).

2) I don't believe Tom will be returning to this thread. He stated that fact and I think we've got to respect that. It's admirable for someone to stand by their word these days! If you want another answer than the one I'm going to give you, you may want to start a new topic. Plus, football is more important. Where I live, I have to watch it from midnight to 12 noon! So Monday's I'm a little crazier than normal.

3) User's need to pressure Microsoft to improve their products.

No matter if I differ in the definition of a bug with Tom, the problem you originally cited is NOT a feature. Microsoft's code is so bloated that it's a pain in the *** to nail things down. If the community User base doesn't make a huge stink (or you happen to work for an investment bank where Microsoft will send it's own developers to break their own code for you and respond to your problems) they tend not to do diddly except work on the next version. Problem is that the next version won't necessarily fix existing problems unless there is a hue and cry.

Because there is an existing work around for something is no reason for them not to tighten their code.

4) Going back to your second question. I believe I understand. The code that you posted works fine? Correct?

What you are trying to do is make "ctrl" a public module level variable by placing it at the top of your module using "Dim ctrl As OLEObject".

So you first run your "create_combobox2" procedure. That executes and returns control back to you. Then you run the "delete_it" procedure but using ctrl.delete, or ctrl.cut. But you get an error because "ctrl" is an undefined object?

Using Tom's words, "it's simply Forms objects behaving as Forms objects".

I know standard string and number variables as well as shapes and other objects that are defined globally are retained. But I guess Form objects are not and get flushed when execution stops. Personally I've never run into this problem because I avoid global variables like the plague. But it's all on how you approach coding.

This is a bit easier for me to accept if all Form controls behave like this.

If I had to hazard a guess, it would be because a combo box (as well as all[?] form controls) is not an inherent part of Excel. It's a part of an object model that is available to all apps which Excel was programmed to comply with. Though in a way, I don't see where that should make that much a difference.

But maybe the computer would get confused if you were able to globally declare a combobox in Excel VBA and globally declare a combobox in Word VBA at the same time with the same name.

5) Lastly, you can just address me as g-. All the gwkenny stuff sounds really formal.



_________________
Helping the people you come into contact with and being a better person every day is more important than living your life according to love of a fictional character

 

[erik.van.geit]

gwkenny

I agree wholeheartedly with Norie. Your title is very misleading because LISTFILLRANGE is in the Excel object model.

Phrasing and word choice is important because if you can't communicate accurately we won't necessarily understand what you mean

Toms response was right on spot and helped me out. Perhaps sometimes Tom is a bit "straight-forward" but he is the one who really helped instead of telling me how I should behave.
It didn't really matter in any sense if you try to set Listfillrange or just the list. The problem is to do something with an object after it has just been created. Using Listfillrange triggers just the same error-result as List does.

Thanks for helping, Tom
just as you I'm leaving this one

Erik

 

[gwkenny]

It didn't really matter in any sense if you try to set Listfillrange or just the list. The problem is to do something with an object after it has just been created. Using Listfillrange triggers just the same error-result as List does.

My point of, "Phrasing and word choice is important because if you can't communicate accurately we won't necessarily understand what you mean" continues to hold. The fact that using Listfillrange triggers the same error isn't the point. The point is that it is confusing for others who are trying to help you.

As for telling you how to behave, I'm the last person who should be doing that, but I will argue adamantly for viewpoints I feel strongly about and then it's up to others to make up their own minds.
_________________
Helping the people you come into contact with and being a better person every day is more important than living your life according to love of a fictional character