Unable to run the 2 sets of codes in one sheet

Question

I need help for VBA as I'm new to this programming language. Is it possible to have 2 different sets of codes in one sheet in the workbook?

I want to make the Excel sheet more interactive like clicking on certain cell then highlighting the entire row that the cell is selected. But the sheet that im trying to make it interactive has a set of codes already.

Here is the codes that I want to make the excel sheet interactive

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    initializeWorksheets
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Activate
        ' Clear the color of all the cells
        Cells.Interior.ColorIndex = 0
        If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub
        Application.ScreenUpdating = False
        With ActiveCell

            ' Highlight the row and column that contain the active cell, within the current region
            Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.ColorIndex = 6

        End With
    Next ws  
    Application.ScreenUpdating = True    
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'filtering
    Dim ws As Worksheet
    ws.Activate

    Dim ccolumn As Integer
    Dim vvalue As String

    ccolumn = ActiveCell.Column
    vvalue = ActiveCell.Value

    For Each ws In Worksheets
    If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub
        Application.ScreenUpdating = False
        With ActiveCell
            Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).AutoFilter Field:=ccolumn, Criteria1:=vvalue
            Cancel = True
        End With
    Next ws
End Sub

Here is the codes that it is used for the same sheet:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    initializeWorksheets
    Application.ScreenUpdating = False
    If (ActiveSheet.Name = "Student Viewer") Then
        searchKey = Trim(Target.Range.Value)
        If (Right(searchKey, 1) = ")") Then
            searchKey = Right(searchKey, Len(searchKey) - InStrRev(searchKey, "(", -1))
            searchKey = Left(searchKey, Len(searchKey) - 1)
        End If
        temp = 2
        Do While (mainSheet.Range(findColumn(mainSheet, "IC Number") & temp) <> searchKey & "")
            temp = temp + 1
            If (temp > 65535) Then
                MsgBox ("Error in Finding xxxx Details")
                End
            End If
        Loop

        viewerSheet.Unprotect
        ' Set details
        For i = 2 To 10
            viewerSheet.Range("C" & i) = mainSheet.Range(findColumn(mainSheet, Left(viewerSheet.Range("B" & i), Len(viewerSheet.Range("B" & i)) - 1)) & temp)
            viewerSheet.Range("F" & i) = mainSheet.Range(findColumn(mainSheet, Left(viewerSheet.Range("E" & i), Len(viewerSheet.Range("E" & i)) - 1)) & temp)
        Next i
        For i = 2 To 3
            viewerSheet.Range("I" & i) = mainSheet.Range(findColumn(mainSheet, Left(viewerSheet.Range("H" & i), Len(viewerSheet.Range("H" & i)) - 1)) & temp)
        Next i

        loadSummary

        viewerSheet.Protect
    ElseIf (ActiveSheet.Name = "xxxx Viewer") Then
        searchKey = Trim(Target.Range.Value)
        viewerSheet2.Unprotect
        ' Set details
        temp = 2
        Do While (DetailsSheet.Range(findColumn(DetailsSheet, "Policy Num") & temp) <> searchKey & "")
            temp = temp + 1
            If (temp > 65535) Then
                MsgBox ("Error in Finding Details")
                End
            End If
        Loop
        For i = 2 To 11
            viewerSheet2.Range("C" & i) = DetailsSheet.Range(findColumn(DetailsSheet, Left(viewerSheet2.Range("B" & i), Len(viewerSheet2.Range("B" & i)) - 1)) & temp)
        Next i
        For i = 2 To 6
            viewerSheet2.Range("I" & i) = ValuesSheet.Range(findColumn(ValuesSheet, Left(viewerSheet2.Range("H" & i), Len(viewerSheet2.Range("H" & i)) - 1)) & temp)
        Next i
        For i = 7 To 12
            viewerSheet2.Range("I" & i) = DetailsSheet.Range(findColumn(DetailsSheet, Left(viewerSheet2.Range("H" & i), Len(viewerSheet2.Range("H" & i)) - 1)) & temp)
        Next i
        viewerSheet2.Hyperlinks.Add Anchor:=Range("C2"), Address:="", SubAddress:="'Client Viewer'!A1"
        loadDetail
        viewerSheet2.Protect
    End If

    Application.ScreenUpdating = True

