Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Kent, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation List (Excel 2000)

    I have a workbook, with various worksheets. Some of the cells have 'Data Validation Lists' set on them which allow input of data from a fixed list of options from another 'linked' workbook. When I set 'Protection - Shared' on it loses the validation list. I believe this is because the link is 'dropped'. Does anybody have any ideas how I can share the worksheet, and still keep the validation lists. Also if I protect the worksheet, I can no longer run macros from the 'buttons' I have setup - is there a way round this ??

  2. #2
    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 List (Excel 2000)

    If you want users to input values, the "data validation cells" must be unlocked (format -cells - protection - uncheck "locked")

    To allow the macros to work on protected sheet, you must Unprotect the sheet at run time or when the workbook opens add a line to the workbook open event to allow macros to change without unprotecting:
    <pre>Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect userinterfaceonly:=True
    End Sub</pre>


    This will protect sheet1 but allow macros to change (change as appropriate)

    Or for a lot of sheets:
    <pre>Private Sub Workbook_Open()
    Dim wks As Worksheets
    For Each wks In Worksheets
    wks.Protect userinterfaceonly:=True
    Next
    End Sub</pre>


    This will protect all the worksheets, but allow the macro to change items without needing to unprotect first

    Steve

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Location
    Kent, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation List (Excel 2000)

    Thank you Steve, that works great - setting the validation cell 'unlocked', then protecting the sheet, the macro then runs OK with the code line added as you say. .... The only problem now is that as soon as I try to share the workbook, using either 'Tools-Shareworkbook' or 'Tools-Protection-Protect & Share' - I lose the validation lists again. Am I missing something obvious ??

  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 List (Excel 2000)

    Validation command is unavailable in shared workbooks
    <hr>Although you can continue to enter data and the input and error messages continue to be displayed, the Validation command is unavailable while a workbook is shared.<hr>

    But validation lists should work if set before the workbook is shared. I am able to do it in XL97. I don't use XL2000 so can not test it there.

    Steve

  5. #5
    New Lounger
    Join Date
    Dec 2001
    Location
    Kent, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation List (Excel 2000)

    You're right about it still working on cells that have Validation set BEFORE the workbook is shared.

    I have a macro button that copies a row (with data validation cells in it) to the 'users' worksheet, but when the document is shared the newly copied row loses the validation cell settings. I'm a little stuck now as it was all working perfectly until I shared it.

    The data that the user can select data from is on a 'hidden from them sheet' that contains 3 columns of data (A,B,C). I guess what I need to do is create a 'user input form' that has 3 dropdown scrollable lists, the second list (column [img]/forums/images/smilies/cool.gif[/img] being dependant on what is selected on the first (column A), then the third (colum C) being dependant on the second. Once the three selections are made it then inserts this data as a new row on the user's worksheet.

    This sounds simple enough, but I'm a bit of a novice with VBA & macros and try as I might I cannot find any examples of code that provide this 'validating' on previous column. Your help is much appreciated.

  6. #6
    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 List (Excel 2000)

    Here is an example that works with datavalidation.
    You have more control with Userforms however, so you really don't need all the tricks you need here.

    Here is a nice primer on creating a userform.
    To make dependent lists will depend on where you lists and sublists are located and how it is setup. You have lots of control with VB so it is tough to give a "general" example".

    How is your data setup? I might just be simpler to create the list at run-time and add it to the list, then do a lot of formula work in excel to get a list.

    Steve

Posting Permissions

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