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

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

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

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

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

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

Hi Kirk,

I have been away for a couple of days and hence the delayed reply. Your
assumption of how Excel knows which sheet is correct. It is the one that it
is called from.

Re your other question: It is possible to search other sheets but first a
couple of questions.

Is my assumption correct that you only want to search the same column in
each worksheet?
You said there is 3 sheets. Is that the total number of sheets in the
workbook or did you mean there are 3 sheet to search. If the latter, then
what are the worksheet names to be searched?

I have been assuming that there will always be only one value to match the
one entered. Is this correct or is it possible for there to be more than one
cell containing the value being searched? (Either in a single worksheet or in
all 3 worksheets).

If the answer to the previous question is 'yes', then does it matter which
value is found and if so, what rules should be applied such as after the cell
that was clicked, the first occurrence in the workbook etc and also does it
matter what order the worksheets are searched? That is the one that was
clicked first and then the other 2 worksheets in any order or a priority
order.

Regards,

OssieMac

"kirkm" wrote:

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