Auto-created combox exit event?

[bdmangum]

I have a form which auto-creates comboboxes based on how much data is in the file. Here's my problem: I need to somehow create an exit event for these auto-created comboboxes. The exit event will be the same for all of them, but I'm not sure how to code the event so it applies to all comboxes. Any ideas?

 

[fumei]

AFAIK each combobox has its own Exit event. In other words, you can certainly write a procedure that performs action TO all comboboxes, but not one that handles actions FROM all comboboxes.

You may have a procedure: Sub AllComboExit() that does whatever you want when a combobox is exited, but you would have to put Call AllComboExit into every Exit event. The Exit event belongs to the control.

Gerry
My paintings and sculpture

 

[bdmangum]

Exactly my problem Gerry. I can't manually create an exit event for each combobox due to there being an infinite number of possible comboboxes.

Here's my goal with the exit event. Maybe someone knows a way to do this without an exit event.


The comboboxes auto-create with autosize set to true. Then a textbox is created using the width of the combobox as it's inital left position. Now if I change what is in the combobox to text that is longer than the original text, part of the combobox is now hidden under the textbox. Does anyone know of a way to reset the textbox's left position based upon the length of the combobox every time the combobox changes?

 

[fumei]

an infinite number of possible comboboxes

An infinite number of comboboxes? Hmmm, that is one heck of a form.

This seems to have some issues of design flaw. I don't like how I am picturing this, but anyway.

To directly answer your last question though...sure. You can use the _Change event of the Combobox.

Sub ComboBox1_Change()
   TextBox1.Left = ComboBox1.Left + ComboBox1.Width + 20
End Sub

This will make the Left property of the textbox = to the Left of the combobox plus its current width plus 20.

Play with that. Or just move the textbox where it won't get messed up by the changing combobox size.

Still, hmmmm, design seems odd.

Gerry
My paintings and sculpture

 

[bdmangum]

Gerry,

Your code is almost exactly what I'm currently using. I was simply hoping for a way not to have to offset the textbox by so much, simply for neatness issues. It looks like I may have to put the neatness aside and just deal with the empty space between the the boxes.

As for infinite comboboxes, I was slightly exaggerating. In reality it could be anywhere from 1 to 200, depending on how much data the user needs to input.

 

[fumei]

I was simply hoping for a way not to have to offset the textbox by so much, simply for neatness issues.

Huh????????

What does "so much" mean???? You asked for a way to

reset the textbox's left position based upon the length of the combobox

This does precisely that. So what the heck is so much? Make the integer (my + 20) smaller. Make it whatever size you want.

You change the combobox to a smaller something (it being AutoSize = True) then the textbox .Left moves again. It should make the distance the same every time.

Sub ComboBox1_Change()
TextBox1.Left = ComboBox1.Left + ComboBox1.Width + 10
End Sub

Sub UserForm_Initialize()
ComboBox1.AddItem "one"
ComboBox1.AddItem "two"
ComboBox1.AddItem "three"
ComboBox1.AddItem "a gazillion things"
ComboBox1.ListIndex = 0
End Sub

You select "one" - the distance between the combobox and textbox is 10. You select "a gazillion things", the textbox is moved to the right, but the distance between it and the combobox is precisely the same.

I have no idea what you are talking about. Maybe you can clarify by saying what exactly you mean by the empty space between the boxes.

Gerry
My paintings and sculpture

 

[bdmangum]

I agree Gerry, that works great, if I can set a change event for each combobox. The problem is that it is impossible to do that when the macro can auto-create up to 200 comboboxes.

The initial position of the textbox relative to the combobox is fine. It's perfectly placed using the same code you have above. The problem is that if the user changes the combobox to some text that is longer than the original text the AutoSize property increases the combobox width enough so that the drop-down arrow is hidden underneath the textbox and thus worthless. So I either need to find a way to shift the textbox upon the combobox change (not using a change event for that particular), or I need to put the drop-down arrow on the left side of the combobox (which I don't think is possible).

I have a new idea I may try. I guess I could use the Form change event to perhaps do what I need. Would that trap a combobox change? I'm spacing out right now though on how to cycle through all the comboboxes in a form, bacuase I would just realign all of them upon a form change. (Not the best for application speed and efficiency, but it would do the job.) I believe it's something to do with the ".Controls." coding, but I can't remember if that can cycle through boxes. Any thoughts?

 

[fumei]

The problem is that if the user changes the combobox to some text that is longer than the original text the AutoSize property increases the combobox width enough so that the drop-down arrow is hidden underneath the textbox and thus worthless.

I don't know what VBA you are using, but that is simply not true with mine.

The "original text" - as it has .ListIndex = 0 - is "one". I change it to "a gazillion things", which is "longer than the original text", and yes, the combobox width increases. And yes, the textbox position moves with it!

I select a longer item, and the textbox is moved EXACTLY the same. Its .Left is the combo width (which includes the dropdown arrow) + X. No matter what item I put in, the textbox is moved approriately, and the dropdown arrow is NEVER, repeat NEVER, hidden.

It is true that if the initial size is less than the item text, the listing is cutoff...but NEVER the dropdown arrow. I have tried with a very very long combobox item text, and (as long as the textbox has room to move) so far...it has NEVER hidden the dropdown arrow. I used:

