How to create a macro which will fill a 'table' worksheet from another 'form' worksheet

[tomgnihtemos]

The intention of this workbook is to include a table, which lists straight data. This table will be used to extract reports from at a later date, which should hopefully be the easy step!

The catch is that I would like to have a second worksheet, which has the layout of a form. Once complete, I would like for users to be able to enter information on this form, which would then enter information into the table, on a separate worksheet. Then, once a full form has been entered into the 'table' worksheet, the plan is to have a button control which would run a macro, which would do the following:

• clear the form fields
• have the ediable fields on the form 'link' themselves to the next field on the table worksheet

At this stage, I have used ActiveX textbox controls on the form worksheet, and then used their Properties to link each control to the relevant table worksheet. This has worked wonderfully as far as inputting data across a single row, however I'm a bit stumped when it comes to creating a macro which may be able to alter the ActiveX properties (appears this may be impossible...!)

Any feedback is welcome. I'm a bit of an Excel newbie, and understand if going down the ActiveX line has been a waste of time. If this can be done without these controls, that would be great too.

Thanks.

[alansidman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[tomgnihtemos]

Ah, good call - unfortunately I don't have MS Office 2007 at home, however I'll get something together soon...

[tomgnihtemos]

Hi again,

Please see attached sample... It's a bit hard to mock up exactly what I want, but to briefly explain it..

The second worksheet ('form') would ideally be filled in by someone, and then would have a simple function which would empty each of the controls, and then alter their 'preferences' so that they are locked onto a separate cell on the 'Table' worksheet. (for example, the field for 'Information' would change from being locked to Table!B2 to being locked to Table!B3)

The ideal way to work this out that I can think of, would be having a macro button, which when clicked would alter the preferences of each of the fields ... however I couldn't begin to guess how to create a macro which alters preferences.

Any feedback welcome, if you need any more info or if there is a simpler way, please let me know.


Edit: Another thought was to remove the controls altogether, which would probably make life a lot easier, however it would be ideal to keep the tickboxes.. anyway, if removing the controls is the way to go that's cool.

test.xlsm

[alansidman]

and then alter their 'preferences' so that they are locked onto a separate cell on the 'Table' worksheet. (for example, the field for 'Information' would change from being locked to Table!B2 to being locked to Table!B3)

I don't understand what you mean. Please explain with an example. What does "locked" mean?

[alansidman]

Look at the attached. It should help you to get this on the right track.

[tomgnihtemos]

Sorry - LINKED* cell. In the ActiveX control's properties (developper tab option) you can elect to link it to a cell .... So far I have linked the table to the form worksheet my linking the controls to Table!(cell here).

Not sure how much that helps explain where I'm coming from. I'll take a look at your attachment ASAP.

Thanks for your help so far.

[tomgnihtemos]

Excellent, can definitely use this!!

Thanks again, very much.