Data Validation Text Box OR Other Solution
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Data Validation Text Box OR Other Solution

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,502
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,502
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Mark,

    Its on its way....

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com