Two macro button but in a double action

[k0st4din]

Hello
I am writing to ask for your assistance:
I did two macro to hide and display rows in my worksheet. I've done two buttons and everything works incredibly well, I turned the internet to find a solution how to get them into a button and perform the same actions. Ie it pressed once to perform (execute) the first macro, it rang a second time to perform the second macro.
Many ask for your assistance and thank you in advance

Sub hide()
Application.ScreenUpdating = False
Rows("2:2").Select
Selection.Locked = False
Selection.FormulaHidden = False
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,4 9:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
).Select
Range("A79").Activate

Selection.EntireRow.Hidden = True
Range("A3").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Application.ScreenUpdating = True
End Sub
-----------------------------------------------------------------------------------------
Sub unhide()
Application.ScreenUpdating = False
On Error GoTo ErrHandler
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation

'ActiveSheet.Unprotect ("kosta")
Application.ScreenUpdating = True
End Sub
And another thing I do not know how to do - what must complete and how and where it can be implemented both macro I select sheets (for example, the action is for sheet names - "Moscow", "London" , "Italy", "New Zealand", etc.) with just this one button?

[Ago]

Sub HideUnhide()
Application.ScreenUpdating = False
If Range("4:4").EntireRow.Hidden = False Then 'Sub hide
Rows("2:2").Select
Selection.Locked = False
Selection.FormulaHidden = False
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,4 9:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
).Select
Range("A79").Activate

Selection.EntireRow.Hidden = True
Range("A3").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Application.ScreenUpdating = True


Else 'Sub Unhide
On Error GoTo ErrHandler
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation

'ActiveSheet.Unprotect ("kosta")
Application.ScreenUpdating = True


End If








End Sub





I'm not sure I understand question two.
You need this macro to function on several sheets when you push the button?

[Aussiebear]

Have the macro call a listbox where the User can select the location for the remainder of the macro to work with.

[k0st4din]

Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear) with much effort did this macro and I could not finish it, I just do not understand it as you understand it. I do not know where and what must complete must complete to tell the macro - something like a selected sheet names to izplalni macro to hide and then pressing the second show hidden rows.
If there is anything that is not clear will try to explain more.
Perhaps what I write is nonsense, but something like:

Sub HideUnhidee()
Application.ScreenUpdating = False
' for each sheet with name ("Moscow", "London" , "Italy", "New Zealand", etc.)
If Range("4:4").EntireRow.Hidden = False Then 'Sub hide
Rows("2:2").Select
Selection.Locked = False
Selection.FormulaHidden = False
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,4 9:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
).Select
Range("A79").Activate

Selection.EntireRow.Hidden = True
Range("A3").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Application.ScreenUpdating = True


Else 'Sub Unhide
' for each sheet with name ("Moscow", "London" , "Italy", "New Zealand", etc.)
On Error GoTo ErrHandler
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation

'ActiveSheet.Unprotect ("kosta")
Application.ScreenUpdating = True


End If



End Sub



You need this macro to function on several sheets when you push the button? - YES :)

[Ago]

I'm typing from my phone now so it's slightly harder to code.

But look at this:
http://support.microsoft.com/kb/142126

Ws_count =..... And down to Next I should be in each of your if statement or else statement.
And your currect code should be where microsoft page tells your code to be.

Hope it makes sense

[k0st4din]

Hello again,
if I have to wait as long as necessary.
Requests, but as far as I understand it makes it replays the entire workbook for all sheets that are in it.
If we say that this statement is exactly right - it I do not work for me because I filed only an example, and in my workbook I have over 50 sheets and I want to just specific sheets to do so to show or hide.
I have shown in my previous comment with an example that is certainly wrong, but I do not know how to do it.
Maybe something like:

Sheets(Array("Moscow", "London", "Italy", "New Zealand", etc.)).Select
Many thanks for the support from you, we just have to find the right solution.
Ie what to do in the code to perform this action for specific sheets.

[Ago]

Sub HideUnhide()
Dim WS_Count As Integer
Dim I As Integer
Application.ScreenUpdating = False
If Range("4:4").EntireRow.Hidden = False Then
'Sub hide
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
Rows("2:2").Select
Selection.Locked = False
Selection.FormulaHidden = 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").Select
Range("A79").Activate
Selection.EntireRow.Hidden = True
Range("A3").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFiltering:=True
next I
Application.ScreenUpdating = True
Else
'Sub Unhide
On Error Goto ErrHandler
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Next I
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
'ActiveSheet.Unprotect ("kosta")
Application.ScreenUpdating = True
End If
End Sub



