Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat.
Nou pe simpatie:
Profil Elena01
Femeie
20 ani
Braila
cauta Barbat
19 - 31 ani
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Hyperlink to Macro to Navigation and Table Filtering Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
Mrrrr
AdMiN

Inregistrat: acum 17 ani
Postari: 2182
You could just have a button for this instead of complicating it with a macro, but I want to make a very simple interface for any user who's accessing my file, so while I complicate things in the background, I simplify them visually and interactively

Okay, now this may sound fairly complicated because it involves several macros in multiple sheets, but it's not complicated at all. You will have 2 static macros and only the sheet macros will change - you'll see it's easy.

Lemme explain a bit what all the sheets contain and what are the relations I made between them via code or via normal hyperlink..

TOC       contains links to all sheets (it's a table of contents)
Law_1    first row is frozen; sheet contains articles of Law 1 + a link to go back to TOC + a link to go filter the table in DEF sheet by source (Law_1, Law_2 or Law_3) + a GO UP link that comes to cell A2
Law_2    same as above, but for Law 2
Law_3    same as above, but for Law 3
DEF        contains a table with definitions from all 3 laws (Table1) with a column stating Law_1, Law_2 or Law_3, depending where the definition was taken from

Note:
In Table1, in the column relating to the 3 laws I didn't use sheet names which are short (Law_1 etc.) but I used real Law numbers and years, like Law 36/2022.
Thus, in all sheets in cell X1 I added the exact name I used in Table1 for each Law.
Eg. in sheet Law_1, cell X1 I have Law 36/2022, and next to the definitions in Table1 (sheet DEF) from Law_1 I also have Law 36/2022, connecting X1 to Table1.
I did this because the table might be used independently (without going to it from hyperlink with filter) and so one would understand where he/she can find the definition.

I made a module for each of the following codes, but you can make 1 module containing both, it doesn't matter (but do not name the module/s the same as any of the 3 macros)

1. A code that filters Table1 from DEF sheet with the criteria from cell X1 in each of the 3 Law sheets. This will be activated by clicking a link in one of the 3 Law sheets.

Sub Filter_Def()

Application.ScreenUpdating = False

Dim lo As ListObject
Dim iCol As Long
Dim crit As String
    crit = ActiveSheet.Range("X1").Value

Set lo = Worksheets("DEF").ListObjects("Table1") 'lo = ActiveSheet.ListObjects("Table1")
    lo.AutoFilter.ShowAllData
    iCol = lo.ListColumns(4).Index 'iCol = lo.ListColumns("WHERE IS IT").index
    lo.Range.AutoFilter Field:=iCol, Criteria1:=crit

Worksheets("DEF").Select

Application.ScreenUpdating = True

End Sub


2. This code is activated by a hyperlink in DEF sheet in order to clear all the filters in Table1.

Sub Clear_All()

Application.ScreenUpdating = False

Dim lo As ListObject
  For Each lo In ActiveWorkbook.Worksheets("DEF").ListObjects
    lo.AutoFilter.ShowAllData
Next lo
       
With ActiveWorkbook.Worksheets("DEF").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveSheet.Range("A4").Select

Application.ScreenUpdating = True

End Sub


Let's do the required settings for sheet Law_1

1. In the code of sheet Law_1 (right click on sheet - View Code) I added the following code. I'm going to explain the code with comments inside of it


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

' in cell A9 I have a normal hyperlink but I want it to filter Table1 from sheet DET
If Target.Range.Address = "$A$9" Then
    Call Filter_Def
    Exit Sub
End If

End Sub


2. In sheet Law_1 I have the following:

a. First row frozen
b. Cell X1 contains the name of the law, value which is also contained in column WHERE IS IT from Table1 (sheet DEF)
c. Go to TOC hyperlink in cell A1 going to cell A1 in sheet TOC
d. Go to top hyperlink in cell C1 going to cell A2 in current sheet
e. Hyperlink in cell A9 going to itself - this because the code from point 1 will link cell A9 to the macro Filter_Def which will filter Table1 using the value in X1 in column WHERE IS IT from Table1

Similar thing is done for the rest of the sheets that need filtering Table1.
Note: a different cell might be instead of cell A9, simply change that in your sheet code and you are done.


_______________________________________


pus acum 1 an
   
Pagini: 1  

Mergi la