+ Reply to Thread
Results 1 to 15 of 15

Resetting Dropdown list in all sheets (Excel Workbook)

  1. #1
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Resetting Dropdown list in all sheets (Excel Workbook)

    Dear All,

    I am a newbie to Excel Macros, and i have been trying to work out on resetting the value of all drop-down list available in Excel Workbook (created via Data Validation) to Default value (i.e. First Value of the list)

    I cam across a piece of code on a different thread http://www.excelforum.com/showthread.php?t=847615 which works perfectly only for one sheet i.e the name of the sheet given in the code ("Entry Sheet"), Can someone please guide/help me to enhance the below code to work on the workbook (multiple sheets).

    Please Login or Register  to view this content.
    Thanks,
    Hawkz

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,466

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Try this
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Hi Yasser,

    Thanks for the reply. I tried the above code, but it gives me the following error. (Attached image) Run-time error '1004': Method 'Range' of object '_Worksheet' failed. Thanks.

    Capture.PNG

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,466

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Can you upload sample of your workbook you are working on? It is working for me

  5. #5
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Yeah,sure.Drop.xlsm

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,466

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Try this
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Hi Yaseer,

    I really appreciate your help! This seems to work, however the only issue now is after clicking the button, my focused page/sheet shifts. For example: The button is on "Test" sheet, and when i click that the drop-down do resets to default, however I am taken to "Data" sheet, i.e. I am no longer on "Test" sheet where I originally was.

    Regards
    Hawkz.

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,466

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Hello Hawkz
    Try adding this line at the end of the code
    Sheets("sheet1").Activate
    I supposed you need to get sheet1 active after running the code

  9. #9
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Hi Yasser,

    It worked on my sample, however if i integrate with my complete sheet I start getting 1004 Error again. Attached is the workbook.
    New Drop.xlsm. Kindly bail me out through this condition.

    Regards
    Hawkz

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,466

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    I think it is because the validation list in cell I5 in sheet Cover is related to another external workbook ...
    The code is working for the internal DV only ...
    May be some experts help us in this point

  11. #11
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    I have removed that validation and External link, but still it shows me the same error.

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,466

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Are there any validation lists that are not connected to list?
    I mean is there VD which has no source list .. that is typed manually in the source field

  13. #13
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Nopes, all of the Validation List has been referred from "Constant" Sheet i.e. (Constant!$F$9:$F$11). The problem is not only in the "Cover" Sheet but also in the "Drops" sheet.
    I tried, using individual sheet specific drop down reset button, but the error persists. Thanks.

  14. #14
    Registered User
    Join Date
    12-23-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    11

    Re: Resetting Dropdown list in all sheets (Excel Workbook)

    Hi All,

    Can anyone please bail me out from this issue. Thanks.

  15. #15
    Registered User
    Join Date
    08-07-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    1

    Re: Resetting Dropdown list in sheets (Excel Workbook)

    I have used the VBA code:

    Sub clearcheck()
    Dim sh As Worksheet
    For Each sh In Sheets
    On Error Resume Next
    sh.CheckBoxes.Value = False
    On Error GoTo 0
    Next sh
    End Sub

    But now need to have this only apply to a specific set of worksheets. Can anyone tell me how to make this change?

    any help is appreciated.

+ 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