VBA ?

[SUNIL]

Is it possible to run a macro when an entry is made in cell "A1"

Column "A" lists 3000 differtent items in alphabetical order. what i need to do is scroll page so that items beginning with the letter "S" are in view if "S" is entered into cell "A1"

Or is there an in-built function to do this. autofilter still requires manual scroll down, and hence takes too long.

Sunil

 

[mangesh_yadav]

Yes, it is possible to run an event when a value is entered in a cell
Just call the required macro for the OnChange event for the cell.

example:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Row = 1 and Target.Column = 1) Then
call your_macro
End If
End Sub


The above code has to be put in the VBA module for the sheet concerned
And the code will work for cell A1 as you can see from line 2 where th
check is made for changes in Target.Row = 1 which means row 1 an
Target.column = 1 which means column A

 

[David McRitchie]

If you want to get to the beginning of a range of names from anywhere in the worksheet
you could use:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'-- Column A after A1 title, must be in Ascending order
Dim val As String
Cancel = True 'Get out of edit mode
val = "M" 'default value
val = InputBox("Supply Name", "Supply Name or first " & _
"few letters of name", val)
If val = "" Then Exit Sub
Rows(Application.Match(val, Range("A2:A65536"), 1)).Offset(1, 0).Activate
End Sub

To install this event macro right-click on the worksheet tab, then
view code, insert code. More on event macros in
http://www.mvps.org/dmcritchie/excel/event.htm

More infomation on the above macro at
http://www.mvps.org/dmcritchie/excel/event.htm#match