Data validation short cuts with macro button RRS feed

  • Question

  • I am working on a spreadsheet that looks up data from a large database of members. I want to be able to compare different types of members against each other. I am using a data validation list to populate the members I wish to compare in separate columns using:


    If Target.Validation.Value = True Then
          iCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 2
          Cells(Target.Row, iCol).Value = Target.Value


    Is there a way to create a shortcut using a "button" that would allow the user to jump to a particular subset of the data. 

    IE:  I have data on structural steel members Wide Flanges, Pipe, Angles and Channel sections,  I want to have the end user be able to bypass scrolling through all the Wide Flanges when Angles are desired, or channel and so on.

    Monday, December 8, 2014 9:06 PM


  • Hi NAMEngJuan,

    The most easiest way in my opinion is that to put an ActiveX command button in the worksheet, assign a button click event handler to it. Then in the event handler VBA function, you can loop through all the data rows you want, put the filtered data into the specified range.

    Or else you can try the methods in the MSDN documents, use PivotTable or ListObject to sort and filter the data you want:

    Filtering Data Programmatically in Excel 2007 by Using PivotTables

    Sorting and Filtering a Table with the List Object in Excel 2010

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 10, 2014 9:00 AM