Text box with a Scroll Bar

[finmanLI]

Dear members,

I am attempting to create a lookup spreadsheet. The big picture is this: I need a quick way to look up what paperwork i have and what i still need for each of my 200+ clients. I have created checkboxes and a document matrix where document names get color coded when a checkbox is checked next to clients name. Now i am attempting to add another feature where when the box is checked off i can see notes ive put in for that client. I picture it as a textbox with a scrollbar linked to a cell on a different sheet where the notes are stored. I cannot to get it to work, i am able to insert the textbox but the notes appear in a single Line, no matter how big the texbox is, which makes the scroll bar not function. Please help in any way possible. Thank You !

 

[mikerickson]

Is the TextBox set to .WordWrap = True?

 

[finmanLI]

I do not have that option or at least i can not find it. I working with 2007 excel, im not sure if that changes things. I insert the Activex control ComboTextbox from the Developer Tab, via the insert button. I adjust the properties with Design Mode on. Anything else ?

 

[mikerickson]

.WordWrap should be one of the properties you can set.

 

[finmanLI]

Maybe im missing some basic step, im doing this for the first time. How do you set .WordWrap=TRUE ?

 

[mikerickson]

Someone should check these instructions on a Windows machine.
I'm assuming this is a ActiveX TextBox from the ToolBox. Not from the Drawing Menu and not on a Userform.
Right click on the textbox. A pop-up menu should appear and one of the menu options should be Set Properties. That will give you access to all kinds of goodies, like Fonts, Bacground Colors and WordWrap.

 

[mattrx731]

The way Mikerickson describes, works on Windows Vista, Excel 2007 for me.

 

[finmanLI]

Could you please give me step by step instructions on exactly what you did? I go to the Developer tab in the ribon, Insert button in the Controls section of the ribbon, I select a combobox from the Activex section, then draw the texbox, click on the Design Mode button, right click the textbox, menu pops up with a whole bunch of selections. One of which is Linked Cell, which is set to lets say Sheet1!A2, then the text in the A2 cell appears in the textbox but it is all in one straight line as if it was in a regular excel cell. WrapText is not of the options in the menu that pops up after clicking properties. What am I doing wrong? Thanks for all the help so far, dont give up on me!

 

[finmanLI]

Ok i have figured out how to insert a textbox with a scroll bar that draws text from a different sheet. I was selecting a COMBOBOX instead of a TEXTBOX. I know it is very ingenius of me, hope you can sense the sarcasm. Now the second part of the project is to make sure that it draws its values from clients column next to which the checkbox has been checked. Any suggestions ?

 

[mikerickson]

How is your data layed-out?
If many check-boxes are checked, how many text boxes do you want to see?
Option buttons would be an alternative if you only have one textbox.
If many buttons may be clicked, it might be easier to use cell comments rather than a textbox.

Application.Caller and the .TopLeftCell property can help you locate the position of a checkbox.

Msgbox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address

Are each of these checkboxes driven by its own code or are you using a custom class to unify the code?

Adding a flock of controls to each row in a data base is usualy not needed. Cells have enough built in goodies (comments, SelectionChange events to emulate a checkbox) that the extra controls are frequently not needed.