• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA (Hyper)linking to worksheet and autofiltering

J

JCTalk

Member
Hi Guys,

I've come across a problem that I think needs a VBA solution, which is not my area. :-( Please can anyone help out with this?

Background: I have a Front page summary sheet that works out the number of agents in a team that are exceeding an allowed threshold. Data is per team per month. I have a breakdown sheet which has a filtered list of agents and their data.

Requirement: I need to create (hyper)links from each "Number exceeding" number cell on the Overview sheet that will link to the Breakdown sheet and;
  • Use the MONTH from directly above the number cell on the Overview sheet, and put it in cell A1 of the breakdown (this will change my data set that is being looked up)
    then...
  • Filter the table on the Breakdown sheet using the TEAM NAME from the Overview sheet (e.g. A1, A5, A9 etc.)
  • Filter the table on the Breakdown sheet using ADHERING as "No"
Attached sample sheet.

Appreciate any help you can offer guys.
 

Attachments

Hi JCTalk,

Right-click on the Overview sheet, view code, paste this in. Uses the selection event to create hyperlink effect. Currently gets triggered if user select a single cell that has a number in it.
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strMonth As String
Dim strTeam As String
Dim numExceed As Long

'Don't do anything if user selects more than 1 cell
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

'Check if user selected a cell of interest. For now, we'll assume that it's the cells with numbers
'in them. May need to refine this later
If WorksheetFunction.IsNumber(Target.Value) Then
    'Store the values we need
    strMonth = Target.Offset(-1, 0).Value
    strTeam = Cells(Target.Row - 2, "A").Value
    numExceed = Target.Value
   
    'Get ready to work with Breakdown
    With Worksheets("Breakdown")
        .Range("A1") = strMonth
        'Apply AutoFilter
        With .Range("A3").CurrentRegion
            .AutoFilter field:=2, Criteria1:=strTeam
            .AutoFilter field:=5, Criteria1:="No"
        End With
        .Select
    End With
End If
Application.ScreenUpdating = True
End Sub
 
Hi Luke,

Many thanks for your quick reply.

It looks like it's trying to function but I think due to me removing things from the original workbook when I created the sample workbook, it's making it go wrong.

I've attached another template that more closely resembles my actual worksheet.

Problems identified:
  • It's activating when clicking any cell with a numeric value. The proper workbook has lots of other numerical data in so the activation needs to be specific the "Number exceeding" rows.
  • I have a piece of code running on the Breakdown page that auto refreshes the filters on the page to ensure blank cells aren't shown. I can't get your code to run unless I completely remove the auto refresh code...

    Private Sub Worksheet_Change(ByVal Target As Range)ActiveSheet.AutoFilter.ApplyFilterEnd Sub
Many thanks Luke. Sincerely appreciate your help getting this functionality in place.
 

Attachments

Hmm. Ok, will adapt to new layout. We now check for both being a number and in the "number exceeding" row. Other macros disabled until this macro finishes running. Month now goes in B6, not A1. Based on last template, Number exceeding row is always 24 rows below table header.
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strMonth As String
Dim strTeam As String
Dim numExceed As Long

'Don't do anything if user selects more than 1 cell

If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
'Prevent the other macro from running
Application.EnableEvents = False

'Check if user selected a cell of interest. For now, we'll assume that it's the cells with numbers
'in them. May need to refine this later
If WorksheetFunction.IsNumber(Target.Value) And LCase(Cells(Target.Row, "B")) = "number exceeding" Then
    'Store the values we need
    strMonth = Target.Offset(-24, 0).Value
    strTeam = Cells(Target.Row - 25, "B").Value
    numExceed = Target.Value
   
    'Get ready to work with Breakdown
    With Worksheets("Breakdown")
        .Range("B5") = strMonth
        'Apply AutoFilter
        With .Range("A3").CurrentRegion
            .AutoFilter field:=2, Criteria1:=strTeam
            .AutoFilter field:=5, Criteria1:="No"
        End With
        .Select
    End With
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Hi Luke,

Truly excellent work Luke. It works brilliantly. However, I literally have no idea how it does what it does haha.

Can you tell me where it starts its journey? I see the offset and target row in the code mentioning -24 and -25, which I assume to mean that its counting down that number of rows, but how on earth does it know where to start?

Thanks Luke.
 
Thanks for the kind words. Here's the explanation. :)

