Data Validation Text Box OR Other Solution

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top