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:
polly__ la Simpatie.ro
 | Femeie
24 ani
Brasov
cauta Barbat
24 - 47 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
First I create a hyperlink in the ActiveCell to itself.
For example I am in cell E5 and I create a hyperlink to E5.
Use this code to do that:
I add multiple such hyperlinks to more cells, eg. E11, E24, E34, E41 and E48, knowing the row range I want to hide/show for each cell.
Then, I add the following snippet to the worksheet code (in my case in the code of Sheet1).
In the snippet below is specified exactly what rows are to be hidden / shown depending on the ActiveCell.
The problem is when you add or delete a row / any row for all the subsequent ranges - in VBA the rows to be hidden are set. Eg. if in E11 I have set to hide rows 12:14, then if I would delete row E10, the rows I must hide would be 11:13, but in VBA the rows will remain fixed to 12:14, thus hiding the wrong ones.
Also the subsequent hyperlinks (if you created them all) will be changed.
This works well for a static - unchangeable - table.
Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim rng As String
rng = ActiveCell.Address
Select Case rng
Case Is = "$E$5"
If Rows("6:8").EntireRow.Hidden = True Then
Rows("6:8").EntireRow.Hidden = False
Else
Rows("6:8").EntireRow.Hidden = True
End If
Case Is = "$E$11"
If Rows("12:23").EntireRow.Hidden = True Then
Rows("12:23").EntireRow.Hidden = False
Else
Rows("12:23").EntireRow.Hidden = True
End If
Case Is = "$E$24"
If Rows("25:32").EntireRow.Hidden = True Then
Rows("25:32").EntireRow.Hidden = False
Else
Rows("25:32").EntireRow.Hidden = True
End If
Case Is = "$E$34"
If Rows("35:40").EntireRow.Hidden = True Then
Rows("35:40").EntireRow.Hidden = False
Else
Rows("35:40").EntireRow.Hidden = True
End If
Case Is = "$E$41"
If Rows("42:47").EntireRow.Hidden = True Then
Rows("42:47").EntireRow.Hidden = False
Else
Rows("42:47").EntireRow.Hidden = True
End If
Case Is = "$E$48"
If Rows("49:53").EntireRow.Hidden = True Then
Rows("49:53").EntireRow.Hidden = False
Else
Rows("49:53").EntireRow.Hidden = True
End If
Case Else
MsgBox "No proper cell is selected." & vbCrLf & _
"The required code doesn't exist in VBA.", vbOKOnly
End Select
End Sub |
Alternatively, you could add each of the If statements to its own procedure (eg Sub CellE41(), Sub CellE48() etc.), then under Case write Run ("CellE41") or Run ("CellE48") etc.
I am the source for this one.
Modificat de TRaP (acum 5 ani)
|
|
| pus acum 5 ani |
|