PDA

View Full Version : Solved: Choose sheet code



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?

sujittalukde
06-08-2007, 04:41 AM
Sorry I forget to attach te file

johnske
06-08-2007, 05:17 AM
This adds a "Show Sheet" control at the top of the "Ply" toolbar (you get to this control by right-clicking the worksheet tab), there's also provision for adding and deleting a sheet.

Note: This was intended to only show one sheet at a time and it's been cobbled together here to cater for two sheets - so it's untested in that form


Code for the ThisWorkbook code module...
Option Explicit
'
Private Sub Workbook_Open()
Call HideSheets
Call AddToolbar
End Sub
'
Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'//change name to suit\\
Const IntroSheet As String = "Sheet1"
'
Application.ScreenUpdating = False
Sheets(IntroSheet).Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = IntroSheet Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
Sheets("instructions").Visible = xlSheetVisible
Application.ScreenUpdating = True
End Sub
'
Private Sub AddToolbar()
Call RemoveToolbar
With Application.CommandBars("Ply")
With .Controls.Add(before:=1)
.Caption = "Show Sheet"
.OnAction = "ShowSheet"
End With
.Controls.Item(2).BeginGroup = True
'add a custom delete to intercept any ''delete'' action
.Controls("Delete").Delete
With .Controls.Add(before:=4)
.Caption = "&Delete"
.OnAction = "DeleteSheet"
End With
End With
End Sub
'
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.ScreenUpdating = False
For Each Sh In Sheets
If Not Sh.Name = ActiveSheet.Name Then
Sh.Visible = xlSheetVeryHidden
End If
Next
Application.ScreenUpdating = True
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveToolbar
End Sub
'
Sub RemoveToolbar()
Application.CommandBars("Ply").Reset
End Sub



This code goes in a standard code module...
Option Explicit
'
Private Sub ShowSheet()
'
Dim ThisSheet As String
'
Application.ScreenUpdating = False
ThisSheet = ActiveSheet.Name
'
If Application.Dialogs(xlDialogWorkbookUnhide).Show = True Then
If Not Sheets(ThisSheet).Name = "instructions" Then
Sheets(ThisSheet).Visible = xlVeryHidden
End if
End If
Application.ScreenUpdating = True
End Sub
'
Private Sub DeleteSheet()
'
Dim ThisSheet As String
'
Application.ScreenUpdating = False
ThisSheet = ActiveSheet.Name
'
If Not ActiveSheet.Name = "Sheet1" Then
Sheets("Sheet1").Visible = True
Sheets(ThisSheet).Delete
End If
Application.ScreenUpdating = True
End Sub

sujittalukde
06-08-2007, 05:28 AM
Not seeing the "show sheet" Also as my wb will contain other macro buttons and i want to add modified code to a button on the "Instruction" sheet ( as can be seen in the att file), so i need a code that will help me to do that. Also I'm not very much familiar with the toolbars macros. anyway, thanks for the reply

johnske
06-08-2007, 05:41 AM
It's 'Event code' based, you'll need to save, close and re-open the workbook to get it started :)

Bob Phillips
06-08-2007, 05:50 AM
It's 'Event code' based, you'll need to save, close and re-open the workbook to get it started :)

Or just run the Workbook_Open procedure from the VBIDE.

sujittalukde
06-08-2007, 06:10 AM
Thanks the efforts you all are giving, but still I have aome problem in running the code through toolbar code. Can this not be simply modified without the toolbar option?

Bob Phillips
06-08-2007, 06:28 AM
so how would you want to trigger these actions?

sujittalukde
06-08-2007, 09:38 PM
In a very simple manner as shown in the attached file in my start/first post

sujittalukde
06-10-2007, 09:47 PM
In case it is tedious to modify the code for the above requirement can this be modify to suit this:
1. It will not the very hidden files in the list
2. It will show hidden sheets.

rbrhodes
06-11-2007, 11:59 PM
Hi sujittalikde,

Try this. If the user selects sheet Instructions it hides all others. Otherwise it does exactly as you requested (I think)...

cheers,

dr

sujittalukde
06-12-2007, 12:04 AM
Thank You rbrhodes!
Many many thanks ! This is what I was exactly looking for.

rbrhodes
06-12-2007, 01:21 AM
You're welcome! However you might want to add the (simplified) menu code. Look at this example for the idea. Right click any Sheet Tab or right click any cell.


Menu idea from johnske


Cheers,

dr

sujittalukde
06-12-2007, 01:38 AM
Wow! Thats really great Thanks