ComboBox1.AddItem "one"
ComboBox1.AddItem "two"
ComboBox1.AddItem "three"
ComboBox1.AddItem "a gazillion things that happen here"

Nope, selecting "a gazillion...." moves the textbox over, and does NOT hide the dropdown.

In fact, I have been trying and I can not get the dropdown arrow to be hidden under the textbox with that code.

To repeat:

So I either need to find a way to shift the textbox upon the combobox change

I do not see why...as the code does EXACTLY that. It shifts the textbox upon the combobox change.

Period. Full stop.

I guess I could use the Form change event to perhaps do what I need. Would that trap a combobox change?

No, it will not. You can guess that all you want, but there is no Form_Change event.

Yes, you certainly can cycle through all controls on a form. Yes, you use the forms .Controls collection.

The problem is that it is impossible to do that when the macro can auto-create up to 200 comboboxes.

I'm sorry, but I would have to see a real business case for any userform having 200 comboboxes. That simply seems absurd to me. No offence intended. If I had a situation that seemed to come even close to needing 200 comboboxes, I would go back and do some serious rethinking about design and functional requirements.

Good luck.

Gerry
My paintings and sculpture

 

[stevexff]

It is possible to handle events from dynamically-added controls.

Essentially you have to make a little class module which has a 'WithEvents' reference to the dynamically added control. Each time you add a control, you make a new instance of your class, and bung it in a private collection. When you click on the control (or whatever event you are handling) it calls back to the event handler in the instance of the event handler class.

I won't explain further, as combo has already written a nice FAQ about it, faq707-4976

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[HughLerwill]

Combo's FAQ looks to be the way to go, however, note that it is possible to create VBA code at runtime re my post in thread707-1167069.

Hugh,

 

[bdmangum]

Thanks for the responses guys.

Steve and Hugh,

I'll look into those guides and test them out.


Gerry,

I'm getting the feeling that me and you aren't on the same page with this. The textbox shifts fine when I use a combobox change event. However, I don't have one for each combobox. The textbox's left position is set only upon the form's initialization, thus it will not shift upon a combobox change.

 

[fumei]

Well, it seems you are not even on the same page with yourself.

The textbox shifts fine when I use a combobox change event. However, I don't have one for each combobox. The textbox's left position is set only upon the form's initialization, thus it will not shift upon a combobox change.

Let me break that up into two statements:

1. "The textbox shifts fine when I use a combobox change event. "

2. "thus it will not shift upon a combobox change"

Sorry, my friend, it either shifts with a change, or it does not. You can not have it both ways.

You are absolutely, totally, wrong in stating the textbox position is "set only upon the form's initialization". That is completely false.

The textbox position is set by the combobox_change event. When the form, any form, initializies it fires the change event, as indeed, by populating the control, you are making a change. ALL other changes will fires _Change, and ALL other changes will adjust the textbox .Left position.

We are not on the same page because, as I stated, EACH combobox has its own Change event. The textbox position is tied to a specific combobox change event. It seems, but I may be wrong, that you are thinking the textbox will move with any combobox change. No, there is a specific Change event. Read the code.

Sub [b]ComboBox1[/b]_Change()
TextBox1.Left = ComboBox1.Left + ComboBox1.Width + 10
End Sub

Textbox1 will set its Left positin EVERY time Combobox1 is changed. EVERY time. NOT just at initialization. EVERY time. EVERY time. EVERY time. EVERY time. Read the code. When Combobox1 changes...Textbox1.Left = .....

Since I have repeated this a number of times, and you seem to persist in....well I am not sure what exactly what you are persisting in...I will leave this thread now. I do not see how I can add anything more.

Except to suggest you use combo's FAQ to make a class module and then make a Collection of your comboboxes. b Although you will still be using each combobox change event.

Good luck.

Gerry
My paintings and sculpture

 

[stevexff]

Following on from Gerry's comments:

Assuming you are dynamically creating the ComboBoxes and the associated TextBox at the same time, you can add an instance variable to your ComboBoxListener class. Pass in a reference to this when you initialise the ComboBoxListener. When the ComboBox change event gets fired, your listener class will already have a reference to the associated TextBox, making it simple to modify its co-ordinates on the form.

# note - pseudocode

mylisteners = new collection

for each thing in list you want to put on the form
   cb = new combobox
   tb = new textbox
   position cb and tb on form programmatically
   cbl = new comboboxlistener
   cbl.init(cb, tb)
   mylisteners.add(cbl)
next

mylisteners acts as a bucket to hold however many comboboxlisteners you care to create. Each comboboxlistener is hooked up to its associated combobox for event notification, and each has a reference to its associated textbox, so when the event fires, it knows which textbox to adjust. Apart from remembering to clear out the mylisteners collection if you repopulate the form, that's pretty much all there is to it...

Aren't objects wondeful?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[fumei]

Objects ARE wonderful. Or "wondeful". Using them makes all the difference.

Gerry
My paintings and sculpture

 

[stevexff]

Objects may be wonderful. Clearly, my spelling is woeful...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[fumei]

<grim>....I mean <grin>

Gerry
My paintings and sculpture