Drop Down Listing Macro
May 26, 2009
Ok well here goes.
I have an excel book with 2 sheets.
Sheet 1 = dropdown
Sheet 2 = Customer list
What i need is a to be able to add to the customer list on sheet 2 and in the process the customer list to be sorted into alphabetical order.
In addition I need the new customer to be added into the dropdown list on sheet 1.
I know i can extend the list in sheet 2 with blanks so that there is space in the drop down list, but not knowing the number of customers at any one time I have no way of knowing how many blanks to add.
View 12 Replies
ADVERTISEMENT
Dec 18, 2007
I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below). I tried to use the InputBox command but needed to actually type in the name.
ActiveSheet.Unprotect
Range("C27:D27").Select
'ActiveCell.FormulaR1C1 = "John Smith"
' Application.CutCopyMode = False
' ActiveSheet.Paste
'Range("c27:d27") = InputBox("Enter value")
View 4 Replies
View Related
Dec 4, 2008
When someone makes ANY selection from my drop-down list, I would like my macro to run so that the user can continue to fill in the rest of the form. My macro simply unhides some rows as below:
Sub UnhideForm()
Application.EnableCancelKey = xlDisabled
ActiveSheet.Unprotect Password:="Testing"
Rows("15:112").Select
Selection.EntireRow.Hidden = False
Range("Name").Select
ActiveSheet.Protect Password:="edit"
End Sub
Here's some info you might need:My sheet is called "Form"
My macro is called "UnhideForm"
My drop-down list is in the array called "TypeOfMoveCell"
I have 5 dropdown options (which includes an option called "Please select from list...") These options are located in cells BW145:BW148.
I would also like the macro to re-hide the data rows if someone deletes the value in the drop-down list, or if someone re-selects "Please select from list..." option.
View 9 Replies
View Related
Jun 21, 2006
A1 is a drop-down list, created from Data> Validation>List, which lists 3 different words (Text1, Text2, Text3). I have recorded 3 macros (Macro1, Macro2, Macro3). Here's what I want to happen:
When Text1 is selected in A1, I want Macro1 to run (same for Text2/Macro2 and Text3/Macro3). I want the user to be able to change this value as many times as they wish and have the corresponding Macro run each time. I've tried creating the appropriate code in Editor using other threads on this forum, but I can't seem to figure it out.
View 8 Replies
View Related
Mar 6, 2007
I am trying to figure out how to activate a macro from each item in a drop down list. I also need the macro to recognize which item is chosen and to use that items data (say cells C3:C11) to create its graph. I know how to create an individual macro that will create a graph for the item but not a general macro that can be used to graph all items just discerning the difference in where the data is (which item to graph). Is this possible????if so please include code (amatuer programmer).
View 9 Replies
View Related
Mar 30, 2009
I have a macro which output depence on the variable input. Input is represented by drop-down list. Before, I was choosing input from drop-down, then pressing the button my macro was running. Now, I'm thinking to if it's possible to exclude a spare operation from button pressing, just by clicking from drop-down.
My drop-down list is created through "Data->Validation->List box"
View 9 Replies
View Related
Apr 16, 2009
I have several pre-configured macros that I would like to choose from using a drop-down menu. I know this is possible within the excel built-in ribbon, but I need to have it only in the spreadsheet. The macro will be importing a csv file. I have several different csv files that I need to be able to choose from and a drop-down list seems to be the best choice. The other option could be to have the macro prompt me to select the csv file of choice. Below is a sample of the macro that I would be using. Is this possible?
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;P:A Harvest of HealthImportVendorsA-N.txt", Destination:=Range( _
"$N$1"))
.Name = "A-N_7"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
View 9 Replies
View Related
Oct 19, 2006
I'm able to create a simple User form Drop-down list that references a cell range list. ( as a test for myself) I have 4 separate macros I would like to run individually. Rather than create 4 separate buttons, I'd like to be able to select each one via Drop-Down list.
View 4 Replies
View Related
Oct 13, 2009
I would like to drop down the cells in B4 and D4 so that if throws up a result in D6 which looks at Sheet 2 and throws back that score? So it will look for Neil in Column A and October in Row 1 and throw back that figure.
View 3 Replies
View Related
Aug 25, 2008
I have found a macro that should work with a change of a specific cell.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub
However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value). The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:
Sub Button1()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "...................
View 8 Replies
View Related
Jul 23, 2007
I am using INDIRECT to create a second dynamic drop down list (L1) that is based on the cell contents of a first drop down list (I1). I want to create a macro that will blank the 2nd drop down list ONLY when the choice in the first list is CHANGED. I have the following, but it blanks the second list as soon as the first list is clicked on, rather than when a change is made:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Range("I1"), Target) Is Nothing Then Range("L1").ClearContents
End Sub
View 2 Replies
View Related
May 15, 2008
I have a worksheet with 7 macros each run from a seperate button. I would like to tidy up the sheet by having a combo box containing a description of each macro and one button to run the macro currently shown in the box.
View 4 Replies
View Related
Nov 22, 2012
I'm tracking 50 food items (in column A) for 40 homes (columns B-AO) where each home gets none or varying quantities for each food item (ex: apples 7 for home B, 0 for homes C-L, 19 for home M, etc.). I can get grand totals for each item for all homes, but need to also list how many for each home receiving apples. Ex: 4 homes get apples (4, 7, 19, 1 respectively). SO I want my total sheet to have a column for each food item with the grand total "31" and the second column to show 4 + 7 + 19 + 1. The quantities change frequently so I need a formula I can use for each food item. Also I don't want "zeroes" included. What formula would I use to do this?
View 3 Replies
View Related
Jun 17, 2009
I have been given a worksheet which has 5000 rows spread along 13 columns.
There are about 200 cells that I am interested in from this sheet, these cells being the subtotals of the cells above them.
I have extracted the individual cells containing the subtotal values to a new, empty column but they are spread down this column with blank cells in between.
Is there a function / formula to get rid of the 4800 blank cells so that the data I need is compressed into a stack of 200 cells all filled with data?
View 14 Replies
View Related
Oct 10, 2008
I have found a lot of posts which show me how to list a directory tree in an Excel file, usually using a macro. I'm just uninitiated with VBA / VBS and simply don't have the time to figure it out now (it really isn't part of my job description... I'm rather doing a favor here).
I need to be able to re-generate an excel-based list of clients at will. The client names are subdirectories of different case types. There aren't tons of case types, right now maybe 20 but I need to be able to add case types without breaking the script.
case_typeslast,_first_name........
View 5 Replies
View Related
Jan 23, 2009
Need way to list the active shortcut keys? I know there are manyy lists of the desfault shortcuts, buit I'm trying to generate a list of the curreently active shortcut keys & what macros they tie to. I have a vague recollection that someone had a VBA means of doing this, but I cannot find it in the forums I've searched.
View 2 Replies
View Related
Jan 17, 2008
I have tried to create a macro which prints the results of each heading in a drop down box. The listfill range is from B2 to B5 and the linked cell is B1. When manually recording the macro, i copy the heading from B2 into B1, the drop down list updates which in turn updates the cells which are linked to that, i then print out the page. The same happens when i copy-paste cells B3, B4, & B5 into B1. I then return finish the macro by copying B2 into B1.
But when i run the macro, the copy paste works but the drown down list doesnt update, therefore it doesnt update my linked cells so i acutally end up printing 5 indentical pages with the same info.
Is there anything i can do to get around this?
Macro listed below:
Sub copypasteprint()
Range("B2").Select
Selection.Copy
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range("E5").Select...........
View 9 Replies
View Related
May 27, 2009
I am trying to accomplish is to display a dropdown or combobox with a list of choices. I want the backcolor to be shaded light green to match instructional text that appears in the cell above. That I have working with the selections appearing. Once a choice is made I want a separate procedure to run that will somehow know which choice was made.
I can't put the code within the module for this particular sheet as it is dynamically recreated each time the data is refreshed and the code will disappear. This may seem to be an odd practice but this is how 25+ workbooks are coded within this system for my employer's customer and it is a required practice.
View 9 Replies
View Related
Jul 25, 2014
I have created a simple account list with - date - detail - category - amount.
How can I automatically list each transaction in date order by category on other pages. Each category is represented by a letter - e.g. 'T' for travel. In this example I want to list all travel transactions in a separate list in date order on another page for easy printing.
I have made a Summary page for each category but cannot work out how to make a detailed list for each category.
View 13 Replies
View Related
Feb 7, 2014
I have a workbook of appointments, I need a formula to list duplicate appointments and display them using 2 criteria (date and time).
See attached workbook : duplicate entries.xlsx‎
View 7 Replies
View Related
Feb 28, 2014
To establish the order for on duty personnel every month, I need to find a proper formula.
I have attached the workbook.
So, when I pick out a month from a drop list in cell A1, in column B returns (based on a formula) net work days of that month.
In range H:T, I have a table with the personnel names (in the header) and the individual holidays.
What I want is to have a formula in column C, that returns the name of the first available person (not on holiday in that day), in exactly the same order as it is in the table's header.
Attached File : ON DUTY.xlsm‎
View 5 Replies
View Related
Oct 28, 2008
I have one that could be difficult. I have a database with names and corresponding numbers. I am trying to develop a report in which one could choose a name from a list (for example, using validation) and the report pulls out all the corresponding numbers for that name. Not the amount of numbers but the actual list of those that are linked to that name.
View 9 Replies
View Related
Apr 15, 2014
I don't know if this will require a macro or maybe a function of excel, but I have a large listing about 1000 row in spreadsheet. Each row correspond to a unique customer. But I will have to extract only the customer that I am looking for, which is about 30 of the 1000. The cell A1 is the customer # which will be use as reference, so I have about 30 customer # to pull out from that listing. Which will be the greatest method to do so?
Example:
A1 44334 ERIK JOHNSON 60$
A2 34555 KARIM EDWARD 50$
A3 32555 EDWARD CHRIS 65$
A4 55666 STEPH LAWRAW 45$
...
I want to pull 44334 and 55666 the whole row.
View 8 Replies
View Related
May 15, 2009
Column B on worksheet3 has a list of dates on it. Column C states set reminder. Column D states how many days before reminder i.e. 3, 7, 10, 14. If the reminder is set to on. Can VBA code be run when the Workbook (or worksheet) that checks this criteria then a pops up is displayed outlining the event (say Cell A)?
Also is it then possible to have on the pop up - Go to this event (i.e. row of data?)
View 11 Replies
View Related
Jul 4, 2012
I have a form with 4 TextBoxe's "TexBox1,2,3 etc" and a ListBox1 were the whole idea is that you enter a value in TexBox1 and the VBA code looks for any and all items on your data "respective worksheet" and lists any/all data that it finds "corresponding to the value in TextBox 1" in the ListBox1, just like a database of sorts. Also note the other TextBoxe's 2,3,4 have info in them as well corresponding to the value you put in TextBox1 using the offset function.
What I mean by this is that when ever I enter data in TextBox 1 or 2 or 3 or 4 that the results will be listed in the ListBox1. The reason I need this is, if the info I require for TextBox1 isn't available I can search for the info using the other criteria in the other TextBoxes.
When the code finds entries that relate to TextBox1 it comes up with a message window with the number of entries it found... just in case you were wondering about the code at the bottom of the cmdFind method.
Code:
Dim MyData As Range
Dim c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range
[Code]...
View 5 Replies
View Related
Dec 3, 2013
I have a datasheet with 6000+ entries. One column has lists of companies, of which there are around 300 and their are entered into this column multiple times. I organized and identified the largest ones but a work file of 6000 is making it a long process to find every single company listed, paste their name onto a separate table to analyses. Is there a formula to take all the different company names and arrange them in a list OR list them just once in a column next to it? I will have to do this again and the analysis of the companies is the most important.but I find I need to spend all evening steadily combing the haystack for needles!
View 4 Replies
View Related
Nov 30, 2006
Is there list anyway of all the error numbers and their descriptions?
View 2 Replies
View Related
Sep 25, 2008
I have a Master sheet that lists people Name, address, City, Phone Number etc…
Now I have separate pages for each individual and was wondering if there ways away if I update or change there information on the master page that is would update there in individual pages as well instead of me doing this numerous times manually.
View 10 Replies
View Related
Mar 3, 2010
Is there a way than I can take a list of data and create a new list but only listing each value once if there is a duplicate value within that list?
Original List
=========
Apple
Pear
Orange
Orange
Pear
Apple
Peach
Plum
Pear
New List
======
Apple
Pear
Orange
Plum
Peach
View 9 Replies
View Related
Apr 16, 2004
I am attempting compile a list of files from my computer into a spreadsheet but I am not sure how to get the list in the first place--I have searched many, many websites and everyone directed me here...
View 9 Replies
View Related
