Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data validation with zero values checked (2000-2003)

    Greetings:
    I have a cell where I'd like there to be 0 values by default in certain "empty" cells, and I'd also like users to be able to set the values of those cells based on a pre-set drop-down list.

    * I go to Tools, Options, View tab, and uncheck Zero Values, so no zeroes show up in the sheet.
    * I go to Data, Validation, select List, and type in: 0,1,2,3,4,n/a.

    The drop-down displays correctly, but I can't type 0 in a cell - it errors out. I have to select it from the drop-down. The other numbers don't have this problem.
    * If I re-check Zero Values, this problem goes away, but now I see lots o' messy 0s everywhere.

    * If I use another column to enter the 0,1,2,3,4,n/a, and point to it from the Data, Validation box, it works fine, BUT
    * If I uncheck Zero Values (as I'd like to make the 0s go away), the dropdown displays a blank option instead of a 0.

    Ideas? Thank you!

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: data validation with zero values checked (2000-2003)

    You are trying to have your cake and eat it.

    You want to turn off display of zeroes but you want the User to be able to enter a zero and see it (but not other zeroes).
    You could do this by trickery.
    The trick involves having the cell display a zero, but actually containing a small non-zero value.
    For example, the cell is formatted to show no decimals but actually contains 0.0000001
    1. Turn off the display of zero values as before
    2. Change the dropdown to only allow 1,2,3,4,n/a (leave out the 0 choice!)
    Then, for entry of a 0, have the User double-click the cell to have VBA put the small non-zero value in the cell.

    Suppose your dropdown validation is in cell [G25]

    You can use something similar to the following:

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    zCell = Target.Cells(1).Address 'fetch address of any double-clicked cell

    If zCell = "$G$25" Then 'User double-clicked this particular cell [G25]
    [G25] = 0.0000001 'set small non-zero value
    Cancel = True 'ignore usual double-click operation which is to Edit the cell
    End If

    End Sub
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~


    You could amend the above to suit

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation with zero values checked (2000-2003)

    You mean I can't have my cake and eat it too?? Such a puzzling world...

    Thank you - this is very cool and I would never have thought of it!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: data validation with zero values checked (2000-2003)

    <hr>You mean I can't have my cake and eat it too?? <hr>

    No because once you eat your cake, you no longer have it, it is just a predigested mass and no longer cake... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation with zero values checked (2000-2003)

    I never cease to be amazed at all I learn on this site...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: data validation with zero values checked (2000-2003)

    Woody's Lounge - everything about cake you never wanted to know! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: data validation with zero values checked (2000-2003)

    Really Hans ..that takes the biscuit

    zeddy

Posting Permissions

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