PDA

View Full Version : Combine macros



rabsocket
02-15-2014, 05:20 AM
Hi All,

I have two separate macros which I need to combine somehow, please can you help?

When anything is changed in any cell in column 4, Macro 1 follows a hyperlink which is in column 3 to a pdf file.

Macro 2 selects the cell in column 5 after anything is changed in any cell in column 4.

They work separately, but I don't know how to combine them, i.e when anything is changed in any cell in column 4, open the pdf via the hyperlink in column 3, AND select the cell in column 5. I don't mind the order of the macros if it helps, but I need both actions to happen please. Please can you help?


MACRO 1


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Offset(0, -1) <> "" Then
Target.Offset(0, -1).Hyperlinks(1).Follow
End If
End Sub



MACRO 2


Private Sub Worksheet_Change1(ByVal Target As Range)
On Error GoTo Whoa
Application.EnableEvents = False
If Not Target.Cells.CountLarge > 1 Then
If Not Intersect(Target, Columns(4)) Is Nothing Then
Target.Offset(, 1).Select
End If
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub




Kind regards,

rabsocket

p45cal
02-15-2014, 10:04 PM
Try:
Private Sub Worksheet_Change1(ByVal Target As Range)
On Error GoTo Whoa
Application.EnableEvents = False
If Not Target.Cells.CountLarge > 1 Then
If Not Intersect(Target, Columns(4)) Is Nothing Then
Target.Offset(, 1).Select
if Target.Offset(0, -1) <> "" Then Target.Offset(0, -1).Hyperlinks(1).Follow
End If
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub

and it might be a bit more robust to use hyperlinks.count>0 rather than <>"" since there could be text in the cell but no hyperlink:

by changing:
if Target.Offset(0, -1) <> "" to:
if Target.Offset(0, -1).hyperlinks.count>0

westconn1
02-15-2014, 10:13 PM
@p45cal
i would believe you are checking the wrong cells for <>""

p45cal
02-15-2014, 10:21 PM
@p45cal
i would believe you are checking the wrong cells for <>""You're quite right, the code has been adjusted in the original reply (msg#2).