Excel Drop Down Menu

I'd love to help you out but I do not understand what you need to do. a2, b2, c2... are cell adresses or names? Those labels are next to the name, on top, below...? The dropdown is on a sheet or in a form?

Sorry I will try again....

So i would like to populate a dropdown with the following

Date
Incident
Problem
End to End Outage
Service Outage
Client
Service
Area
Business Area
Fact
Cause
Action
Due Date
Owner
Root Cause Code
Strategic Client Impact
Completed Date
PM Owner
Region
IFC
# of Strat Clients Impacted
Downtime Minutes
Internal Impact Only
Comments


This i can do but i would like the ID of the names to be different

So if i select Comments - I get the value B3

The drop down is on the sheet

Is this what you meant?
Code is in module2
ExcelDropDownMenu.xlsm

Thats right!!!

Thing is what if i have the values for the table in a different sheet??

Also how could i upp the value in vb code like ActiveSheet.dropdown.Text

Here it is.
I do not get "Also how could i upp the value in vb code like ActiveSheet.dropdown.Text"
ExcelDropDownMenu.xlsm

Notice that I have renamed the two sheets in the VBA properties window. Sheet1 has become wksSheet1 and Sheet2 wksSheet2

So does wksSheet2.Range("valuelist").Offset(dblOffsetRow, 0) give me the value i want?

yes, it gives you the value corresponding to the position of the label you've selected from the dropdown. Valuelist range name is one cell above the first cell with data and it reads the value for an offset equal to the value of the dropdown cell link

The output values in sheet2 start withc4, while the values on sheet1 start with c5, therefore for the same label you'll have two different output values.

Im trying to grap it all from the second page.
Copy-of-ExcelDropDownMenu.xlsm

Copy-of-ExcelDropDownMenu.xlsm

I've changed the input range with a dynamic range name. This allow you to add/remove values to your input range of the dropdown without having to modify the range
Copy-of-ExcelDropDownMenu.xlsm

How are you setting offsetrow & valuelist as when i copy the code it does not work?

offsetrow is the range name of the cell link of the dropdown
valuelist is the range name of the cell above the first record of the dropdown input range
Check the range names of the workbook under the formula tab>range manager

I have name manager but not range :S

what is range :S?

could you please give me the full picture of what we are doing? Are you transferring the code to another sheet? If you are doing so you have to carefully copy all the range names exactly as they are in the workbook I've sent you, otherwise it will not work

Might be easyer to send you the sheet im working on
help.xlsm

Could you explain your sheets set-up and logic, please?

So FCA tab will display some outputs.... (had to remove them for security)

a users fill in the form and picks from the drop down which column they want to search in.

so really what i want is the colume names displayed in the attached to show in the dropdown.

If that drop down is selected it will search the colum here (Under test in macros)

