Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    2

    Unanswered: dynamic drop down list from one or more files

    I have created an app that allows the user to specify one or more spreadsheets as a datasource using getopenfilename.

    Now I would like to pull names from the open spreadsheet(s) to populate a drop down in the app so the user can select data to import from the datasource. Any ideas?

  2. #2
    Join Date
    Jun 2006
    Posts
    103
    I have created an app that allows the user to specify one or more spreadsheets as a datasource using getopenfilename.

    Now I would like to pull names from the open spreadsheet(s) to populate a drop down in the app so the user can select data to import from the datasource. Any ideas?

    Hi,
    I am not sure what u r asking, for my understanding u r trying to get some data from another file, paste them on a sheet of your workbook (eg. a column of data) then u try to make a program to allow user to select only those data, m i right?

    Here r a piece of code which use validation using vba

    'this code is try to get how many row of records that you have in the column
    dim rowNum as String
    ActiveSheet.range("AF65536").Select
    Selection.End(xlUp).Select
    rowNum = ActiveCell.Row 'If column AF has 10 selections, rowNum = 10 in String

    ActiveSheet.range("AF2").Select
    'because i get data from other sheet, then i need to use this code where OtherStaff is the sheet name
    ActiveCell.FormulaR1C1 = "=OtherStaff!RC[-30]"
    Selection.AutoFill Destination:=range("AF2:AF" & rowNum), Type:=xlFillDefault

    'here is where i change the cell C7 validation to different selection.
    'careful, u need to make Cell C7 a validation list first, which u can find it on the excel menu->Data->Validation
    ActiveSheet.range("C7").Select
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=$AF$2:$AF$" & rowNum
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With


    Hope these helps.

  3. #3
    Join Date
    Jun 2006
    Posts
    2
    thanks for the assistance...I'll give it a try. Where I am running into confusion is the fact that the user is able to specify multiple workbooks so my code has to consider which workbook(s) the user specified in step 1, then search them for a specific field (say customer name) and return data on just those customers. I'd also like to allow flexibility so the user can search based on a different criteria (say zip code).

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by davemeyer1
    Where I am running into confusion is the fact that the user is able to specify multiple workbooks so my code has to consider which workbook(s) the user specified in step 1, then search them for a specific field (say customer name) and return data on just those customers. I'd also like to allow flexibility so the user can search based on a different criteria (say zip code).
    There are a couple ways of going about this. (1) if the user is specifying workbooks in a multi-select getopenfile dialog the results are returned in an Array. You can retain the array data in a Global Variable or by writing it to a location, text file or hidden workbook. (2) suppose you want to check all the open workbooks. You can use a loop to check each book.
    Code:
    For each wBook in Workbooks
     With wBook.Worksheets(1)
     If .Cells(1,1) = "Customer Name" Then
         strName = .Cells(2,1)
    ' Next Add the name to your list
    End If
    End With
    Next
    A good idea for something like this is to use a Named Range to contain the data in your workbooks. Then if you are search for data in several books by a named range you can still find it if the position varies from book to book. Although if you call a named range where the range does not exist you will produce an error. you must handle the error unless you are 100% sure the named range is always going to exist.

    If this is going to be a frequent process, you may want to consolidate this data into an index file or a database table where you can search all the data in a single location.
    ~

    Bill

Posting Permissions

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