none
create drop down list for each excell cell using excel vba

    Question

  • how can i create dropw down list for each cell in excel spread sheet  plz can any one help me in this
    Ramya
    Monday, June 30, 2008 10:17 AM

Answers

  • Hi,

    You can use the following macro code to create in cell drop down for a cell with valid values in any other range:

    Dim objCell As Range 
    Dim objDataRangeStart As Range 
    Dim objDataRangeEnd As Range 
    ' >> Set The Range For Valid Data 
    Set objDataRangeStart = ActiveSheet.Cells(1, 3) 
    Set objDataRangeEnd = ActiveSheet.Cells(6, 3) 
    ' >> Set Validation On Required Cell  
    Set objCell = ActiveSheet.Cells(8, 4) 
    With objCell.Validation 
      .Delete 
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & objDataRangeStart.Address & ":" & objDataRangeEnd.Address 
      .IgnoreBlank = True 
      .InCellDropdown = True 
      .ErrorTitle = "Warning" 
      .ErrorMessage = "Please select a value from the list available in the selected cell." 
      .ShowError = True 
    End With 

    You can also play around with IgnoreBlank, InCellDropDown and ShowError flags to modify the behavior.

    Regards,

    Shobhit Deep
    Monday, June 30, 2008 12:48 PM