Macro on entry

E

Ed

Hello how do I manage to program a Macro to run after a entry on a specific
cell rather than with a key or a button?

thanks!
 
P

Paul Lautman

Ed said:
Hello how do I manage to program a Macro to run after a entry on a
specific cell rather than with a key or a button?

thanks!
Click to expand...

use a worksheet_change event
 
P

Paul B

Ed, something like this, put in worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Then
MsgBox "Test"
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
E

Ed

Hello Paul, I'm not so familiarized with editing Macros and I have been
trying but I haven't been able to manage it yet... the macro I have recorded
is:

Sub FiltroInvMateriales()
'
' FiltroInvMateriales Macro
' Macro recorded 27/05/2006 by Arquitectura Integral
'
' Keyboard Shortcut: Ctrl+m
'
Selection.AutoFilter Field:=1, Criteria1:="x"
Columns("A:A").Select
Selection.EntireColumn.Hidden = True
End Sub

What is does is just to Filter Column A that has "Autofilter" to show only
the ones that have a "x" on it and then hide Column A. The "x" is given with
a IF formula, so on rows where there are entires it gives and x, those
entries are gathered through VLOOKUP's, and the target of those VLOOKUP's is
a number I select from a dropdown list. The cell where I select that number
is $C$1. What I do so far is to select the number from $C$1 dropdown and then
hit Ctrl+m to run the macro, but I would like to run it automatically each
time I select a number from $C$1.

Where do I have to insert the macro written above into the one you gave me?

thanks a lot!
 
P

Paul B

Ed, see if this will do what you want

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$C$1" Then

'**no need to select the column to hide it**
Selection.AutoFilter Field:=1, Criteria1:="x"
Columns("A:A").EntireColumn.Hidden = True

End If
End Sub

To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
E

Ed

Hello Paul, thank you very much! it is exactly what I was looking for, it's
very helpful...
 
P

Paul B

Your welcome, thanks for the feedback

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
Top