I think this could work. Just a quick fix


And the forum screwed up my post....

[k0st4din]

Hi, I fixed the macro to read, but modifying it gives me an error here.


Sub HideUnhide12121212()
Dim WS_Count As Integer
Dim I As Integer
Application.ScreenUpdating = False
If Range("4:4").EntireRow.Hidden = False Then 'Sub hide
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
Rows("2:2").Select
Selection.Locked = False '->>> now here gives me error
Selection.FormulaHidden = False
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,4 9:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _
).Select
Range("A79").Activate
Selection.EntireRow.Hidden = True
Range("A3").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _ ' and here at least the logic of my macro remains to lock the only the active sheet, and other .....?
, AllowFiltering:=True
Next I
Application.ScreenUpdating = True
Else 'Sub Unhide
On Error GoTo ErrHandler
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Next I
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
'ActiveSheet.Unprotect ("kosta")
Application.ScreenUpdating = True
End If
End Sub



There is still something not clear to me.
And it is this - how and where to write just for the sheets to hide, lock and then again just the same to have the opposite effect.
I will once again give you an example, hoping to be understood:
I have for example 10 sheets:
1 - bananas
2 - london
3 - strawberries
4 - Paris
5 - Italy
6 - New Zealand
7 - Japan
8 - mushrooms
9 - Greece
10 - England
I want the macro action with a button to perform only sheets (London, Paris, Italy, New Zealand, Japan, Greece, England) and does nothing on the other (Bananas, Strawberries, mushrooms)


In your example, processing the macro - turns out that it absolutely counts all sheets, which leads me to the conclusion that all the sheets(rows) will be hidden, locked, etc.
Wrong somewhere?
Thanks for the effort, I think we left a little to finish it.

[k0st4din]

