none
Auto search type ahead drop down- Best Method? RRS feed

  • Question

  • Hi,

    In one of my project i have a drop down list of more than 150 values. I have used Activex Combo box to achieve this using listfill range (Range varies dynamically) and dropdown property to achieve this.

    I just want to know is there any alternative method to achieve this feature? (I have come across some other sites where they mentioned we can use vba code to achieve "Auto search type ahead drop down".

    Reason for looking for other method:

    I have a excel file with multiple drop down of this type.(Say for example student information) I need to add a macro(Add New Student Information) to copy and paste the existing drop down. This student information have multiple dropdowns and other fields which needs to be copied. The existing method of drop down makes the macro to  be complex.

    Here is the sequence req for adding new student info. (to be Achieved via macro)

    1) Need to copy paste all the student information content(Including all  the dropdowns)

    2) Rename the controls(Combo box)

    3) Need to add codes (Dropdown, list fill range) for newly added combox boxes.

    I would like to know any alternative method is there and how to proceed on that.

    Thursday, April 2, 2015 6:33 PM

Answers

  • Hi,

    Since you get the current location of the button, so after that you could resize the range through Range.Resize property, then copy the range through Range.Copy method.

    Regards

    Starain


    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, April 8, 2015 2:14 AM
    Moderator

All replies

  • Hi,

    For this requirement, I think you could refer to these steps below to accomplish.

    1. Get the range value of Combo box control through ListFillRange property.
    2. Copy the range through Range.Copy method
    3. Create a new Combo box control //
    4. Specify the ListFillRange property to the new Combo box control

    As far as I know the Combo box’s name need to be changed through the UI.

    Regards

    Starain


    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.

    Friday, April 3, 2015 7:17 AM
    Moderator
  • The following shows how to rename combobox through macro

    Click here

    My Aim to to create macro:

    Here is the sequence req for adding new student info. (to be Achieved via macro)

    1) Need to copy paste all the student information content(Including all  the dropdowns)

    2) Rename the controls(Combo box)

    3) Need to add codes (Dropdown, list fill range) for newly added combox boxes.

    Is there anyway to copy the combobox control along with their VBA codes?

    Sunday, April 5, 2015 7:50 AM
  • Hi,

    As I suggest that, you could copy the studentInfo range, then create the new Combo box control and specify the data with the new range data.

    For rename the combo box, your code is ok. We also could use this code below to change the name:

    ActiveSheet.Shapes.Range(Array("ComboBox2")).Name = "[NewName]"

    If you just want to copy the controls, you could use Selection.Copy to copy selected controls and use Worksheet.Paste method to paste controls.

    Regards

    Starain


    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.

    Tuesday, April 7, 2015 2:17 AM
    Moderator
  • Hi Starain Chen,

    Thanks for your reply.

    I have assigned a macro to particular button. I would like to run a macro to copy the contents from sheet. (i.e 3 rows below the location of that particular button and 8 rows above the location of the particular button.

    Worksheets("Sheet2").Range("P3") = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    Worksheets("sheet2").Range("P4") = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Column

    The above code will return the location of the "Button" in terms of  rows and column. (i.e button present in 54th row and 8th column... like that).

    Need help in copying 8 rows above the button and three rows below the button...

    Tuesday, April 7, 2015 11:42 AM
  • Hi,

    Since you get the current location of the button, so after that you could resize the range through Range.Resize property, then copy the range through Range.Copy method.

    Regards

    Starain


    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, April 8, 2015 2:14 AM
    Moderator