Sub Test()
       
       Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String

    Set wb1 = ThisWorkbook
   ' Application.Workbooks.Open("C:\Sample.xlsx")
    Set ws1 = wb1.Worksheets("FCA")

    strSearch = ActiveSheet.inputname.Text



    With ws1

        '~~> Remove any filters
        .AutoFilterMode = False

        lRow = .Range("G" & .Rows.Count).End(xlUp).Row

        With .Range("G1:l" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With

    '~~> Destination File
    Set ws2 = wb1.Worksheets("Output")

    With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("G1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row + 1
        Else
            lRow = 1
        End If

        copyFrom.Copy .Rows(lRow)
    End With


End Sub

Select allOpen in new window

G represents the colum it needs to search

Sub Test()
       
       Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String

    Set wb1 = ThisWorkbook
   ' Application.Workbooks.Open("C:\Sample.xlsx")
    Set ws1 = wb1.Worksheets("FCA")

    strSearch = ActiveSheet.inputname.Text 'what is this suppose to do?



    With ws1

        '~~> Remove any filters
        .AutoFilterMode = False

        lRow = .Range("G" & .Rows.Count).End(xlUp).Row

        With .Range("G1:l" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With

    '~~> Destination File
    Set ws2 = wb1.Worksheets("Output")

    With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("G1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row + 1
        Else
            lRow = 1
        End If

        copyFrom.Copy .Rows(lRow)
    End With


End Sub

Select allOpen in new window

strSearch... I do not understand what is suppose to do. "a users fill in the form", which form? There are no forms in the VBA and I only see empty sheets. It is quite hard t understand the logic you need to implement. Please be more specific.

Have to go, be back in an hour

All i need is to get the values in row 2 ino the dropdown menu and the values will be the Colume they are in

where does the column value go? in a cell in FCA or in the texbox next to the dropdown?

It sounds quite simple but I need to know:

Values that has to appear in the dropdown (indicate cell range and worksheet)
Value that the dropdown selection has to extract (indicate cell range and worksheet)
Address where the extracted value have to be placed (indicate cell range and worksheet)
Thanks

At the moment I am:

Filling the dropdown with values in range b2:b25 of Coding Sheet
Extracting the corresponding value in range c2:c25 of the same sheet
Putting the extracted value in the textbox in FCA sheet

This is what I've done as explained above
Copy-of-help.xlsm

Sorry was away yesterday -

What i need to do is display the code in here:

Sub Test()
       
       Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String

    Set wb1 = ThisWorkbook
   ' Application.Workbooks.Open("C:\Sample.xlsx")
    Set ws1 = wb1.Worksheets("FCA")

    strSearch = ActiveSheet.inputname.Text 'what is this suppose to do?



    With ws1

        '~~> Remove any filters
        .AutoFilterMode = False

        lRow = .Range("G" & .Rows.Count).End(xlUp).Row

        With .Range("G1:l" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With

    '~~> Destination File
    Set ws2 = wb1.Worksheets("Output")

    With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("G1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row + 1
        Else
            lRow = 1
        End If

        copyFrom.Copy .Rows(lRow)
    End With


End Sub 

Select allOpen in new window

so here        lRow = .Range("G" & .Rows.Count).End(xlUp).Row

        With .Range("G1:l" & lRow)

the G is the colum name so i would like to display what is currently in the textbox here!

Also when i hit submit the drop down and the submit button disapears

The dropdown and the texbox disappear because you satrt in G1 as With .Range("G1:l" & lRow)

Modify that with G2 and nothing will disappear

Can you load the excel file with the data as you would like to see it, please? I do not understand what you are trying to achieve with your code

Ok so what i need to do is this:

I search for some txt withing on of the colums so in this case i can to search the client column for 14.

I select the column i want to search from the drop down menu and what i wanna search in the text box.

Clicking submit will then copy anything with 14 in the client colum over to the output tab!
Copy-of-Copy-of-help.xlsm

Does that make sence?

I think it does.... When you mean anything with 14 do you mean that it searches for the value 14 in the Incident column and if it finds it copies the entire row on the first available row in the output sheet?

Spot on!!!!

Try this if it is what you are looking for, I will do some cleanup in the code
Copy-of-Copy-of-help.xlsm

Nice!!! thats it!!

I get an error when I use the drop down menu?!

Fixed it.

I have question.... would you know how to search external spreadsheets so i dont have to copy and paste all he data into this one all the time??

Sorry I had changed a range name without adapting the code.

I cleaned up the code and now you only have 1 module.
Copy-of-Copy-of-help.xlsm

Also my old code used to reemove the old search data from the output file... any idea how i can get it back... cant rember which line of code does it

well all your code is in your original file, but I have not seen any code which clears the data in the output sheet.
I'm shooting out know, I can give it a look later on. If I understand correctly you want a command which clears all data in the output file?

yes please

Ok, the command has to be a separate command or the output sheet has to be cleared every time you click the Submit button? If it is linked to the submit button you will only get one line in output sheet as the code is written at the moment. Can you have duplicate records in you data?

Thats brilliant thanks :)

Thank you for sticking with it to get an answer! I do have anouther issue regaridng reading external workbooks but will post that again as you have spent alo of time on this issue.

Thanks for the compliments, I'm glad I could be of help.

yuppydu

Ermm i have found an error.... when you type in text like CEMS.. it throws an error

Thats in the search facility btw

can you be more clear? I do not understand...

So in the code you provided above... in the search textbox type CEMS and click search.

It then comes up with an error

The search value you have sent me were numbers only. They can be text as well?

sorry yes... i just added numbers as was easy to type

Copy this over the Sub Test() you have

Sub Test()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim lRow As Long    '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String
    Dim rngStartRange As Excel.Range
    Dim rngEndRange As Excel.Range

    ' Application.Workbooks.Open("C:\Sample.xlsx")

    strSearch = CStr(wksFca.TextBox1.Value)

    lRow = wksFca.Range("G" & wksFca.Rows.Count).End(xlUp).Row
   
    Set rngStartRange = wksFca.Range(gszSTART_CELL).Offset(0, wksCoding.Range(gszOFFSET_COLUMN).Value - 1)
    Set rngEndRange = wksFca.Range(gszSTART_CELL).Offset(lRow - 2, wksCoding.Range(gszOFFSET_COLUMN).Value - 1)
   
    wksOutput.Range("A2:a" & wksCoding.Range(gszNUMBER_OF_ENTRIES).Value + 1).EntireRow.Delete

    For Each cell In Range(rngStartRange, rngEndRange)
        If cell = strSearch Then
        wksCoding.Range(gszNUMBER_OF_ENTRIES).Calculate
            cell.EntireRow.Copy wksOutput.Range(gszSTART_CELL).Offset(wksCoding.Range(gszNUMBER_OF_ENTRIES).Value, 0)
            Application.CutCopyMode = False
            'Exit For <--- uncomment this if you cannot have multiple outputs
        End If
    Next cell

End Sub

Thats ok i fixed it to    Dim dblSearch As String

Also 1 quick one ....

 If cell = dblSearch Then - this means that it has to be exactly that text to show.... would i be possible to add if cell contain dblsearch


So if i have "a b c 1 2 3" in the cell & im searcing if that cell contain c it will show me the result

Sory done it    If InStr(1, cell, dblSearch, 1) Then

should start using my own head :D

thanks again for help

I would advise you to keep a rule in your variable naming convention, therefore I would rename dblSearch to strSearch if it becomes a string. This will help you debug your code in the future.
Remember to substitute it throughout your code.
It does not matter what convention you use, as long as it is consistent.
I use:
dbl double
str string
lng long
int integer
b boolean
cur currency
rng range
.....