+ Reply to Thread
Results 1 to 11 of 11

Two Toolbox Controls Questions

  1. #1
    Registered User
    Join Date
    12-21-2006
    Posts
    5

    Two Toolbox Controls Questions

    I have a form with two data-entry cells and three listboxes ...
    1. Users will type a number (e.g., "100") into one of the two appropriate cells and then, without pressing Enter, proceed to make selections via the listboxes. It appears to them that the the number has been properly entered even though this is not the case. How might I address this problem?
    2. Replacing the data-entry cells with spin buttons raise a different issue. If I have such a button in, let's say, cell A1, it is easy to link it to A2, but I can find no way to allow the button to modify the value in A2 while preventing the user from editing A2 directly (thereby preventing the user from entering something other than integers within an appropriate range. How might this be accomplished?
    Finally, a question related to #2 above: is it possible to constrain the value of a particular cell, e.g., can I restrict A2 to integers between 1 and 10? Thank you.

  2. #2
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Restricting a cell value is the easiest, so I will answer it first.

    Look at Data >> Validation

    There is a downside to Data >> Validation. A user can kill it by copying a blank (or really, any) cell into your validation cell and wipe it out. So, when you set up the validation, do it with the macro recorder on. Then, run this macro often (maybe every time the workbook opens, every time they change sheets, and any other events that seem like a good idea).

    Is it really necessary that your users see the sheet that the form controls are connected to?

    Could you connect the form to a hidden sheet? If they need to see the numbers, you could still connect the form to a hidden sheet and show them the cell values by referring to the hidden sheet in the not-hidden sheet.

    If you use Named Ranges for the references instead of actual cell addresses, they would still have no idea that there is a hidden sheet.

    To make it VERY secure, make the sheet VERY hidden.

  3. #3
    Registered User
    Join Date
    12-21-2006
    Posts
    5
    Quote Originally Posted by MSP77079
    Restricting a cell value is the easiest, so I will answer it first. Look at Data >> Validation
    Thank you.

    Quote Originally Posted by MSP77079
    Is it really necessary that your users see the sheet that the form controls are connected to?
    Perhaps a simple example will explain my main issue ...

    Imagine a worksheet with three objects:
    1. Cell A1 is used to input a quantity between 1 and 100.
    2. Below that input cell is a list box containing N items from a table hidden to the user. That table associates each item with a unit price.
    3. Below the list box is a cell that displays total cost, obtained by taking the selected item, using vlookup to obtain the unit price, and multiplying this unit price by the quantity typed into cell A1.
    The user types "25" into A1, fails to press the Enter key, and proceeds to select Widget #3. He sees "25", "Widget #3", and a total price, but that price does not reflect the displayed quantity. How might this problem be addressed?

  4. #4
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    I get it. But, I am unable to duplicate this with a control on the worksheet because when the worksheet is in Edit Mode (i.e., I am in the process of changing a cell value, but have not pressed "Enter"), the control on the worksheet will not drop down for me.

    Since I cannot duplicate the problem, I cannot be sure my solution will fix it. But, try it anyway ... it can't hurt.

    Does the drop down box have any code behind it? If so, then for the first line of code put this:

    Please Login or Register  to view this content.
    If not, then add this code:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-21-2006
    Posts
    5
    No change, but thanks ...

  6. #6
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Can you remove the data from your workbook and post it?

  7. #7
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203

    Let's return to the spin button question, then

    As you have proposed, a spin button would solve the problem.

    The way to do this without allowing the user to enter values in the cell is to:
    1. protect the cell
    2. the spin button code would remove protection, make the change, then restore protection

  8. #8
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203

    Code for spin button

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-21-2006
    Posts
    5
    Thanks - I'll give it a try ...

  10. #10
    Registered User
    Join Date
    12-21-2006
    Posts
    5
    Nope ... but thanks anyway.

  11. #11
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    What does "nope" mean?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1