Code: 
'The code is triggered when a range of some type is selected in the Worksheet. You'll notice at start of macro
'here that it calls out "Taget as Range". Target is the name of a variable, and the type of the variable is a Range
'For the rest of the macro then, Target refers to the cell(s) selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Define some variables that we are going to be using.
'Good practice to do so, and helps later code readers know what you're talking about. =)
Dim strMonth As String
Dim strTeam As String
Dim numExceed As Long

'Don't do anything if user selects more than 1 cell
'Since Target is a range of cell(s), we want to make sure only one cell is selected. We don't have to,
'but thinks could get a little loopy trying to handle multiple cells. So, we check the Count (aka, how many cells)
'are in the Target. If just 1, then we can continue
If Target.Count > 1 Then Exit Sub

'Turn of the screen momentarily, so user doesn't see so many flashes.
'This also lets macro run faster, as it's not spending time
'updating the screen
Application.ScreenUpdating = False

'Prevent the other macro from running
'Pretty straight-forward, this turns off all Event based macros
Application.EnableEvents = False

'Check if user selected a cell of interest. Per latest change, we check if the cell is a number
'and if the Cell in same row as Target (aka Target.Row) and column B has the value "number exceeding"
'For reference, the Cells object has 2 arguments: row number and column number/letter (VB is flexible)
If WorksheetFunction.IsNumber(Target.Value) And LCase(Cells(Target.Row, "B")) = "number exceeding" Then
    'Store the values we need
    'Offset has 2 arguments, row offset and column offset. These go positive in the down/right directions,
    'and negative in the up/left directions. So, an offset of -24 rows says to go up 24 rows from the Target cell
    strMonth = Target.Offset(-24, 0).Value
   
    'For the variable, I could have tried to do Offset, but I already know which column I want
    'So, we take the Target row and subtract 25 from it. Works about the same, but a little easier
    'to calculate, in my opinion
    strTeam = Cells(Target.Row - 25, "B").Value
   
    'Stores the cell's value
    numExceed = Target.Value
   
    'Get ready to work with Breakdown
    'Every line inside the With statement that starts with a period traces back up
    'to this With line. Saves us from repeating Worksheets("Breakdown") on every line
    With Worksheets("Breakdown")
        'Set the value of B5 on 'Breakdown' to be the month
        .Range("B5") = strMonth
       
        'Apply AutoFilter
        'Again, I could repeat this 2 times, but it's more efficitent this way
        With .Range("A3").CurrentRegion
            'Sets the second column in the AutoFilter range to be the chosen team
            .AutoFilter field:=2, Criteria1:=strTeam
            'Sets the fifth column in the AutoFilter range to be 'No'
            .AutoFilter field:=5, Criteria1:="No"
        End With
        'Now that we're all done manipulating, select the worksheet
        'Normally we don't select things in Macros as it takes longer, but
        'this is kind of the point of a hyperlink, yes? ;)
        .Select
    End With
End If

'Turn everything back on
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Let me know if you have any questions. :awesome:
 
Sorry to bother you again Luke.

In my last template you'll see that there is a blank box on the Overview. Based on the exact same information in it (except it a is a combined view of all teams), how do I amend the code to miss out the team element when clicking the equivalent box in that top table?

I tried to duplicate the code, but take out the bits that referred to a team, but it didn't do what I expected, and I think stopped the other bit from working.

Thanks Luke.
 
Thanks for the donation. :cool:

Assuming that the top table isn't changing in size, we could do something like this in the autofilter section.
Code: 
        'Apply AutoFilter
       With .Range("A3").CurrentRegion
            'NEW Check
            If Target.Row > 32 Then
                .AutoFilter field:=2, Criteria1:=strTeam
            End If
            .AutoFilter field:=5, Criteria1:="No"
        End With

Which says, if the Target cell was in a row after row 34, then apply the team filter, otherwise just do the Adhering filter. My other idea, if the table size may change, but the cell that would normally contain the team name (cell B5 in your example) is blank, we could change the If statement to be:
Code: 
        'Apply AutoFilter
       With .Range("A3").CurrentRegion
            'NEW Check
            If strTeam <> "" Then
                .AutoFilter field:=2, Criteria1:=strTeam
            End If
            .AutoFilter field:=5, Criteria1:="No"
        End With
Hopefully one of the two ways will work.
 
Thanks Luke. The first one worked great.

I had to add in another line to change the filters back to default but seems fine after that.

.AutoFilter Field:=2

Thank you Luke. :-)
 
You must log in or register to reply here.
Back
Top