Free Microsoft Excel 2013 Quick Reference

Run Macro after value change from drop down list

Hi

I have read several related threads but can't find the answer.

I want to trigger a macro when I select a value from a scroll down list in a cell.

Let's say A1 has a validation list of 5 numbers:

1
2
3
4
5

When I select a value from the scroll down list it will not trigger the macro to run. If I double click on the cell *after* I have selected from the drop-down list, and *then* hit enter, the macro will run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Not IsEmpty(Target) Then
Macro1
End If
End If
End Sub

But that sort of defeats the point of using the scroll down list.

Any ideas would be appreciated.
Thanks

tx

btw, I have had some trouble with the search function for this board. anybody else having the same trouble?


Does anyone know how to setup a macro so that it only runs IF there is a certain value in a drop down list? For example, I'm populating a list of 30,000 lines and then I'm running a vlookup on those lines to see whose team belongs to each line. IF there is are certain teams listed in the drop down list I would like their data copied and moved to another tab and then removed from the main data sheet. These teams are not always listed on the drop down list so the macro would only have to run IF they are listed. I put some code below but i don't know how to add an IF, Then statement to the macro. Does anyone have any ideas?


	VB:
	
 Sort_Out_Data() 
     
    Range("A2").Select 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    Selection.AutoFilter Field:=29, Criteria1:="=team1", Operator:=xlOr, _ 
    Criteria2:="=team2" 
    Selection.Copy 
    Sheets("team drop outs").Select 
    Range("A2").Select 
    ActiveSheet.Paste 
    Range("A2:IV65536").Select 
    Selection.Interior.ColorIndex = xlNone 
    Sheets("Data").Select 
    Application.CutCopyMode = False 
    Selection.EntireRow.Delete 
    ActiveSheet.ShowAllData 
    Range("A1").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I came across a thread which was very helpful in helping me to create my first drop down list linked to a chart. I have succeeded in getting the title and legend of the chart to update with changes in selection of the drop down list. However, I have not succeeded in setting the macro up correctly (see error below) to select the data for the "StoreNo" string which is selected from the drop down list. I have attached my spreadsheet as well so you can see what I am trying to do.

I am new with creating macros and would appreciate your help in correcting the macro to get this up and running.ANNUAL USAGE BY STORE 2009 - 2011 - DRAFT WITH ERRORS FOR HELP.xlsx

Also, I am very interested in learning to write macros for excel to assist in the many analysis reports our customers request so I would appreciate direction for a good curriculm (online is my preference but a book would work as well).

Sub Macro4()
'
' Macro4 Macro
'

'
ActiveSheet.ChartObjects("Chart 3").Activate
End Sub
Sub Macro1()
'
' Macro1

Dim StoreNo As String
Dim FindStore As Range

StoreNo = Range("H1").Value

