Worksheet_Change

  • Thread starter Thread starter Jack Sons
  • Start date Start date
J

Jack Sons

Hi all,

In sheet1 I have numbers in column A, the same numbers in sheet2 column B.
I want that doubleclicking on a number in column A of sheet1 results in
going to the cell of sheet2, column B that is right of the cell in column B
that has the same number in it (without highlighting anything) If I click on
the tab of sheet1 I should see the cursor still on the cell where I
doubleclicked, nothing highlighted.

I thought the following code would work, but it doesn't. It stops at
Sheets("Blad2").Columns("B:B").Select
with an errormessage that says that the method select will not work.

What did I wrong? Was I going in the right direction anyway? Should the
second half of the code (after the find operation) be changed into better
code? (I think so).

Your help will be appreciated very much.

Jack Sons
The Netherlands

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myValue As Double
Dim NewRow As Integer
Dim NewColumn As Integer

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:H")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub

If Application.IsNumber(Target.Value) Then
myValue = Target.Value

Sheets("Blad2").Columns("B:B").Select

Selection.Find(What:=myValue, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

NewRow = ActiveCell.Row
NewColumn = ActiveCell.Column

Sheets("Blad2").Range(Cells(NewRow, NewColumn + 1), Cells(NewRow,
NewColumn + 1)).Select

End If

End Sub
 
First, I would use the Worksheet_BeforeDoubleClick event, not Worksheet
Change. One way:


Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
Dim found As Range
Cancel = True
Set found = Sheets("Blad2").Columns(2).Find( _
What:=Target.Value, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If Not found Is Nothing Then
Application.Goto found.Offset(0, 1)
Else
MsgBox "'" & Target.Value & _
"' Not found in Sheet2, Column B"
End If
End Sub

Note: I'm not sure what your reference to "without highlighting
anything" means.

Note also that your text said the doubleclick occurs in column A, while
your macro fires if the changed cell is in columns A:H. I limited the
above macro to column A.
 
JE,

Your code works fine (of course).
without highlighting anything" meant that in some variations to my code it
Click to expand...
occurred that the correct cell of sheet2 was selected, but also its whole
column highlighted (=selected). When I returned to sheet1 there also the
whole column (col A) was highlighted (=selected).

Please explain

1) why my code stopped at the line that I indicated
2) all variations to my code failed "shift to" sheet2, I mean that even when
it appeared (afterwards) that in sheet2 the correct cell was selected, on
the screen would remain sheet1. I don't understand why sheet2 did not appear
on the screen. (In your code it is done with the GoTo I believe. How and
why?)

TIA

Jack.
 
You can only select a range on the active sheet. So since Blad2 isn't the
activesheet, the code stops.

Sheets("Blad2").Columns("B:B").Select

You could have done something like this:

with sheets("blad2")
.select
.columns("B:B").select
....

But as J.E. showed, you don't usually have to select something to work with it.

stops
 
Back
Top