Solved

Excel Drop Down Menu

Posted on 2013-11-28
56
426 Views
Last Modified: 2013-12-02
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
0
Comment
Question by:runnerjp2005
  • 31
  • 25
56 Comments
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683215
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
 

Author Comment

by:runnerjp2005
ID: 39683249
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683291
Is this what you meant?
Code is in module2
ExcelDropDownMenu.xlsm
0
 

Author Comment

by:runnerjp2005
ID: 39683327
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683477
Here it is.
I do not get "Also how could i upp the value in vb code like ActiveSheet.dropdown.Text"
ExcelDropDownMenu.xlsm
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683481
Notice that I have renamed the two sheets in the VBA properties window. Sheet1 has become wksSheet1 and Sheet2 wksSheet2
0
 

Author Comment

by:runnerjp2005
ID: 39683509
So does wksSheet2.Range("valuelist").Offset(dblOffsetRow, 0) give me the value i want?
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683528
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683537
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
 

Author Comment

by:runnerjp2005
ID: 39683592
Im trying to grap it all from the second page.
Copy-of-ExcelDropDownMenu.xlsm
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683633
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683644
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
 

Author Comment

by:runnerjp2005
ID: 39683652
How are you setting offsetrow & valuelist as when i copy the code it does not work?
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683661
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
 

Author Comment

by:runnerjp2005
ID: 39683693
I have name manager but not range :S
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683696
what is range :S?
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683712
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
 

Author Comment

by:runnerjp2005
ID: 39683716
Might be easyer to send you the sheet im working on
help.xlsm
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683725
Could you explain your sheets set-up and logic, please?
0
 

Author Comment

by:runnerjp2005
ID: 39683738
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683773
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683805
Have to go, be back in an hour
0
 

Author Comment

by:runnerjp2005
ID: 39683853
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683867
where does the column value go? in a cell in FCA or in the texbox next to the dropdown?
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683909
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683943
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39683959
This is what I've done as explained above
Copy-of-help.xlsm
0
 

Author Comment

by:runnerjp2005
ID: 39684860
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:runnerjp2005
ID: 39684862
Also when i hit submit the drop down and the submit button disapears
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39684884
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39684897
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
 

Author Comment

by:runnerjp2005
ID: 39684905
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
 

Author Comment

by:runnerjp2005
ID: 39685061
Does that make sence?
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39685067
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
 

Author Comment

by:runnerjp2005
ID: 39685080
Spot on!!!!
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39685097
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
 

Author Comment

by:runnerjp2005
ID: 39685107
Nice!!! thats it!!

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

Author Comment

by:runnerjp2005
ID: 39685120
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39685125
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
 

Author Comment

by:runnerjp2005
ID: 39685153
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39685241
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
 

Author Comment

by:runnerjp2005
ID: 39685243
yes please
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39685412
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
 
LVL 4

Accepted Solution

by:
yuppydu earned 500 total points
ID: 39685750
I think this is it
Copy-of-Copy-of-help.xlsm
0
 

Author Closing Comment

by:runnerjp2005
ID: 39689361
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
 
LVL 4

Expert Comment

by:yuppydu
ID: 39689382
Thanks for the compliments, I'm glad I could be of help.

yuppydu
0
 

Author Comment

by:runnerjp2005
ID: 39689482
Ermm i have found an error.... when you type in text like CEMS.. it throws an error
0
 

Author Comment

by:runnerjp2005
ID: 39689494
Thats in the search facility btw
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39689500
can you be more clear? I do not understand...
0
 

Author Comment

by:runnerjp2005
ID: 39689509
So in the code you provided above... in the search textbox type CEMS and click search.

It then comes up with an error
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39689532
The search value you have sent me were numbers only. They can be text as well?
0
 

Author Comment

by:runnerjp2005
ID: 39689543
sorry yes... i just added numbers as was easy to type
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39689549
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
 

Author Comment

by:runnerjp2005
ID: 39689552
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
 

Author Comment

by:runnerjp2005
ID: 39689560
Sory done it    If InStr(1, cell, dblSearch, 1) Then

should start using my own head :D

thanks again for help
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39689607
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Outlook Free & Paid Tools
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now