Data Validation Text Box OR Other Solution

Mister H · Mar 7th, 2002, 02:34 PM

Hi: I have created a spreadsheet with most cells having Data Validation in them. I have mainly used the Data Validation for general comments (eg. Type persons name here or Enter Employee's ID #). My question is in regards to the location of the Data Validation Text Box. When I orginally do the Validation the text box appears underneath and to the right of the active cell. This is what I want. The problem is that if someone clicks on and drags that box it no longer appears in that location it now appears whereever they have dragged the box to as do ALL OTHER Data Validation Text Boxes in the spreadsheet (they now all appear in the exact same location). This spreadsheet will be used by a lot of people and I am sure someone will move the Text Box. Does anyone know how I can make sure that the Text Box ALWAYS appears below the active cell. OR Does anyone have a better solution for indicating what I want entered in each cell? Any help you can provide would be greatly appreciated.

Thanks,
Mark

Damon Ostrander · Mar 7th, 2002, 03:34 PM

Hi Mister H,

Yes, the position of the textbox associated with validation prompting cannot be set from VBA code because the Excel object model does not expose it. The only options I can think of off hand are:

1. Use a comment in the cell. Comments CAN be controlled from VBA code, so you could have a macro that moves all the comments to the position where you want them in the workbook's open event so that they are all initialized in the desired position relative to the cell.

2. You could use the worksheet's SelectionChange event to determine when a validated cell has been selected, then pop up your own "custom" textbox wherever you want it. The advantage of this is that you would then have control of the textbox's shape type, meaning you could pop up a star, oval or some other shape if you so desired.

If you are interested in one of these solutions, let me know.

Mister H · Mar 8th, 2002, 07:15 AM

Hi Damon:

I would be interested in knowing how to do both. Unfortuantely I don't really know alot about VBA. Would it be possible to e-mail me a sample of each so that I could look at how you have done the coding? I don't expect anything elaborate and I don't expect you to send me anything if it is going to be a very time consuming process. If you can help me my e-mail address is
mark.huggins@mnr.gov.on.ca

Also on another note do you have any idea where I can find easy-to-follow documentation that might help me to understand VBA and its capabilities. Most of the things I have seen are beyond me. If I see a sample of what you are talking about I should be able to duplicate that.

THANKS Damon,
Mark

Damon Ostrander · Mar 10th, 2002, 11:59 PM

Hi Mark,

Its on its way....

Mar 7th, 2002, 03:34 PM	#2
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Hi Mister H, Yes, the position of the textbox associated with validation prompting cannot be set from VBA code because the Excel object model does not expose it. The only options I can think of off hand are: 1. Use a comment in the cell. Comments CAN be controlled from VBA code, so you could have a macro that moves all the comments to the position where you want them in the workbook's open event so that they are all initialized in the desired position relative to the cell. 2. You could use the worksheet's SelectionChange event to determine when a validated cell has been selected, then pop up your own "custom" textbox wherever you want it. The advantage of this is that you would then have control of the textbox's shape type, meaning you could pop up a star, oval or some other shape if you so desired. If you are interested in one of these solutions, let me know. __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Mar 10th, 2002, 11:59 PM	#4
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Hi Mark, Its on its way.... __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 02:34 PM	#1
Mister H Board Regular Join Date: Mar 2002 Location: Sudbury, Ontario Canada Posts: 1,219	Hi: I have created a spreadsheet with most cells having Data Validation in them. I have mainly used the Data Validation for general comments (eg. Type persons name here or Enter Employee's ID #). My question is in regards to the location of the Data Validation Text Box. When I orginally do the Validation the text box appears underneath and to the right of the active cell. This is what I want. The problem is that if someone clicks on and drags that box it no longer appears in that location it now appears whereever they have dragged the box to as do ALL OTHER Data Validation Text Boxes in the spreadsheet (they now all appear in the exact same location). This spreadsheet will be used by a lot of people and I am sure someone will move the Text Box. Does anyone know how I can make sure that the Text Box ALWAYS appears below the active cell. OR Does anyone have a better solution for indicating what I want entered in each cell? Any help you can provide would be greatly appreciated. Thanks, Mark

Mar 8th, 2002, 07:15 AM	#3
Mister H Board Regular Join Date: Mar 2002 Location: Sudbury, Ontario Canada Posts: 1,219	Hi Damon: I would be interested in knowing how to do both. Unfortuantely I don't really know alot about VBA. Would it be possible to e-mail me a sample of each so that I could look at how you have done the coding? I don't expect anything elaborate and I don't expect you to send me anything if it is going to be a very time consuming process. If you can help me my e-mail address is mark.huggins@mnr.gov.on.ca Also on another note do you have any idea where I can find easy-to-follow documentation that might help me to understand VBA and its capabilities. Most of the things I have seen are beyond me. If I see a sample of what you are talking about I should be able to duplicate that. THANKS Damon, Mark