So here's my idea - but this is done by clicking on any one selected from my sheet creating a password and set filters etc.
Thus I choose himself which are the sheets, but to stop here because it gives me an error - just do not know how to do it,
Please, for your assistance!!!
I am convinced that there is a much easier way for things to happen, but I do not know how to do them.
Please do not mock me :(

Sub poslednaproba()
Application.ScreenUpdating = False
If Range("4:4").EntireRow.Hidden = False Then 'Sub hide
Sheets(Array("London", "Italy", "Paris", "UK", "Greece")).Select
Sheets("London").Activate
Rows("2:2").Select
Selection.Locked = False
Selection.FormulaHidden = False
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,4 9:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79").Select
Range("A79").Activate
Selection.EntireRow.Hidden = True
Range("A2").Select
Sheets("London").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Sheets("Italy").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("Paris").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("UK").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("Greece").Select
ActiveSheet.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("London").Select
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error GoTo ErrHandler
Range("A2").Select
Sheets("London").Select
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation

Application.ScreenUpdating = True

Sheets("Italy").Select
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("Paris").Select
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("UK").Select
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("Greece").Select
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("London").Select
Range("A2").Select
End If
End Sub

[k0st4din]

Aussiebear:
Hello if I can do so if you can otherwise only one button.
I made a small example

[SamT]

Option Explicit

Sub poslednaproba()
Dim RowsToHide As String
Dim SheetsToUse As Variant
Dim ThePassWord As String
Dim TestSheet As String
Dim i As Long

RowsToHide = "4:5, 10:11, 13:14, 17:18, 34:35, 38:38, 45:46, 49:50, 72:73, 76:79"
SheetsToUse = Array("London", "Italy", "Paris", "UK", "Greece")
TestSheet = "London"
Application.ScreenUpdating = False
ThePassWord = InputBox("Please write your password!")

'Test Password
On Error GoTo ErrHandler
Sheets(TestSheet).Unprotect Password:=ThePassWord

'Check if Rows are hidden
If Sheets(TestSheet).Range("4:4").EntireRow.Hidden = False Then
GoTo HideSheets
Else
GotoUnHideSheets
End If

HideSheets:
For i = LBound(SheetsToUse) To UBound(SheetsToUse)
With Sheets(SheetsToUse(i))
.Unprotect Password:=ThePassWord
.Rows("2:2").Locked = False
.FormulaHidden = False
.Range(RowsToHide).EntireRow.Hidden = True
.Range("A1").Select
.Protect (ThePassWord), DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFiltering:=True
End With
Next i
GoTo GracefulExit 'Done hiding sheets

UnHideSheets:
For i = LBound(SheetsToUse) To UBound(SheetsToUse)
With Sheets(SheetsToUse(i))
.Unprotect Password:=ThePassWord
.Range(RowsToHide).EntireRow.Hidden = False
.FormulaHidden = True
.Rows("2:2").Locked = True
.Protect (ThePassWrod), DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFiltering:=True
.Range("A1").Select
End With
Next i

GracefulExit: '
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox "Wrong password", vbExclamation
On Error GoTo 0 'Clear the error
Application.ScreenUpdating = True
End Sub

[k0st4din]

Hello SamT,
now try the macro, but first I do not hide anything and the second tells me that the password is wrong.
in your macro password should be: ThePassWord
http://prikachi.com/images.php?images/742/7606742N.jpg
http://prikachi.com/images.php?images/743/7606743e.jpg
http://prikachi.com/images.php?images/744/7606744n.jpg
Maybe I wrong somewhere?
Thank you in advance
P.S - There's something else I do not see anywhere protect password, ie after hide rows should lock them and then my password wants to display rows and unlock the sheets?

[SamT]

"ThePassWord" is a variable. The value is set by the InputBox("Please write your password!")

In screenshot http://prikachi.com/images.php?images/742/7606742N.jpg use your real password for the workbook.

Also I make spelling error. Find "ThePassWrod" and change to "ThePassWord"

[Aussiebear]

I did two macro to hide and display rows in my worksheet. I've done two buttons and everything works incredibly well, I turned the internet to find a solution how to get them into a button and perform the same actions. Ie it pressed once to perform (execute) the first macro, it rang a second time to perform the second macro.


From my understanding of this section, you are seeking a boolean button effect to run the macro. That is, pressed once it caused one effect, and when pressed again, it causes another effect. My interpretation of your code is that you are seeking a method preventing some users, from seeing the formulas used in your workbook.


And another thing I do not know how to do - what must complete and how and where it can be implemented both macro I select sheets (for example, the action is for sheet names - "Moscow", "London" , "Italy", "New Zealand", etc.) with just this one button?

Again I'm struggling to understand what you are wanting here, is it that the sheets are known beforehand, or are you wanting to select the sheets on each occasion?

If you want to protect known sheets (say, "Moscow", "London", "Italy" & "New Zealand", then list them in an array as SamT has kindly shown you, otherwise you need to provide a method for the User to select on the run.

[SamT]

It's not hard to incorporate a toggle single sheet.


Sub poslednaproba()
Dim RowsToHide As String
Dim SheetsToUse As Variant
Dim TestSheet As String
Dim ThePassWord As String
Dim Result as long 'Add This declaration
Dim i As Long

RowsToHide = "4:5, 10:11, 13:14, 17:18, 34:35, 38:38, 45:46, 49:50, 72:73, 76:79"
SheetsToUse = Array("London", "Italy", "Paris", "UK", "Greece")
TestSheet = "London"

Application.ScreenUpdating = False

'Add the following code
Result = MsgBox(Click YES to toggle this sheet. Click NO to toggle all sheets. Click CANCEL to cancel action", vbYesNoCancel)
Select Case Result
Case vbOK
SheetsToUse = Array(ActiveSheet.Name)
TestSheet = SheetsToUse(LBound(SheetsToUse))
Case vbCancel
Exit Sub
End Select
'End Add Code Section

ThePassWord = InputBox("Please write your password!")

[k0st4din]

From my understanding of this section, you are seeking a boolean button effect to run the macro. That is, pressed once it caused one effect, and when pressed again, it causes another effect. My interpretation of your code is that you are seeking a method preventing some users, from seeing the formulas used in your workbook.



Again I'm struggling to understand what you are wanting here, is it that the sheets are known beforehand, or are you wanting to select the sheets on each occasion?

If you want to protect known sheets (say, "Moscow", "London", "Italy" & "New Zealand", then list them in an array as SamT has kindly shown you, otherwise you need to provide a method for the User to select on the run.
Hello Aussiebear
idea is the following of my excel file:
Assume that we are in London (UK), in England there are many cities.
We have 200 people working any products in every city (evenly distributed).
Several of us, however, work on special concrete products in the same cities that do not work on other people.
In a workbook we have many worksheets named with names of specific cities + other data.
Each of these worksheets in column A (in particular rows) are exactly the same only different cities (the names of the sheets).
At the end of each month, this file is sent to be examined by any person (representative).
In macros that I have shown and trying to do before I can send files to hide shown me lines (because there are products that should not be visible to others) and to lock all worksheets with names of cities.
Yes I have and I know the names of the worksheets to be hidden (rows) and locked worksheets.
In my macro so long (which is good for nothing) - the short version:
Only one button
macro to loop through all the worksheets with names of cities (I have to write it down in the macro what those cities) want to hide my rows and lock all worksheets (with city names).
Second macro - it unlock all the worksheets with the names of cities and unhide the hidden rows.

SamT
you write password of a workbook, and I do not have one (excel file is unlocked), I have (ie I want to have passwords worksheets - only selected sheets with names of cities).
I tried but your macro:
When I press the button once - as I I go up the word "write your password" then none of the sheets with names of cities not hide any mention of the desired row and lock them.
This is precisely why I asked if I wrong somewhere?
In the macro there were two mistakes that spelling is one that you mention, and the other (GotoUnHideSheets) and I fixed it for her (Goto UnHideSheets).
Please wholeheartedly look at my unworkmanlike macro that I put after hiding rows password worksheets and in your macro I dont see setting a password for worksheets.
Ie in the example that I gave put (write) password "kosta".
Thank you all for your support, I believe we can find a solution.
If you have more questions ask, I will answer, I just do not believe that I can create so many cares one macro.
Thanks again.

[k0st4din]

Hi friends,
I suspect maybe why you do not reply, but only as a tiny, tiny interjected want to ask you nice to look at whether the other place I answered, as there ask such a question that I answer something, somewhere and I know how I broke into my macro. Once my inquiry is the 10th page and no response, I decided to ask you, and we've just got to finish and almost no response.
Really ask for your understanding and look (read) that there is a close question, but only refers to a worksheet while on this site asking is another.
Begging your excuse but I do not think that I have broken the rules in some way.
I hope to be understood, otherwise I do not see why I should glow red.
With warmest greetings
If you still have any doubts, you can look for yourself, I do not have an answer.
It is nothing to do in this case - wait a while and decided to ask you.

http://www.excelforum.com/excel-programming-vba-macros/1036531-macro-to-hide-and-unhide-rows-but-requiring-a-password-to-unlock.html

[SamT]

Well, you had a password in your macro, so I put one in mine.

It should work if you just comment out or remove all references to a password.

[k0st4din]

SamT hello
I do not understand macros from clear to 1000%, in that there is absolutely no question but surely I do not understand or do not see - in your macro is incredibly made​​, but I can not see inside him where to enter the password (see himself that I was mistaken, that ThePassWord a password that it was not so).
It is true that I did the most stupid way that may exist, but in post number 9 each sheet where I hide rows then I locked him sheets.
And then when I open the workbook (again by button) unlock sheets and unhide rows show.
Maybe you missed it, no big trouble.
Let us alive and healthy.
Regards





Sub poslednaproba()
Application.ScreenUpdating = False
If Range("4:4").EntireRow.Hidden = False Then 'Sub hide
Sheets(Array("London", "Italy", "Paris", "UK", "Greece")).Select
Sheets("London").Activate
Rows("2:2").Select
Selection.Locked = False
Selection.FormulaHidden = False
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,4 9:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79").Select
Range("A79").Activate
Selection.EntireRow.Hidden = True
Range("A2").Select
Sheets("London").Select
ActiveSheet.Protect ("kosta"), ' Please look at these lines, see how I put a password on the sheets - here we do not do something in your macroDrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Sheets("Italy").Select
ActiveSheet.Protect ("kosta"), ' Please look at these lines, see how I put a password on the sheets - here we do not do something in your macroDrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("Paris").Select
ActiveSheet.Protect ("kosta"), ' Please look at these lines, see how I put a password on the sheets - here we do not do something in your macroDrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("UK").Select
ActiveSheet.Protect ("kosta") 'Please look at these lines, see how I put a password on the sheets - here we do not do something in your macro, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("Greece").Select
ActiveSheet.Protect ("kosta") 'Please look at these lines, see how I put a password on the sheets - here we do not do something in your macro, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("London").Select
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error Goto ErrHandler
Range("A2").Select
Sheets("London").Select
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation

Application.ScreenUpdating = True

Sheets("Italy").Select 'and here again foolishly trying to unlock and show rows
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("Paris").Select 'and here again foolishly trying to unlock and show rows
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("UK").Select 'and here again foolishly trying to unlock and show rows
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("Greece").Select 'and here again foolishly trying to unlock and show rows
ActiveSheet.Unprotect Password:=InputBox("Please write your password!")
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
Sheets("London").Select
Range("A2").Select
End If
End Sub




Sub poslednaproba()
Dim RowsToHide As String
Dim SheetsToUse As Variant
Dim TestSheet As String
Dim ThePassWord As String
Dim Result As Long 'Add This declaration
Dim i As Long

RowsToHide = "4:5, 10:11, 13:14, 17:18, 34:35, 38:38, 45:46, 49:50, 72:73, 76:79"
SheetsToUse = Array("London", "Italy", "Paris", "UK", "Greece")
TestSheet = "London"

Application.ScreenUpdating = False

'Add the following code ->>> please tell me which code might add here?
Result = MsgBox(Click YES To toggle this sheet. Click NO To toggle all sheets. Click CANCEL To cancel action", vbYesNoCancel)
Select Case Result
Case vbOK
SheetsToUse = Array(ActiveSheet.Name)
TestSheet = SheetsToUse(LBound(SheetsToUse))
Case vbCancel
Exit Sub
End Select
'End Add Code Section ->>>>please tell me which code might add here?

ThePassWord = InputBox("Please write your password!")

[Aussiebear]

Please attach a dummy workbook, with sheets for the cities you currently have. On the first sheet list those sheets which should be available if you know the password

[k0st4din]

ok, I'll do a sample workbook and attach it. from your letter I feel that you think that I do not know the password - it's not because I'm trying now to do this workbook and actually it has no passwords, ie I will ask these passwords and will fit in any I wish. in the above macros (which are not good for anything I have given password "kosta", but it is indicative. if we can do macro then I will change the password of the sheets (it will be the same for all sheets with names of cities). really thank you very much.

[k0st4din]

Hello everyone.
So - here is the sample file.
I have done it roughly the looks like my file.
In the original file I have many more cities and worksheets.
In orange I show which lines should be hidden in each worksheet with the name of the city and be locked.
Then worksheets with names of cities are unlocked and to show hidden rows - >>> as it wants to unlock password.
I am very grateful to you all.

[k0st4din]

Friends I beseech you for any assistance.

[Aussiebear]

Okay, so given that something is missing in the translation, please go through all of the posts.

1. Password protection. Sam gave you a great example on using a password to unhide those sheets you require.

2. The sheets to display. Sam also gave you an example of how to arrange the sheets you wish to display.

Please go back through the posts and indicate which posts relate to points 1 and 2.

[jonh]

Sorry, I haven't read all the posts. May have missed something.


'this workbook
Private Sub Workbook_Open()
'Startup hidden
HideUnhide
End Sub


'module
Dim hidden As Boolean

Public Sub HideUnhide()
If hidden Then
Unhide "kosta", "london", "ny", "paris"
Else
Hide "london", "ny", "paris"
End If
hidden = Not hidden
End Sub

Private Sub Hide(ParamArray sheetname() As Variant)
On Error Resume Next
For Each sht In sheetname
With Sheets(sht)
.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
.Protect ("kosta"), DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End With
Next
End Sub
Private Sub Unhide(pw As String, ParamArray sheetname() As Variant)
On Error GoTo ErrHandler
For Each sht In sheetname
With Sheets(sht)
.Unprotect Password:=pw
.Cells.EntireRow.hidden = False
End With
Next
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
End Sub

[k0st4din]

Okay, so given that something is missing in the translation, please go through all of the posts.

1. Password protection. Sam gave you a great example on using a password to unhide those sheets you require.

2. The sheets to display. Sam also gave you an example of how to arrange the sheets you wish to display.

Please go back through the posts and indicate which posts relate to points 1 and 2.

Hello Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear)
pleace read my post N= 12 and 16 -> for 1st question
(I think there I asked my questions accurately and exactly what I do not understand.)

pleace read my post N= 19 (VB2 my green text and question).
----------------------------------------------------------------------------------------------
jonh (http://www.vbaexpress.com/forum/member.php?47310-jonh)
: first steps to hide the desired cities and lock are great.
: the second step - unlock all desirable cities and unhide the hidden rows (Please read the green text in the macro (below))

Private Sub Unhide(pw As String, ParamArray sheetname() As Variant) On Error GoTo ErrHandler
For Each sht In sheetname
With Sheets(sht)
.Unprotect Password:=InputBox("Please write your password!") ' If I use this = pw - tells me directly "Wrong password", I tried to move in with my version displaying window and there to write the password to unlock all of the desired cities and then show hidden rows
.Cells.EntireRow.hidden = False
End With
Next
Exit Sub
ErrHandler:
MsgBox "Wrong password", vbExclamation
End Sub
12286
I'll be grateful if you show me how to do that, write the password only once to unlock them all and show rows.
Now also was prepared but I write password as many times as the designated cities

[jonh]

Your error handler says 'Wrong Password' for any error.
You need to make sure that it really is the password that is incorrect. Put a breakpoint on the code and step through it.

Also, unless you have different passwords for each sheet you need to put the inputboxes in HideUnhide(), otherwise it will pop up for every sheet.

[k0st4din]

No, no - maybe you do not understand me.
I attach the file and there is your macro.
Try it:
First - when you open the workbook - the selected sheets will be locked and rows will be hidden.
2nd - press the button to unlock all selected worksheets (you can see them later in the macro), and see the result - no unlocks them and does not show the hidden rows.
3rd - if I get this "= pw" and put "InputBox (" Please write your password! ")" For each worksheet selected (recorded) in the macro begins to climb my form and wishes of each worksheet entering a password (this well if i have different passwords).
but in my case for all selected worksheets I have only one single password - ie the same for all sheets - and I wish once you show the form, I write the password only once to unlock all sheets and show rows.
Please try the file.
I hope now to be able to understand it.
But if there is something - please ask boldly.
Thanks
Perhaps the quickest option that comes to mind as an explanation:
pressing the button
displayed form
write password (same for sheets -> "kosta", after that i will change passwor, this is for the sample file)
pressing OK
unlocks the sheets (only those who have found its way into the macro., not all)
shows rows
:)

[jonh]

Ok, try this.

ThisWorkbook

Private Sub Workbook_Open()
HideUnhide True
End Sub

Module


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

[k0st4din]

I'm probably the biggest stupid person.
Video shows you where you can see exactly what I do and again I can not see where to write my password to unlock these sheets.
And somehow one of the selected sheets (Plovdiv) is not activated by the macro and not know why?
He shows me the wrong password - and I think that is normal, because nowhere in the macro will set this, and when I have to write the password to unlock - it (the macro does not find one) and everything stays locked and remained hidden rows

http://www.videoklipove.com/video/5043/howtomp4/
(Can comfortably watch videos, no spam)
1 - I open the workbook
2 - all required worksheets are locked and hidden lines (with the exception of Plovdiv -> I do not know why he does not want the macro to act on this sheet). - Super, super ............ :clap:
The made button from you is great, the show / hide - I thank you infinitely. :clap:
And now come the problems (please watch the video) :banghead:

[jonh]

The first parameter of hide and unhide is the password.
For unhide you enter the password via the input box.
For hide it is hard coded. In the code I posted above the password is Plovdiv. Which is also a sheet name so I think you are confusing the two.

so you should change this line

If Hide("Plovdiv", "London", "NY", "Paris") Then

to

If Hide("whatever your password is", "Plovdiv", "London", "NY", "Paris") Then

++edit

basically for both, it's password and then the list of sheet names. (I've no idea why I used Plovdid as password)

[k0st4din]

Hello to All
finally, finally found a solution to my query.
I want to thank you all for your support and responsiveness. For your big hearts and a desire to help us in need.
Be healthy and always helps.
And do not **** off a lot - because we do not understand this macro. :clap::friends::bow::yes
Thank you once again.

[robinho0d]

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
Thank you in advance and if necessary please let me know if I should make a new topic.

[Paul_Hossler]

It would be better to start a new topic instead of tagging along on a 9 year old thread

You can refer to this one, but my preference would be to make the new one self contained so that it's easier for people to see what you're looking to do