I found this whilst trawling the web and I was wondering if there wa
some way to modify the macro (specifically the 'Select Cas
Target.Address'
'Case “$A$1″' area) to run through many rows without having t
create a seperate Case per row (i.e. having 'i' as the number of row
and creating 'Case “$A$i″).

I would be grateful for any thoughts on this matter :)

Cheers.

-"Independent of the hyperlink, put a SelectionChange event in th
worksheet code module. To do this, right click the sheet tab, an
select View Code. In the code module that pops up, select Workshee
from the top left dropdown, which puts the following procedure into th
code module:"-


Code
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
You then adjust what’s between the Sub and End Sub to get what you want:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case “$A$1″
‘ carry out action for Cell A1
Case “$A$2″
‘ carry out action for Cell A2
‘ etc.
End Select
End Sub
-------------------

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=53720

You question is not clear, but if you want to do something to a cell on the
row selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' just to demonstrate how you could work with
' a cell in the same row as the cell selected
Msgbox cells(target.row,"F").address

End Sub

--
Regards,
Tom Ogilvy

"Daminc" wrote:


I found this whilst trawling the web and I was wondering if there was
some way to modify the macro (specifically the 'Select Case
Target.Address'
'Case €œ$A$1€³' area) to run through many rows without having to
create a seperate Case per row (i.e. having 'i' as the number of rows
and creating 'Case €œ$A$i€³).

I would be grateful for any thoughts on this matter :)

Cheers.

-"Independent of the hyperlink, put a SelectionChange event in the
worksheet code module. To do this, right click the sheet tab, and
select View Code. In the code module that pops up, select Worksheet
from the top left dropdown, which puts the following procedure into the
code module:"-


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
You then adjust whats between the Sub and End Sub to get what you want:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case €œ$A$1€³
€˜ carry out action for Cell A1
Case €œ$A$2€³
€˜ carry out action for Cell A2
€˜ etc.
End Select
End Sub
--------------------


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=537207

Cheers for your reply Tom.

I'm away until Tuesday and I'm going to take a closer look at this
procedure them.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=537207

Hi Tom, back again :)

I've tried that macro you posted and I see where you're coming from.

To make my original post a little clearer:

I'm planning to create a hyperlink to a name on a worksheet that, when
clicked, will bring up contact details or a graph dipiciting recorde
statistics etc depending which will be most required by the person ho
will use this application

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=53720

Code
-------------------
If Not Application.Intersect(Target, Range("b1:b10")) _
Is Nothing Then

-------------------


Could you clarify this bit of code for me please. I have very littl
information on using 'Intersect'.

Cheers :

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=53720

I've got this little bit of code working:

Code
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim numRows As Variant
Dim tRow As Variant

numRows = ActiveSheet.UsedRange.Rows.Count

tRow = Target.Row

Select Case Target.Address(numRows)
Case "$A$" & tRow
MsgBox "Date approved was " & ActiveCell(1, 3)

End Select
End Su
-------------------


I know it's really simple but I think I can now modify it to displa
calculations derived from other sheets.

Thanks Tom for showing me a way forward :

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=53720