k0st4din
02-08-2023, 02:19 AM
Hello, everyone
I didn't know whether to ask here or ask a brand new question, because what I was looking for corresponds to 90% of the macro made.
My query is (if you say I will start a new topic with the question and link to this topic) can I do exactly the same idea, but make the macro so that it works in the same way, but for each selected worksheet, can to hide different rows.
For example:
London - rows - 25,69,88,89,90,115 etc
Paris - rows - 74,254,255,256,284,293 etc
First macro witch working
Private Sub Workbook_Open()
HideUnhide True End Sub
Public Sub HideUnhide(Optional LockSheet As Boolean)
'LockSheet = true forces sheet(s) to be locked. Use on workbook open.
'LockSheet not specified or false; sheet(s) locked based on button caption.
Dim shp As Object: Set shp = Sheets("Product").Shapes("Button 1").DrawingObject
If Not LockSheet And shp.Caption = "Unhide" Then
If Unhide(InputBox("Enter password"), "London", "NY", "Paris") Then
shp.Caption = "Hide"
End If
Else
If Hide("Plovdiv", "London", "NY", "Paris") Then
shp.Caption = "Unhide"
End If
End If
End Sub
Private Function Hide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
For Each sht In sheetname
With Sheets(sht)
Select Case Err.Number
Case 0
.Range( _
"4:4,5:5,10:10,11:11,13:13,14:14,17:17,18:18,34:34,35:35,38:38," & _
"45:45,46:46,49:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
).EntireRow.hidden = True
Select Case Err.Number
Case 0, 1004 'already hidden?
Case Else: Debug.Print Err.Number, Err.Description
End Select
Err.Clear
.Protect pw, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Select Case Err.Number
Case Is <> 0: Debug.Print Err.Description
End Select
Err.Clear
Case Else
MsgBox "Sheet " & sht & " not found!"
Err.Clear
End Select
End With
Next
Hide = True
End Function
Private Function Unhide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
For Each sht In sheetname
With Sheets(sht)
Select Case Err.Number
Case 0
.Unprotect Password:=pw
.Cells.EntireRow.hidden = False
If Err.Number <> 0 Then
MsgBox "Wrong password", vbExclamation
Exit Function
End If
Case Else
MsgBox "Sheet " & sht & " not found!"
Err.Clear
End Select
End With
Next
Unhide = True End Function
and from this second macro is a how to take it line witch say how to write worksheets and rows
Application.ScreenUpdating = False Dim Ary As Variant
Dim i As Long
Ary = Array(London, "26,28,39,142", Paris, "135,147,158,200,201,202", Sheet8, "26:75", Sheet3, "11:239")
For i = 0 To UBound(Ary) Step 2
Ary(i).Rows(Ary(i + 1)).Hidden = False
Next i
I didn't know whether to ask here or ask a brand new question, because what I was looking for corresponds to 90% of the macro made.
My query is (if you say I will start a new topic with the question and link to this topic) can I do exactly the same idea, but make the macro so that it works in the same way, but for each selected worksheet, can to hide different rows.
For example:
London - rows - 25,69,88,89,90,115 etc
Paris - rows - 74,254,255,256,284,293 etc
First macro witch working
Private Sub Workbook_Open()
HideUnhide True End Sub
Public Sub HideUnhide(Optional LockSheet As Boolean)
'LockSheet = true forces sheet(s) to be locked. Use on workbook open.
'LockSheet not specified or false; sheet(s) locked based on button caption.
Dim shp As Object: Set shp = Sheets("Product").Shapes("Button 1").DrawingObject
If Not LockSheet And shp.Caption = "Unhide" Then
If Unhide(InputBox("Enter password"), "London", "NY", "Paris") Then
shp.Caption = "Hide"
End If
Else
If Hide("Plovdiv", "London", "NY", "Paris") Then
shp.Caption = "Unhide"
End If
End If
End Sub
Private Function Hide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
For Each sht In sheetname
With Sheets(sht)
Select Case Err.Number
Case 0
.Range( _
"4:4,5:5,10:10,11:11,13:13,14:14,17:17,18:18,34:34,35:35,38:38," & _
"45:45,46:46,49:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
).EntireRow.hidden = True
Select Case Err.Number
Case 0, 1004 'already hidden?
Case Else: Debug.Print Err.Number, Err.Description
End Select
Err.Clear
.Protect pw, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Select Case Err.Number
Case Is <> 0: Debug.Print Err.Description
End Select
Err.Clear
Case Else
MsgBox "Sheet " & sht & " not found!"
Err.Clear
End Select
End With
Next
Hide = True
End Function
Private Function Unhide(pw As String, ParamArray sheetname() As Variant) As Boolean
On Error Resume Next
For Each sht In sheetname
With Sheets(sht)
Select Case Err.Number
Case 0
.Unprotect Password:=pw
.Cells.EntireRow.hidden = False
If Err.Number <> 0 Then
MsgBox "Wrong password", vbExclamation
Exit Function
End If
Case Else
MsgBox "Sheet " & sht & " not found!"
Err.Clear
End Select
End With
Next
Unhide = True End Function
and from this second macro is a how to take it line witch say how to write worksheets and rows
Application.ScreenUpdating = False Dim Ary As Variant
Dim i As Long
Ary = Array(London, "26,28,39,142", Paris, "135,147,158,200,201,202", Sheet8, "26:75", Sheet3, "11:239")
For i = 0 To UBound(Ary) Step 2
Ary(i).Rows(Ary(i + 1)).Hidden = False
Next i