Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating and copying data into new sheet
Sorry, can I ask you one last question. I have entered a msgbox at the start of the procedure, at which poin the user is asked to enter the name of the city they wish to look at eg: New York. I have got this working, and when I enter the correct city name in th box it outputs only data relating to that city. However I want it to full proof. Is there a way to allow the user t pick from all available cities in the msgbox ?! THanks a million. Scott -- scottwilson ----------------------------------------------------------------------- scottwilsonx's Profile: http://www.excelforum.com/member.php...fo&userid=1112 View this thread: http://www.excelforum.com/showthread.php?threadid=26382 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating and copying data into new sheet
Scott, it seems they have left you with your last question unanswered, so I
will give it a stab. First scrap the msgbox. You will need a listbox and you will need to populate the listbox with a unique list of available cities.From there you will need to pass the city chosen to your code. Create a listbox from the Control Toolbox menu and place it right of column E. Now you need a unique list of cities from column E. The easiest way is to select cloumn E and use Data/Filter/Advanced and copy the list to the sheet some where way out of sight. the code would look like this: Range("E:E").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("CA5000"), Unique:=True That will put a list of each city way off the visible sheet for the listbox. Now tell the listbox where the list is: Dim rwcnt rwcnt = Range("CA5000").CurrentRegion.Rows.Count + 5000 - 1 Sheet1.ListBox1.ListFillRange = "CA5001:CA" & rwcnt We add 5000 to the number of rows with cities because that is the row number we pasted to. Then we subtract 1 so we do not include the header in the list. Then we tell the ListFillRange to start 1 row below the header (CA5001). To use the value selected from the list we can use another variable to substitute in Bernie's line If myCell(1, 5).Value = "New York" Then Dim city as String city = Sheet1.ListBox1.Value If myCell(1, 5).Value = city Then You will have to assemble the code in the correct sequence as you need it used. Cheers...Mike F "scottwilsonx" wrote in message ... Sorry, can I ask you one last question. I have entered a msgbox at the start of the procedure, at which point the user is asked to enter the name of the city they wish to look at, eg: New York. I have got this working, and when I enter the correct city name in the box it outputs only data relating to that city. However I want it to full proof. Is there a way to allow the user to pick from all available cities in the msgbox ?! THanks a million. Scott. -- scottwilsonx ------------------------------------------------------------------------ scottwilsonx's Profile: http://www.excelforum.com/member.php...o&userid=11128 View this thread: http://www.excelforum.com/showthread...hreadid=263823 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
Calculating and copying data into new sheet | Excel Programming | |||
Calculating and copying data into new sheet | Excel Programming | |||
Calculating and copying data into new sheet | Excel Programming | |||
Calculating and copying data into new sheet | Excel Programming |