sujittalukde
06-08-2007, 04:27 AM
I have found a macro code which if run will show a list of all sheets and u can directly jump to that sheet. However I need some modifications :
1. It shows very hidden sheets also I need that it should not show very hidden sheet.
2. Though it shows other hidden sheets also but if those sheets are chosen, it does not show those sheets. I want that if sheets are normally hidden than it should jump to that sheet only.
3. If the user cancel the dialog box, an error message come which I want that instead of showing error message, normal screen should come again if cancel button is pressed.
4. I want that only two sheets should be visible at any point of time. One sheet say ?instruction?s which by default will always be there in the file others one sheet should come only if the user selects from this macro code. Say sheet1. If in case when user will select other sheet by running the code say sheet 2, sheet 1 should again be hidden and user can see only sheet2 & Instruction sheet.
5. All these sheets will be password protected (Also Workbook protection will be there) & also contain some other macro buttons. When the sheets will be shown, it must be password protected and macro buttons should also appear.
A sample wb is attached for ready testing.
Sub Go2sheet()
myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)
Sheets(mySht).Select
End Sub
Password sets for worksheet & workbook is ?abcd?
1. It shows very hidden sheets also I need that it should not show very hidden sheet.
2. Though it shows other hidden sheets also but if those sheets are chosen, it does not show those sheets. I want that if sheets are normally hidden than it should jump to that sheet only.
3. If the user cancel the dialog box, an error message come which I want that instead of showing error message, normal screen should come again if cancel button is pressed.
4. I want that only two sheets should be visible at any point of time. One sheet say ?instruction?s which by default will always be there in the file others one sheet should come only if the user selects from this macro code. Say sheet1. If in case when user will select other sheet by running the code say sheet 2, sheet 1 should again be hidden and user can see only sheet2 & Instruction sheet.
5. All these sheets will be password protected (Also Workbook protection will be there) & also contain some other macro buttons. When the sheets will be shown, it must be password protected and macro buttons should also appear.
A sample wb is attached for ready testing.
Sub Go2sheet()
myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)
Sheets(mySht).Select
End Sub
Password sets for worksheet & workbook is ?abcd?