General discussion

Locked

Excel

By ldalton_us ·
I am creating templets and do not know how to assign form tools to them. Check marks, list boxes, etc. I want the templet to store info in an Excel data base. Help!

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel

by SteveD In reply to Excel

Sorry, but in my opinion Excel just isn't the right tool for this kind of project. In the first place, there really is no such thing as "an Excel data base." Excel can interact with data in external databases (like Access or SQL Server), though. In the second place, Excel isn't the best environment for building forms such as you're describing.

You would be much better off using Access. Once you have defined your database structures (tables) there, you can use the form building Wizard to create forms for data entry that have all the appropriate check boxes, list boxes, etc. There are also Wizards to help you create queries and reports. And you'll be able to use Excel to analyze your data too.

It's always a lot easier when you use the right tool for the job.

I hope this helps.

Steve Diamond

Collapse -

Excel

by ldalton_us In reply to Excel

Poster rated this answer

Collapse -

Excel

by Peyison In reply to Excel

Sure you can use Excel as a database, and I can think of server cases where it might be a good option to do so.

Anyway, to answer your question -

To add form controls to a sheet, you first need to open the Forms toolbar:

Select View -> Toolbars -> Forms

Then you pick the control you want and click on the sheet where you want the control to go.

If you want to use Radio Buttons, and you have multiple sets of them, you have to put each set in its own Group Box. This is because only one Radio Button can be selected at a time - the others in the group will be unselected.

Once the control is on the sheet, you can link it to a cell to capture the selection.

For Radio Buttons and Check Boxes:

* right click on the control
* select Format Control
* choose the Control tab
* enter a cell in the Cell Link box

The cell linked to a Radio Button will contain the number of the button selected. The cell linked to a Check Box will contain True (checked) or False.

Fora Combo Box and List Box - you link to a cell the same way, but you also need to link to a range of cells that contain the values that are to appear in the list.

A Spinner Control also links to a cell - pressing the up and down arrow buttons "spins" the value in the cell. You can assign a max, min and an increment

A Scroll Bar works like a Spinner, except the value in the linked cell changes when you scroll it up or down.

More to follow...

Collapse -

Excel

by Peyison In reply to Excel

The last thing you might want is a Text Box, but you can't use the one on the Forms toolbar. Open the Drawing Toolbar, and there is a Text Box there that you can use. This is handy for free text. You can add a drop shadow, etc. You don't link itto a cell, you have to reference it by name (change the name in Excel's Name box at the upper left of the screen, above column A.

One last thing - if you protect the sheet (which is a good idea), make sure the cells that you've linked to are unprotected.

Hope this helps.

Collapse -

Excel

by ldalton_us In reply to Excel

Poster rated this answer

Collapse -

Excel

by SanKulPune In reply to Excel

Hello,

You can use the following VBA code assuming that you want to change the color of the cell A1.

Cells(1, 1).Interior.ColorIndex = 6
Cells(1, 1).Interior.Pattern = xlSolid
Cells(1, 1).Font.ColorIndex = 3

SanganakSakha
Simple And Effective Solutions To Small Problems, No quickFixes

Collapse -

Excel

by ldalton_us In reply to Excel

Poster rated this answer

Collapse -

Excel

by ldalton_us In reply to Excel

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums