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

 

[gwkenny]

Hmmmm

Things to make you go hmmmm

I can only think that's a bug.

 

[Tom Urtis]

Its not a bug at all, it's simply Forms objects behaving as Forms objects, where a certain sequence and syntax is required to program them. No timer required either.

To do all that in one macro:


Sub create_combobox2()
Dim obj As OLEObject
With ActiveCell
Set obj = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
End With
With obj
.Object.List = Array("date1", "date2", "date3")
.Name = "test"
End With
End Sub

 

[gwkenny]

Not a bug?

How can it not be a bug?

If you run Create_Combobox, and then run Fill_combobox, the code executes fine as programmed below.

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" 

End Sub 

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

Combine them together:

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

and it bombs as the OP indicated.

Can you please explain the difference to me between the two code executions and the reasoning it's not a bug?

You cited certain sequence and syntax. The sequence is the same as is the core syntax/grammar.

 

[Tom Urtis]

It's not a bug because it's not a bug, like I said it's VBA being VBA, with certain syntax rules and sequencing required depending on the circumstance.

You wrote:
"The sequence is the same as is the core syntax/grammar."

Not clear what you were comparing there - - if between Erik's two examples, Yes you are correct; if between mine and his, No you are incorrect.

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, while they as objects get their other properties changed with reference to their new object Name property, such as Erik attempted in the same macro. Each step (creation, naming, and property manipulation such as List in this example) will be accepted in separate macros but not together in the same macro depending on Name property.

That is not a bug, it is VBA rules as manifest in countless other examples.

In an unrelated example to help make the general point, this works when executed when Sheet1 is active but not on when any other sheet is active:
Sheets("Sheet1").Range("A1").Select

When you are on another sheet the Select method needs to be chopped up into two statements:
Sheets("Sheet1").Select
Range("A1").Select

Or a different approach (see the pattern here) for dealing with VBA rules to avoid the 1004 error for range selection method to make it happen in one command line:
Application.Goto Sheets("Sheet1").Range("A1"), True 'or False


You are confusing "bug" with VBA rules. Now look at my example, which is purposely not the same as Erik's approach, in order to accomplish the basic task which he wrote in his posted Subject line:
"create combobox & set listfillrange within same macro"

Instead of directly creating and naming the object, when considering in workbook design that the List property is to be defined immediately thereafter, the more reliable approach is to declare a variable ("obj" in my example) and set it to an added object type. Then you can manipulate the object type's properties (Name and List in this case) at the same time in the same macro per the With structure in my post.

Not sure how else to explain it. Erik's first approach that you are claiming is a bug is really not a bug; it's VBA protesting against the attempt to force a square peg in a round hole.

 

[Norie]

Erik

Is that code actually setting the ListFillRange?

As far as I can see what it's doing is populating the combobox using the List property.

 

[gwkenny]

No, it's a bug

No, it's clearly a bug.

That is not a bug, it is VBA rules as manifest in countless other examples.

It's a bug like the countless other examples.

You are taking a Microsoft line. It's a "VBA rule". That's nonsense.

Again, if it is NOT a bug, tell me logically how the compiler would give you an error when you execute the two pieces of code one after another, but when you put them in one subroutine it crashes.

Saying the difference is an "attempt to force a square peg in a round hole" doesn't explain anything.

Honestly, there is NO logical explanation for that to happen. Thus it has to be a bug.

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

What we need to do as professionals who use Microsoft products, is to keep Microsoft honest. Not to let them off the hook by explaining bugs as a "VBA rule". If we accept that, then that's worse then Microsoft's response to me on several occasions, "Yes, we know it's a bug, but we have no plans at the moment to correct it".

We need to be on Microsoft's back so they keep delivering a better product. Right now, using VBA to program across apps is a lot tougher because the same objects in different apps don't behave the same. Now we've come a long way since Microsoft WordBasic and Excel Macro Sheets, but there's still a long way to go to unify the object models of the apps.

Let's call an apple an apple, and a bug a bug.

 

[Norie]

gwkenny

How is it a bug?

 

[gwkenny]

Hi

Hi Norie:

The following code generates an error:

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

If you take out the ActiveSheet line and put it in a second subroutine. Run the first routine, then run the second routine, there is no error:

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" 

End Sub 

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

The bug is that there is no logical explanation for the compiler to generate an error in one situation but not the other as the code is exactly the same except one is broken up into two subroutines.

 

[Tom Urtis]

Re: No, it's a bug

This will be my final post on this thread, too much football on the satellite dish today.

No, it's clearly a bug.

No it clearly is not, but call it whatever you want.

It's a bug like the countless other examples.

No it clearly is not, but call it whatever you want.

You are taking a Microsoft line. It's a "VBA rule". That's nonsense.

Of all the things anyone can say about me, one thing could never be that I am a taker of Microsoft's line...I've had my share of run-ins with them and am no Microsoft sympathizer.

Again, if it is NOT a bug, tell me logically how the compiler would give you an error when you execute the two pieces of code one after another, but when you put them in one subroutine it crashes.

I already did, re-read my post. Other examples of non-bug VBA'isms:

Range("C3") = Range("A3")
will transfer the Value without the Value property specified.

Range("C3:C6") = Range("A3:A6")
will not

Range("C3:C6").Value = Range("A3:A6").Value
will, with the Value property specified.



This line in the Immediate window:
For Each obj in ActiveSheet.Shapesbj.Delete:Next
deletes all shapes on the sheet even though in some cases the features they represent are still enabled, example, Data Validation drop-downs and AutoFilter drop-downs will be cleared although afterwards the cell will still be validated and the sheet will still be in AutoFilter mode. That's VBA among the countless examples, not a bug.

Saying the difference is an "attempt to force a square peg in a round hole" doesn't explain anything.

It explains the exact essence of the topic to reasonable people.

Honestly, there is NO logical explanation for that to happen. Thus it has to be a bug.

Call it whatever you want but really it is not a bug, and I offered what I think is a reasonable logical rebuttal.

What we need to do as professionals who use Microsoft products, is to keep Microsoft honest. Not to let them off the hook by explaining bugs as a "VBA rule". If we accept that, then that's worse then Microsoft's response to me on several occasions, "Yes, we know it's a bug, but we have no plans at the moment to correct it".

We need to be on Microsoft's back so they keep delivering a better product. Right now, using VBA to program across apps is a lot tougher because the same objects in different apps don't behave the same. Now we've come a long way since Microsoft WordBasic and Excel Macro Sheets, but there's still a long way to go to unify the object models of the apps.

Go for it!.

Let's call an apple an apple, and a bug a bug.

The only thing we seem to agree on, except that I would add, let's not call a bug what is not a bug. It's VBA being VBA.


Anyway, none of my quotes and responses are meant for any disrespect. Just because I do not think you are correct does not mean you are not correct. Personally, and what works for me, I draw a distinction between what the expected behavior of Excel and VBA is regardless of how I would have designed the program if given the chance, which is what we have seen here, versus 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.

You have a different way of looking at it, which is fine if it works for you. I appreciate your point of view; thanks for the discussion.