Trying to make a list/form Newbie!!!!!

[wanderer]

At this point I'm ready to hit the rum bottle. I have inputed names of
medications down column A, then sorted it to be in alphabetical order.
Next I wanted to put a checkbox next to each one to allow someone to
mark the boxes when looking at a printed copy to indicate which
medications they are currently using. When I got to view - toolbars-
forms and insert a damn checkbox, I get a rather large object with the
identifying caption, such as 'checkbox 1'. This isn't what I was hoping
to do. Then I tried to insert a checkbox and change the caption to a
name of a medication. Of course it won't let me cut and paste so I get
to retype it in. Then I insert the next box but it isn't aligned with
the previous box, so I move it to do so. Then by doing so it clips some
of the letters of the box above it such as the letter 'P'. At this
point I am ready to become a monk and say the hell with it. I have
Office 2003 and tried to use infopath to do it but that seems to be
over my head too.

Can anyone out there help me with this in a manner that will make
things look nice and be aligned and hopefully won't make me retype the
200+ medications I've already entered?

Many thanks

 

[Biff]

How about a macro? (by Dave Peterson, slightly modified by me to add the
captions!)

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("B1:B4").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = myCell.Offset(0, -1) 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub

Change this line to the actual range where you want the checkboxes:

For Each myCell In ActiveSheet.Range("B1:B4").Cells

For example, if you want the checkboxes in B1:B100, then:

For Each myCell In ActiveSheet.Range("B1:B100").Cells

The value listed in cell A1 will become the caption for the checkbox in cell
B1.

Place this code in a GENERAL module:

Hit ALT F11 to open the VB editor
Hit CTRL R to open the Project Explorer
Look for the name of your file. It'll look like this: VBAProject
(your_filename)
Right click on your filename
Select Insert>Module
Paste the code above into the window that opens on the right.
Close the VB editor and return to Excel.

To run the macro:
Goto the menu Tools>Macro>Macros
Select the macro and click Run

Biff