Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to make a list/form Newbie!!!!!
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to make a list/form Newbie!!!!!
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 InsertModule 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 ToolsMacroMacros Select the macro and click Run Biff "wanderer" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a 3-dimensional plot in Excel? | Charts and Charting in Excel | |||
Can I make formulas more flexible? | Excel Discussion (Misc queries) | |||
How do I make each row add up seperatly? Please make it easy... | New Users to Excel | |||
make hidden window or workbook visible without specify the name | Excel Worksheet Functions | |||
make cell contents equal to null value - not blank, but empty | Excel Worksheet Functions |