Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - JumpTo Specific Row
Proposed:
A double click in a cell launches an input box. If what is entered exists in any row in Col X then immediatly show the Sheet starting at that row? Is that possible? Thanks - Kirk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - JumpTo Specific Row
Hi,
Right click on the worksheet tab name and select View code and paste the following into the VBA editor window. As per the comment, try with and without the Cancel=True line to see what you think is the best. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Try with and without Cancel = True Cancel = True 'Place cell in top row of window. ActiveWindow.ScrollRow = Target.Row 'Alternative option to place cell in top left of window 'Application.Goto Range(Target.Address), Scroll:=True End Sub Regards, OssieMac "kirkm" wrote: Proposed: A double click in a cell launches an input box. If what is entered exists in any row in Col X then immediatly show the Sheet starting at that row? Is that possible? Thanks - Kirk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - JumpTo Specific Row
My apologies. I did not answer you question properly. Here is the completed
code. Right click on the sheet name tab and select View Code and copy and paste the macro into the VBA editor. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim response As Variant Dim foundCell As Range Cancel = True response = InputBox("Enter data to find in column") Columns(Target.Column).Select Set foundCell = Selection.Find(What:=response, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not foundCell Is Nothing Then foundCell.Select 'Place cell in top row of window. ActiveWindow.ScrollRow = foundCell.Row 'Alternative: Place cell in top left of window 'Application.Goto Range(foundCell.Address), Scroll:=True Else MsgBox response & " Not found" Target.Select End If End Sub Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - JumpTo Specific Row
Many thanks OssieMac
I have it working !! Is it possible to make 2 changes ? 1. Execute the code from a module, so I can call it from more than one sheet without duplicating it? 2. The eventual cell that is displayed and selected - can the bold outline around the cell be off? Thanks - Kirk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - JumpTo Specific Row
Hi Kirk,
Selected Cells always have a bold outline. I don't know any way of getting rid of it. For you other request insert the following private sub in each worksheet so that it calls a sub in a module. (Being an event driven procedure it must be tied to the worksheet):- Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True Call Cell_Select(Target) End Sub Then insert the following sub in a module so that it can be called from the event driven procedure in each worksheet. The Target value is passed to it when it is called:- Sub Cell_Select(Target) Dim response As Variant Dim foundCell As Range response = InputBox("Enter data to find in column") Columns(Target.Column).Select Set foundCell = Selection.Find(What:=response, _ After:=Cells(Rows.Count, Target.Column), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not foundCell Is Nothing Then foundCell.Select 'Place cell in top row of window. ActiveWindow.ScrollRow = foundCell.Row 'Alternative: Place cell in top left of window 'Application.Goto Range(foundCell.Address), Scroll:=True Else MsgBox response & " Not found" Target.Select End If End Sub I will be out of the picture for a few days but will check back here after that in case you have any further problems but feel free to post a fresh request if you want answers before I return. Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - JumpTo Specific Row
Hi OssieMac,
That's all working perfectly. I've now been able to add the rest of the code and can see a need for one last capability! I can't see how it knows which Sheet it's using. I assume its the one that calls it. However there's 3 sheets involved and sometimes you want one of the others (instead of the one you clicked in). Is that a problem? Thanks again, Cheers - kirk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send data from userform to specific cell on specific sheet? | Excel Programming | |||
Link from a specific Cell in Excel to a specific para. in Word | Excel Worksheet Functions | |||
Form a circle (ie. color specific cells) given specific radius | Excel Programming | |||
Highlight a row if a specific cell is specific numbers/words | Excel Worksheet Functions | |||
How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) |