VBA too Large

[RBone90]

So I am new to VBA and I created a hyperlink that will take me to another page if it is selected. It will also tell me which cell was selected (which is important). However, it was way to big and I am stuck. It begins on 162 and goes to 344 and also needs to go from column J to column DD. PLEASE HELP!

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

'Update cell B2 in TEST sheet based on the origin of hyperlink
If Sh.Name = "Master" Then
If GSourceCell = "J162" Then
Sheets("TEST").Range("B2").Value = "Hyperlinked from cell J-162"
ElseIf GSourceCell = "J163" Then
Sheets("TEST").Range("B2").Value = "Hyperlinked from cell J-163"
ElseIf GSourceCell = "J164" Then
Sheets("TEST").Range("B2").Value = "Hyperlinked from cell J-164"
ElseIf GSourceCell = "J165" Then
Sheets("TEST").Range("B2").Value = "Hyperlinked from cell J-165"
ElseIf GSourceCell = "J166" Then
Sheets("TEST").Range("B2").Value = "Hyperlinked from cell J-166"
ElseIf GSourceCell = "J168" Then
Sheets("TEST").Range("B2").Value = "Hyperlinked from cell J-168"
etc....

 

[jproffer]

Why not skip all that If stuff and just do something like:

Sheets("TEST").Range("B2").Value = "Hyperlinked from cell " & GSourceCell

Or if it just has to have the "-" in it:

"Hyperlinked from cell " & Left(CSourceCell,1) & "-" & Mid(GSourceCell,2,100000)

 

[welshgasman]

Just concatenate the source cell with your string

Sheets("TEST").Range("B2").Value = "Hyperlinked from cell " & GSourceCell

HTH

 

[offthelip]

this code should do it:

let1 = Left(GsourceCell, 1)
 If let1 = "J" Then
  num3 = Val(Right(GsourceCell, 3))
  If num3 > 161 And num3 < 344 Then
    Sheets("TEST").Range("B2").Value = "Hyperlinked from cell J-" & num3
  End If
End If

 

[RBone90]

jproffer & welshgasman, this did not work. does it look correct?

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

'Update cell B2 in TEST sheet based on the origin of hyperlink
Sheets("TEST").Range("B2").Value = "Hyperlinked from cell " & GSourceCell

End Sub

 

[RBone90]

off the lip, I need the column to change also.

 

[jproffer]

Which column? The B2 column? The GSourceCell "column" could be anything. It's just adding that string variable to your sentence in B2.

If you need B2 to change as the columns move over that could be done...just need to know what you meant by that.

 

[jproffer]

If that is what you're wanting: For instance if GSourceCell = J163 then you want your sentence to be in B2...and if GSourceCell = L344, you want your sentence in B4 then something like this would do it in just a few lines I think.

Col = Left(GSourceCell, 1)
Cols = Range("A:" & Col).Columns.Count
    Sheets("TEST").Cells(2, Cols - 8).Value = "Hyperlinked from cell " & GSourceCell