On Error Resume Next
With Sheets("CHART BY STORE")
Set FindStore = .Columns(1).Find(What:=StoreNo, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

On Error GoTo 0

If FindStore Is Nothing Then
MsgBox "Team Not Found"
Else
Application.Goto FindStore, True

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Range("B" & ActiveCell.Row, "D" & ActiveCell.Row)
ActiveChart.SeriesCollection(1).Name = "=CHART BY STORE!$A$" & ActiveCell.Row
ActiveChart.SeriesCollection(1).XValues = "=CHART BY STORE!$B$2:$D$2"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
Range("H1").Select

End If
End With
End Sub

How to run different macro from drop-down list ???

Hi,

Attached is a sample in which Sheet1 contains list in which the ColumnF contains a Drop Down box which have values from Sheet2. Now when a value is selected from Drop Down List the Place,Mandal and School ID columns in Sheet1 are to be filled from the corresponding Place,Mandal and School ID columns in Sheet2 and the row in sheet2 has to be deleted.Next the original Place,Mandal and School ID columns in Sheet1 are to be copied to Sheet2 and sorted first by mandal and then by Place.Now the available Drop Down List in Sheet1 after the last operation should be the latest one with Place selected removed and Place copied included.

Hope I explained what I am required.

Thank you in advance.

I have a drop down list cretaed via Data Validation List. However, the cell can be left blank if delete id pressed. can a tip be provided (be a macro or else) so that a value will always be at that cell.

Say cell A1 contains the drop down list with values A,B,C,D I want that "A" should always be at A1 If the user selects other value then that value should appear say user select B then B should appear. If delete is pressed it should not be blank rather show A.

Hi

I need to perform a Text to Column conversion from a drop down list, but I
also need the extracted value to be updated if the value in the list is
changed. eg:

Drop down list has 2 values:

1. 4x16K
2. 8x8K

If the user selects 1 I can easily extract out to another cell the value 1
using Text to columns, however if the user then changes the choice to 2 the
text to columns extraction is not updated to 2.

Is there a way to update changes in the original cell using text to columns?

Or is there another way I can assign a value to a drop down menu choice in a
different cell while having text displayed in the drop down menu?

Thanks for any help,

Kurgan.

Hi folks,

I'm trying to make a macro run to hide a certain column based on the value of a drop down list in Excel 2007. So far, my code looks like this:
Sub TDDeal()
'
' TDDeal Macro
'

'
    ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.ColumnWidth = 0
End Sub
but doesn't run when the appropriate cell value (E11) = TD, can someone help me here?

Does anyone know if we can change the font size of values in a drop down list
created through the Data Validation function?

Hi

I am using the following code to enable multi select from drop down
lists in XL2003, but have a group of users requiring this feature in
XL97...

Public InActivity As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If InActivity Then Exit Sub
InActivity = True
On Error GoTo NonValidatedCell
If Selection.Validation.Type = xlValidateList Then
ColAbs = Target.Column
If ColAbs 3 Then GoTo NonValidatedCell
RowAbs = Target.Row
If Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value = "Delete
Contents" Then
TotalString = ""
Else
Application.Undo
TotalString = Sheets("Recreation_Activity").Cells(RowAbs,
ColAbs).Value & ", "
Application.Undo
TotalString = TotalString &
Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value
End If
If Left(TotalString, 1) = "," Then TotalString = Mid(TotalString, 3)
Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value =
TotalString
End If
InActivity = False

Exit Sub
NonValidatedCell:
InActivity = False

End Sub

The spreadsheet opens in XL97 without error but you can only select
one item from the list...
Selecting a second item just overwrites the first..

Any suggestions??????

How do I set default values in a drop down list. I currently have a macro
which resets the sheet (resets the order number and customer cell for
example) but the options of the drop down menu cannot be reset this way. Can
anyone help?

Ok what I am trying to do is lookup a value in a table (kinda like one below but alot bigger). What I plan on doing is creating a drop down list for the rows that show "A, B, C etc) and then another drop down for (AA, BB, CC etc).

So lets say the 2 drop-down list are set to C and BB
I want to be able to fill another cell with the value -134

I would use If statements but like I said the table would be alot bigger than example. If possible I would rather not use VBA due to work security settings.

a b c
AA -110 -110 -129
BB -115 -115 -134
CC -117 -133 -136

Anyone got any good ideas?

Thanks in advance!

I have a list of tables that have a similar structure. I would like to make a new (single) table with a header that contains a drop down list of the options. In my example, sheet 1 contains Northeast, Southeast and Gulfcoast followed by a table of values, respectively. I would like to reduce this to a single table, as in sheet 2, where the drop down includes Northeast, Southeast and Gulfcoast and when the corresponding location is chosen, the values update. I have no trouble with vlookup or creating the drop down list, but I cannot seem to make an array of values update based on changing the drop down list value. Many thanks in advance,

I am trying to create a data input page where I select the relevant information from drop down lists (which I have already set up) hit ENTER and it will automatically create an entry on a list on a separate page
for example:

COMPANY: NAME: DATE: TIME IN: TIME OUT: MAKE ENTRY

List generated automatically on separate page

COMPANY NAME DATE TIME IN TIME OUT
Generic Bob 22/03/2020 8:20 14:30

Hope that makes sense

Thanks

Also see the attachment if it worked

When the (right-click) "Pick From Drop-Down List..." command is used in Excel
to facilitate key-entry, it only works for contiguous entries in cells in the
column above the input cell. blank cells in between break the link.

Is there a way to designate a named range of terms as the basis for the
"drop-down" list?

Microsoft,this should be a standard option!

I have created a list in cells A2 thru A13. A1 has text 'Month & Yr'
entered. When I right-click in A14, I do not have the option of Pick From
Drop-Down List... appear. I have this in another file that was converted
over from Excel 2000 & it works fine. What am I missing in Excel 2003?

Thanx! Mary

Hi guys I am trying to create a student register.

Right now I have 3 columns; "Student Name", "Number of Subjects registered"
and "Subjects Registered".

The students' names are entered manually. The number of subjects is selected
from a drop down list, giving selection options from 1 to 6.

I would like help with the "Subjects Registered" column. There are only a
maximum of 6 fixed subjects that a student can register for, English, Math,
History, Chemistry, Physics and Accounts. Is there a way I can put this in a
drop down list and select multiple items, maybe by checking boxes so that
they are all listed in a cell? Or is there some other way to do this apart
from drop down lists?

I would appreciate any help offered. Thank you.

I have been trying to change a drop down list for ages now. I follow the
directions that I have, exactly, but can not seem to make the changes
necessary. I have followed the edit drop downs explaination, and it still
wont change. I can't even see to remove the drop down list and start fresh.
Any help would be greatly appreciated. Thanks

Hello Friends,
Please assist me on "How to open existing worksheet from Drop Down list menu options in Excel 2007". Advance thanks

When the (right-click) "Pick From Drop-Down List..." command is used in Excel
to facilitate key-entry, it only works for contiguous entries in cells in the
column above the input cell. blank cells in between break the link.

Is there a way to designate a named range of terms as the basis for the
"drop-down" list?

Microsoft,this should be a standard option!

I am looking to find a formula to have a cell auto fill based on a selection made from a drop down list in the adjacent cell. I have a master list of items with corresponding numbers next to them in adjacent cells. In another section, I have a drop down list to choose the item from this master list and want the adjacent cell to autofill with the corresponding cell adjacent to it in the master list. In the image you can see what I am asking. A4-A18 are set to choose from drop down lists that reference A22:A57, I want B4 to auto fill with the number in B22 if I choose A22 from the drop down, and B55 if I choose A55, and so on. How do I make this happen. I tried the IF/THEN formula but I can only add 7 functions and on this page I have 35 (with more to come) and on another I have 127 items. Thanks for the help.

Excel.JPG

All,

I am struggling to set up the "Pick from Drop-down List" feature in my spreadsheet. I followed an on-line tutorial but ended up with a Drop-down list in just one cell.

Any help would be appreciated.

Thanks

Hi

I have read several related threads threads but can't find the answer.

I want to trigger a macro when I select a date from a scroll down list in a cell *or* when a value in a cell calculates to a certain value.

Let's say A1 has a validation list of 5 dates:

2/1/05
2/2/05
2/3/05
2/4/05
2/5/05

When I click on the arrow and then select a date this *will* change the value of the cell, but it will not trigger the macro to run. If I double click on the cell *after* I have selected from the drop-down list, and *then* hit enter, the macro will run. But that sort of defeats the point of using the scroll down list.

Any ideas would be appreciated.
Thanks

tx

I want to be able to change a drop down list that I created via data validation with a macro

Basic construction
List has Red, Blue, Green, None
I want it to always say none after running a "refresh" macro

Thanks
mat

Hello,

I have a cell with a drop down list of values (that I specified on a separate sheet) and a column with a description for each value (the values are abbreviations). Is there a way to convert the value (abbreviation), after the user has selected it from the drop down list, to the appropriate description (those values are specified on a different column/sheet)?