End Sub

Yes, it is possible. Where are you getting stuck with having both sets of code in the file? — Shauno_88, Apr 27 '15 at 01:31
`Is it possible to have 2 different sets of codes in one sheet in the workbook? ` Yes. As long as you use different events and you follow how the sequence of how event based routine are called. You can also use any number of sub routine you can call within your event based routine or sub routine (sub routine within a sub routine). As for your question, it is a bit vague what you're trying to achieve. Please elaborate further your issues, what's not working and which code or line. — L42, Apr 27 '15 at 01:40
When i want to click on a cell that is hyperlink, it cannot run. i think is from this code. temp = 2 Do While (mainSheet.Range(findColumn(mainSheet, "IC Number") & temp) <> searchKey & "") temp = temp + 1 If (temp > 65535) Then MsgBox ("Error in Finding Client Details") End End If Loop They say error in finding xxxx details — Sherry Kong, Apr 27 '15 at 01:42
Define different events ? Like how ? What kind of event based routine? I want to make the sheet more interactive for the user. Like if they were to click on the cell, the entire row will be highlighted so it will be easier to read. But certain field like IC number is hyperlinked to another sheet. Because of that, the user cant click on the IC number. As a result, there is a message saying that Error in finding xxxx details. — Sherry Kong, Apr 27 '15 at 01:56
Events are like what you posted (e.g `Workbook_SheetFollowHyperlink` and `Worksheet_SelectionChange`). Now the problem here is if you want to select the cell or click the hyperlink. When you point your mouse on the cell, it can be a cross or pointer. So the user gets to decide if they want it selected or follow the link. — L42, Apr 27 '15 at 03:07
@L42 yes. When i point my mouse on that cell, it will load but show me an error message saying that Error in finding xxxx details. Now i want the user to be able to click on the hyperlink and able to load it. Also, if i were to put the codes in the respective sheet, the codes for the hyperlink will be not work too. — Sherry Kong, Apr 27 '15 at 03:19
@L42 I want the user to only click the hyperlink. Sorry for the confusion. — Sherry Kong, Apr 27 '15 at 03:22
Click the hyperlink and then highlight the row where the hyperlink is? You can do that by using `Selection` event only. I'll try to post a sample for you later. — L42, Apr 27 '15 at 03:34
@L42 clicking on other cell that do not have hyperlink, it will then highlight. But when clicking on the hyperlink, it will go to the page that is directed to. — Sherry Kong, Apr 27 '15 at 03:42

score 0 · Accepted Answer · edited May 23 '17 at 11:43

As commented, you can try this approach:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo halt
    Application.EnableEvents = False

    With Me ' Me refers to the worksheet where you put this code
        .Cells.Interior.ColorIndex = -4142 ' xlNone
        If Not CBool(-Target.Hyperlinks.Count) Then ' Check if there is hyperlink
            Target.EntireRow.Interior.ColorIndex = 6 ' or you can use RGB(255, 255, 0)
        Else
            Target.Hyperlinks(1).Follow  ' follow hyperlink if there is
            CodeFromYourFollowHyperlinkEvent ' call a routine
        End If
    End With

moveon:
    Application.EnableEvents = True
    Exit Sub
halt:
    MsgBox Err.Description
    Resume moveon
End Sub

As you can see above, CodeFromYourFollowHyperlinkEvent should be a sub that contains what you want done in your FollowHyperlink event as shown below.

Private Sub CodeFromYourFollowHyperlinkEvent()
    ' Put your code in FollowHyperlink here
    initializeWorksheets
    Application.ScreenUpdating = False
    If (ActiveSheet.Name = "Student Viewer") Then
    .
    .
    .

End Sub

Now take note that you need to exercise explicitly working on your objects.
To know more about that, check this cool post out.

Unable to run the 2 sets of codes in one sheet

1 Answers1