Hyperlinks in Macros??? Maybe easier way?

[weiser386]

Good Afternoon,

I would imagine this is a really easy question for most of you however for me it is not.

So here is my issue, I have a worksheet that has a years worth of production data that is nicely lined out with the dates included.

I have two buttons at the top of the page, one button "Current Month" reduces the report down to just show the current months worth of data and the next button "View Year" UN-hides all the cells and shows the full years worth, currently when you press the "view year" button it works correctly but it reverts you back to the beginning of the worksheet at January's data...So what I need is for the "view year" button to UN-hides all the cells as normal and use the current month to find today() date and make that the return point of the worksheet instead of reverting it back to Jan all the time. Essentially it should work like a hyperlink in that a hyperlink finds the cell you select and centers it on your screen.


Here is my code for "Current Month" button:

Sub Breakaway_Button2_Click()


' hides all rows not from current month

Dim cell As Range
Application.ScreenUpdating = False
Rows.EntireRow.Hidden = False
For Each cell In Range("b4:b381")

If Month(cell.Value) <> (Month(Date)) Then
cell.EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True

Here is my code for "View Year" button:

Sub Button2_Click()
Rows.EntireRow.Hidden = False
End Sub

Thanks in advance for your help, you guys are awesome!

 

[MARK858]

Essentially it should work like a hyperlink in that a hyperlink finds the cell you select and centers it on your screen.

Not so sure about a hyperlink centers it on your screen bit but try the code below to find today's date and if the activecell is past the center of the screen then center it.

Because you are only using column B for your date you strictly speaking don't need the j variable but I have left it there in case you do go further across the screen.

I'll leave you to insert it into your code as I am not 100% sure where you want the code to reside.

[color=darkblue]Sub[/color] Find_Date()

    [color=darkblue]Dim[/color] foundDate [color=darkblue]As[/color] Range, i [color=darkblue]As[/color] [color=darkblue]Long[/color], j [color=darkblue]As[/color] Long, myRng [color=darkblue]As[/color] Range
    
    [color=darkblue]Set[/color] myRng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)

    [color=darkblue]Set[/color] foundDate = myRng.Find(What:=Date, LookIn:=xlFormulas, _
                               LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    [color=darkblue]If[/color] [color=darkblue]Not[/color] foundDate [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]

        Application.Goto foundDate, scroll:=[color=darkblue]True[/color]

        [color=darkblue]With[/color] ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        [color=darkblue]End[/color] [color=darkblue]With[/color]

    [color=darkblue]Else[/color]
        MsgBox " not found"
    [color=darkblue]End[/color] [color=darkblue]If[/color]

End [color=darkblue]Sub[/color]

 

[Eric W]

Try changing your "View Year" code to:

Sub Button2_Click()
    Rows.EntireRow.Hidden = False
    For Each cell In Range("B4:B381")
        If Month(cell.Value) = Month(Date) Then
            cell.Select
            Exit Sub
        End If
    Next cell
End Sub

You can make the code a bit more efficient if you only search the date column in both macros, but the range is so small you don't really notice.

 

[weiser386]

Thank you guys! it worked like a charm!!!