Custom Menus

[sburgess]

I'm working with another dept on setting up a Tracking Document. They have set up a custom menu, where you have the option to select several different custom views. On one of the custom views, I want it to ask for a password, then open up the view. When the workbook is closed, then it should go back to the original view, where there are several columns hidden. So, when the workbook is reopened, it should open it in the default view (with columns hidden and locked).

I have tried just using Tools-Protection-Protect Sheet, and have locked those columns and have hidden the columns, and have saved the document. But, when I have chosen the custom view, it doesn't show the view correctly.

The Custom Views were set up with VBA, and I need some help trying to figure out the rest of the coding.

[jproffer]

OK.....so what do you have so far? Throw us a bone here, lol......but we have to have something on which to base "the rest of the coding".

[sburgess]

My apologies.... unfortunately, I only have so much of the coding. I can copy what i have. For the custom view, I can't seem to bring that up in VBA. Whenever I try to run the macro, it brings me into VBA, but won't show me anything. I found where the coding is, but when I try to click on it to view, it shows me nothing. So, I'm at a loss. I will try to see if I can find something. Here is the code that calls out the custom menu. The one that I'm interested in is the 4th menu item... When I try to do a find on "PRView", again, it takes me to the module that has the info, but there's nothing there. So, I don't know if anyone can help or not. I will try to find that coding. I do appreciate anyone's help with this.

[VBA]Sub CreateMenu()
Dim helpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
' Delete the menu if it already exists
Call DeleteMenu

' Find the Help Menu
Set helpMenu = CommandBars(1).FindControl(ID:=30010)

If helpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)



Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Before:=helpMenu.Index, _
temporary:=True)
End If
' Add a caption for the menu
NewMenu.Caption = "&Progress Tracker Tools"


' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Launch Tracker Update Menu Service..."
.FaceId = 2115
.OnAction = "Tums"
End With

' Second MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Launch Financial Data Update Portal..."
.FaceId = 395
.OnAction = "Fdup"
End With

' Third MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "TTO Report View"
.FaceId = 2522
.OnAction = "TTOview"
End With

' Fourth MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Progress Report View"
.FaceId = 2517
.OnAction = "PRview"
End With
' Sixth MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&About This Tracking Document..."
.FaceId = 487
.OnAction = "Macro5"
End With



End Sub
Sub DataEntryMacro()
MsgBox "Hello from the data entry macro"
End Sub
Sub Macro1()
Call Open_Update_Formatting_Form
End Sub
Sub Macro2()
Call hyperlinks
End Sub
Sub Macro3()
' Call setup_UpdateLinks_Form
Call update_links
End Sub
Sub Macro4()
Call Update_MSO
End Sub
Sub Macro5()
SplashForm.Show
End Sub

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("Progress Tracker Tools").Delete
End Sub
[/VBA]

[sburgess]

Okay - here is the coding that I'm stuck on.... when the document is closed it needs to run this code, rehide the columns that it unhid, and lock them and protect the worksheet again. When I reopen the workbook, and try to go to that custom view, it's not asking for a password, as it does not seem to be running the code. any help would be great!
[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
'On Error Resume Next

Columns("CAR").Select
Range("CA9").Activate
Selection.EntireColumn.Hidden = True
ActiveWorkbook.CustomViews("Initial View").Show
ActiveSheet.Name = "table"
ActiveSheet.Protect Password:="test"

[/vba]