VBA ?
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 |
VBA ?
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 -- Message posted from http://www.ExcelForum.com |
VBA ?
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 -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "mangesh_yadav " wrote in message ... 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 the check is made for changes in Target.Row = 1 which means row 1 and Target.column = 1 which means column A. --- Message posted from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 01:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com