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
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 |
|