Excel Drop Down Menu

Hello all,

I would like to have a drop down menu in excel which does the following

Date      B2
Incident      C2
Problem      D2
End to End Outage      E2
Service Outage      F2
Client      G2
Service      H2
Area      I2
Business Area      J2
Fact       K2
Cause       L2
Action      M2
Due Date      N2
Owner      O2
Root Cause Code      P2
Strategic Client Impact      Q2
Completed Date      R2
PM Owner      S2
Region      U2
IFC      V2

The name will be displayed in the dropdown but a would like the value to be a cell name... so if i select IFC from the drop down manu and call ActiveSheet.dropdown.Text the output would be V2
runnerjp2005Asked:
Who is Participating?
 
yuppyduConnect With a Mentor Commented:
I think this is it
Copy-of-Copy-of-help.xlsm
0
 
yuppyduCommented:
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?
0
 
runnerjp2005Author Commented:
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
yuppyduCommented:
Is this what you meant?
Code is in module2
ExcelDropDownMenu.xlsm
0
 
runnerjp2005Author Commented:
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
0
 
yuppyduCommented:
Here it is.
I do not get "Also how could i upp the value in vb code like ActiveSheet.dropdown.Text"
ExcelDropDownMenu.xlsm
0
 
yuppyduCommented:
Notice that I have renamed the two sheets in the VBA properties window. Sheet1 has become wksSheet1 and Sheet2 wksSheet2
0
 
runnerjp2005Author Commented:
So does wksSheet2.Range("valuelist").Offset(dblOffsetRow, 0) give me the value i want?
0
 
yuppyduCommented:
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
0
 
yuppyduCommented:
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.
0
 
runnerjp2005Author Commented:
Im trying to grap it all from the second page.
Copy-of-ExcelDropDownMenu.xlsm
0
 
yuppyduCommented:
0
 
yuppyduCommented:
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
0
 
runnerjp2005Author Commented:
How are you setting offsetrow & valuelist as when i copy the code it does not work?
0
 
yuppyduCommented:
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
0
 
runnerjp2005Author Commented:
I have name manager but not range :S
0
 
yuppyduCommented:
what is range :S?
0
 
yuppyduCommented:
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
0
 
runnerjp2005Author Commented:
Might be easyer to send you the sheet im working on
help.xlsm
0
 
yuppyduCommented:
Could you explain your sheets set-up and logic, please?
0
 
runnerjp2005Author Commented:
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

Open in new window


G represents the colum it needs to search
0
 
yuppyduCommented:
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

Open 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.
0
 
yuppyduCommented:
Have to go, be back in an hour
0
 
runnerjp2005Author Commented:
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
0
 
yuppyduCommented:
where does the column value go? in a cell in FCA or in the texbox next to the dropdown?
0
 
yuppyduCommented:
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
0
 
yuppyduCommented:
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
0
 
yuppyduCommented:
This is what I've done as explained above
Copy-of-help.xlsm
0
 
runnerjp2005Author Commented:
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 

Open 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!
0
 
runnerjp2005Author Commented:
Also when i hit submit the drop down and the submit button disapears
0
 
yuppyduCommented:
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
0
 
yuppyduCommented:
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
0
 
runnerjp2005Author Commented:
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
0
 
runnerjp2005Author Commented:
Does that make sence?
0
 
yuppyduCommented:
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?
0
 
runnerjp2005Author Commented:
Spot on!!!!
0
 
yuppyduCommented:
Try this if it is what you are looking for, I will do some cleanup in the code
Copy-of-Copy-of-help.xlsm
0
 
runnerjp2005Author Commented:
Nice!!! thats it!!

I get an error when I use the drop down menu?!
0
 
runnerjp2005Author Commented:
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??
0
 
yuppyduCommented:
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
0
 
runnerjp2005Author Commented:
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
0
 
yuppyduCommented:
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?
0
 
runnerjp2005Author Commented:
yes please
0
 
yuppyduCommented:
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?
0
 
runnerjp2005Author Commented:
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.
0
 
yuppyduCommented:
Thanks for the compliments, I'm glad I could be of help.

yuppydu
0
 
runnerjp2005Author Commented:
Ermm i have found an error.... when you type in text like CEMS.. it throws an error
0
 
runnerjp2005Author Commented:
Thats in the search facility btw
0
 
yuppyduCommented:
can you be more clear? I do not understand...
0
 
runnerjp2005Author Commented:
So in the code you provided above... in the search textbox type CEMS and click search.

It then comes up with an error
0
 
yuppyduCommented:
The search value you have sent me were numbers only. They can be text as well?
0
 
runnerjp2005Author Commented:
sorry yes... i just added numbers as was easy to type
0
 
yuppyduCommented:
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
0
 
runnerjp2005Author Commented:
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
0
 
runnerjp2005Author Commented:
Sory done it    If InStr(1, cell, dblSearch, 1) Then

should start using my own head :D

thanks again for help
0
 
yuppyduCommented:
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
.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.