Add Custom Control to VBA toolbox

I have created a "hybrid" textbox that functions as a refEdit box, however, there is a bit of coding running in the background and I'd like to use this again and again and again and... Well I think you get the point, how do I get the box INCLUDING the code into the toolbox?

I have read drag and drop onto the toolbox, but apparently this only moves the button with visual properties, and not the code, I really need the code as well.

Thanks

Which Toolbar, your profile says that you are using Excel 2010. Do you mean the QAT?

Morning Roy

Sorry but I use 2007, and no, not the toolbar, but the userform toolbox

As you and the help say,

When you drag a control onto the Toolbox, you only transfer property values. Any code you have written for that control does not transfer with the control. You must write new code for the icon or copy code from the control on the form to the control on the Toolbox.

Depending on the code and events used you could move the code to a class object and then export that. To reuse create the control, import the class file and then declare an object within the code and make the assignment.

Goeie More Jacques,

See the attached WorkBook. I can use the TextBox over and over again. What do you want to do with the value that appears in Label 4?

Hi Winon,

I'd like to basically use the text box and label containing the value as a template, to be used miltiples on the same form, pointing to various cells etc... And then also reuse the text-label setup on multiple other forms, so I'm not sure if what you suggest will work.

Hi Andy,

How would I go about getting it to a class and then declare it for reuse?

That all depends on your code. Can you post example workbook?

Hi Andy

Everything except the OKtbn is what I'd like to have for reuse.

Maybe you overcomplicated things too much:

cfr. the attachment

Hmmm, I might have overcomplicated it, thanks SNB, will make it a bit shorter, but the question still remains:

Can I make a template of the setup above to the extend that I can drag and drop it onto my userform, the same way I can drag a textbox or label onto my form?

I might have a setup where I have 2 or 3 of these setups and then changing all the names is not very difficult, but I am planning forms where I might have 30 to 40 of these and then I dont want to sit and change names all day.

You can export the code (.bas) and import it in any VBproject you like.
The only thing you have to do is import the code into the new userform and add a new textbox with the unique name you have given to this 'refedit control'.

So if you use instead of 'Textbox1" the name "ref_grob" it's very easy to do.

Here is the class, because the BeforeUpdate event is not exposed within the class I have used the Change event and the result label displays #VALUE! is the result is not valid rather than a popup message box.

CRefEdit class

Please Login or Register  to view this content.

So you would add your compound control to a user form, include the class code and then assign relevant controls to the object.

userform code

Please Login or Register  to view this content.

Andy, that is fantastic!!!

I am unfortunately new to classes, so how would I go about declaring a second set of buttons, say text2 and label5?

Very nice, thanks Andy,

I see it is a case of declaring instances and repeating pretty much what is in the userform code, have an instance of m_clsMyRefEdit, now create one for say n_clsMyRefEdit.

Not exactly what I had in mind when I started this thread, but it sure will do what I'd like.

The userform code is moved to the class and events are captured within the class.
You still have to generate some code in the userform, which is as you say declaration of object, initializing it and assigning relevant controls.