PDA

View Full Version : Userform controls



demarc
12-04-2012, 07:13 PM
Hi,

When I create a userform in excel it looks like if has a scrollbar and various buttons, i.e.:

New, Delete, Find Prev, Find Next, Close etc

I would like to know the code to include these options on my own userform, how can I access the code on the excel created userform to understand how these options work?

Thanks,
Demarc

Jan Karel Pieterse
12-05-2012, 01:11 AM
I'm not sure I follow. If you insert a userform in your project it has no controls.

You add controls by drawing them on the form using the control toolbox. Then you double-click each control and write the VBA code that does what you want the control to do when the user takes an action (event).

demarc
12-05-2012, 01:55 AM
Thanks for the reply, I will try and explain further.

When I create a few headings for a table, say:

A B C D
1 NAME AGE HEIGHT WEIGHT

If I select from A1:D1 and then press the form button I have added to the quick access toolbar, excel creates a userform that has the above mentioned controls automatically i.e. you can add/delete/scroll through records using the various controls.

I want to design my own userform, that is opened with a button (can do this bit) that has the same features as the automatically generated userform in excel. I am after a link to the code for each of the buttons and scrollbar on that form or an idea how I can access the code for the automatically generated userfrom.

Hope that helps,
Cheers,
Demarc

demarc
12-05-2012, 01:57 AM
hmmmm, some formating issues. Hope you can still make sense of it.

Should have read 'if I now select from A1 through D1 ....

Aflatoon
12-05-2012, 04:18 AM
You cannot access the code used in the built-in Data Form.

demarc
12-05-2012, 04:29 AM
Thanks for the reply,

I now realise that after a little trying, any ideas on what the code is or where I can find it would be helpful.

Cheers,
Demarc

Jan Karel Pieterse
12-05-2012, 04:46 AM
That code is in C++ somewhere hidden away inside the Excel application, unavailable for us mere mortals I'm afraid.

demarc
12-05-2012, 06:23 AM
Sorry I am prob not using the correct technical terms for what I am after as I am a novice (clearly) at using VBA and userforms, I'll try again.

All I want to achieve is to make my own userform with:

- A scrollbar that you can move forwards and backwards through the existing records in the tabe, each record displayed in the relevant text box of the form;
- A button that can delete an existing entry once I have scrolled to it;
- A button that when pressed adds a new entry to a table.

Surely there is VBA code for these simple actions?

Thanks again,
Demarc

Aflatoon
12-05-2012, 06:38 AM
There is an article here (http://blog.contextures.com/archives/2010/10/01/new-improved-excel-data-entry-form/) which demonstrates how to create a worksheet data entry form - it should be fairly simple to adapt the principles to a userform. Failing that if you search you will find numerous samples available - J-Walk has a version on his site, though you have to pay for the source code.

demarc
12-05-2012, 06:21 PM
Aflatoon,

This looks like a step in the right direction, thanks for your assistance!

Demarc