Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SCROLL ON CELL CHANGE
hi from a newbie
in column A i have a very long list of medical products in alphabetical order. I keep having to scroll up and down all day to find items. I would like a sheet macro to detect a cell change e.g in B1 i enter "para" and column A scrolls down until "Paracetamol" is visible on the screen in column A. (i.e column A automatically scroll to line 400 where cell A400 is Paracetamol). Thanks in advance Sunil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SCROLL ON CELL CHANGE
Right click sheet tabview codecopy/paste this
Now, if you type para in cell a1 you will go there Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Call gotoltr End Sub Sub gotoltr() lr = Cells(Rows.Count, 1).End(xlUp).Row x = UCase(Range("a1")) ml = Len(x) On Error Resume Next For Each c In Range("a4:a" & lr) If Left(Trim(UCase(c.Value)), ml) = x Then Exit For End If Next If UCase(Range("b1")) = "Y" Then Cells(c.Row, 1).Select End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "sunilpatel" wrote in message ... hi from a newbie in column A i have a very long list of medical products in alphabetical order. I keep having to scroll up and down all day to find items. I would like a sheet macro to detect a cell change e.g in B1 i enter "para" and column A scrolls down until "Paracetamol" is visible on the screen in column A. (i.e column A automatically scroll to line 400 where cell A400 is Paracetamol). Thanks in advance Sunil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SCROLL ON CELL CHANGE
Freeze the window pane in cell B2 by selecting cell B2, then, in main menu,
select Window-Freeze Panes. Then in the worksheet code module, try something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Dim c As Range Set c = Me.Range("A:A").Find(Target.Value) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row End If End If End Sub -- Hope that helps. Vergel Adriano "sunilpatel" wrote: hi from a newbie in column A i have a very long list of medical products in alphabetical order. I keep having to scroll up and down all day to find items. I would like a sheet macro to detect a cell change e.g in B1 i enter "para" and column A scrolls down until "Paracetamol" is visible on the screen in column A. (i.e column A automatically scroll to line 400 where cell A400 is Paracetamol). Thanks in advance Sunil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SCROLL ON CELL CHANGE
Following macro is different from what you want ("change in B1") but more
flexible. It assumes that you've put the cursor into the column of the data table that you want to search (so it works on any column). It pops up an inputbox where you would input "para". Upon Enter the screen scrolls to the first cells which starts with "para" (the normal Excel Find command would find para anywhere in the cell - that's not what you want) .The search is not case sensitive, so it will find Paracetamol, paracetamol or PARAcetamol. I recommend that you assign a keyboard shortcut to this macro: In the Excel menu go to Tools-Macro-Macros-[select macro]-Options. Cheers, Joerg Mochikun Sub QuickSearch() Dim SearchString As String SearchString = InputBox("Search for...") For Each cell In Range(ActiveCell.CurrentRegion.Columns(ActiveCell. Column).Address) If LCase(SearchString) = Left(LCase(CStr(cell.Text)), Len(SearchString)) Then cell.Select Exit Sub End If Next cell End Sub "sunilpatel" wrote in message ... hi from a newbie in column A i have a very long list of medical products in alphabetical order. I keep having to scroll up and down all day to find items. I would like a sheet macro to detect a cell change e.g in B1 i enter "para" and column A scrolls down until "Paracetamol" is visible on the screen in column A. (i.e column A automatically scroll to line 400 where cell A400 is Paracetamol). Thanks in advance Sunil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I put a scroll bar within a cell to change its contents? | Excel Discussion (Misc queries) | |||
Adding a scroll bar to a cell to scroll its contents. | Excel Programming | |||
Can the scroll wheel be used to change cell values | Excel Discussion (Misc queries) | |||
Scroll bar to change value in cell - PART 2 | Excel Programming | |||
How do I change scroll speed? | Setting up and Configuration of Excel |