command bars

[keks]

Hi again!
i spent an hour trying to resove this but it seems i won't do without your help

i have MyBook.xls that contains a sheet named "MySheet" and a command bar named "MyCommandBar". I want MyCommandBar to be visible only when the user sees MySheet, and respectively to be hidden all other time. I tried the code below but it doesn't help when i switch from MySheet in MyBook.xls to SomeOtherSheet in SomeOtherBook.xls. In this case MyCommandBar remains visible in SomeOtherSheet.
Please help me coorect my code or suggest your own. big thanks in advznce

[placed in ThisWorkbook]
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

If ThisWorkbook.Name = Application.ActiveWorkbook.Name And Application.ActiveSheet.Name = "MySheet" Then
Application.CommandBars("MyCommandBar").Visible = True
Else
Application.CommandBars("MyCommandBar").Visible = False
End If

End Sub

 

[staticbob]

Hey Keks,

Try hiding the toolbar on worksheet deactivate event. You should be able to do that from the code you have already.


EDIT - In fact, just use code on the sheet in question, not workbook . .

EDIT 2 - Copy the code to hide onto workbook_deactivate !


Private Sub Worksheet_Activate()
Application.CommandBars("Chart").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("Chart").Visible = False
End Sub
Cheers
Bob

 

[Richie(UK)]

Hi Keks,

OK, I think the following should cover all of your objectives. As Bob has indicated, you need to use the sheet Activate and Deactivate events to switch the visibility while your workbook is active.

In a general module (named modMenu):

Const cCommandBarID As String = "MyCommandBar"
Const cBtnPop1Sub As String = "Pop1"
Const cBtn1Sub As String = "Btn1"
Const cBtn2Sub As String = "Btn2"
Const cBtn3Sub As String = "Btn3"
'
Sub CreateCM()
    Dim cbBar As CommandBar, cbPop As CommandBarPopup
    Dim cbPop2 As CommandBarPopup, cbCtrl As CommandBarControl
    
    DeleteCM
    Set cbBar = CommandBars.Add(Name:=cCommandBarID, Position:=msoBarTop)
    
    Set cbPop = cbBar.Controls.Add(msoControlPopup)
    'create new commandbar control (popup menu type) on custom menu
    With cbPop
        .BeginGroup = True
        .Caption = "Cats && Dogs"
        .Tag = "MyMenu"
    End With
    
    Set cbPop2 = cbPop.Controls.Add(msoControlPopup)
    'popup on main popup
    With cbPop2
        .BeginGroup = True
        .Caption = "Pop menu :"
        .Tag = "MyPop"
    End With
    
    'add to pop
    Set cbCtrl = cbPop2.Controls.Add(Type:=msoControlButton)
    With cbCtrl
        .OnAction = cBtnPop1Sub
        .Style = msoButtonCaption
        .Caption = "Pop1 Sub"
    End With
    
    'add various sub-menus
    Set cbCtrl = cbPop.Controls.Add(Type:=msoControlButton)
    With cbCtrl
        .OnAction = cBtn1Sub
        .Style = msoButtonCaption
        .Caption = "Button1"
    End With
    
    Set cbCtrl = cbPop.Controls.Add(Type:=msoControlButton)
    With cbCtrl
        .OnAction = cBtn2Sub
        .Style = msoButtonCaption
        .Caption = "Button2"
    End With
    
    Set cbCtrl = cbPop.Controls.Add(Type:=msoControlButton)
    With cbCtrl
        .OnAction = cBtn3Sub
        .Style = msoButtonCaption
        .Caption = "Button3"
    End With
    
    cbBar.Visible = True
    
    Set cbBar = Nothing
    Set cbCtrl = Nothing
    
End Sub

Sub DeleteCM()
    Dim cbBar As CommandBar
    
    On Error Resume Next
    Set cbBar = CommandBars(cCommandBarID)
    On Error GoTo 0
    If Not cbBar Is Nothing Then cbBar.Delete
    Set cbBar = Nothing
    
End Sub

Sub CMVisibility(showme As Boolean)
'routine to switch visibility of the custom menu

    On Error Resume Next
    'in case its already gone
    CommandBars(cCommandBarID).Visible = showme
    'change the visibility status

End Sub

and in a general module (named modSubs):

Sub Pop1()
    MsgBox "Pop1 code here"
End Sub

Sub Btn1()
    MsgBox "Btn1 code here"
End Sub

Sub Btn2()
    MsgBox "Btn2 code here"
End Sub

Sub Btn3()
    MsgBox "Btn3 code here"
End Sub

In the sheet event code (for Sheet1 in this example):

Private Sub Worksheet_Activate()
    modMenu.CMVisibility (True)
End Sub

Private Sub Worksheet_Deactivate()
    modMenu.CMVisibility (False)
End Sub

And finally, in the ThisWorkbook object:

'Module level declarations
'
'IsClosed is used to detect whether the user selects Cancel
'when the workbook is being closed and it hasn't been saved
'
'IsOpen is used to detect whether the workbook has just been
'opened or is simply being reactivated
'

Dim IsClosed As Boolean, IsOpen As Boolean

Private Sub Workbook_Activate()
    IsClosed = False
    If IsOpen = False Then
    'this is not the the first activation after the
    'workbook is opened, ie its a reactivation
        If ActiveSheet.CodeName = "Sheet1" Then modMenu.CMVisibility (True)
        'call the routine to switch visibility of the custom menu
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'when a workbook is being closed, if there have been changes and
'the workbook hasn't been saved Excel will prompt the user with
'a warning message "Do you want to save the changes ..."
'If the user selects Cancel we do not want to delete the menu, but
'you can't actually detect the 'Cancel', this is a parameter
'that is passed to the event (see Case Is = vbCancel section)
'therefore, if the workbook has not been saved we add our own message
'to the BeforeClose event (see below)
    Dim ans As Integer
    
    If Not ThisWorkbook.Saved Then
        ans = MsgBox(prompt:="Do you want to save the changes to " _
        & ThisWorkbook.Name, _
        Buttons:=vbInformation + vbYesNoCancel, Title:="Microsoft Excel2")
        Select Case ans
            Case Is = vbYes                 'user wants to save
                IsClosed = True             'closing so set to True
                ThisWorkbook.Save           'save workbook
            Case Is = vbNo                  'user doesn't want to save
                IsClosed = True             'closing so set to True
                ThisWorkbook.Saved = True   'don't save workbook
            Case Is = vbCancel              'changed their mind so don't close
                IsClosed = False            'changed their mind, set to False
                Cancel = True               'cancel the closing process
                Exit Sub
        End Select
    End If
End Sub

Private Sub Workbook_Deactivate()
    IsOpen = False
    'set to false so that when the workbook is reactivated the
    'Activate event will know that the workbook hasn't just been opened
    If IsClosed = True Then
    'the workbook is being closed (as determined by the BeforeClose event)
        modMenu.DeleteCM
        'call the routine to delete the custom menu
    Else
    'the user has only activated another Workbook
        modMenu.CMVisibility (False)
        'call the routine to switch visibility of the custom menu
    End If
End Sub

Private Sub Workbook_Open()
    IsClosed = False
    IsOpen = True
    'initialise the IsClosed and IsOpen variables
    modMenu.CreateCM
    'call the routine to show the custom menu
End Sub

You may need to tinker with the code a little to suit your chosen menu layout, but the basic layout should help you.

Thanks to Dave Hawley for providing the initial idea and to Tushar for suggested amendments.

HTH

 

[keks]

Wow it would take a minute to think coup with it

 

[keks]

i finally added 2 more of my initial codes in the ThisWorkbook object under Workbook_Deactivate() and Workbook_Activate(). now it looks as below and works all right! many thanks, guys!


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

If ThisWorkbook.Name = Application.ActiveWorkbook.Name And Application.ActiveSheet.Name = "MySheet" Then
Application.CommandBars("MyCommandBar").Visible = True
Else
Application.CommandBars("MyCommandBar").Visible = False
End If

End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next

If ThisWorkbook.Name = Application.ActiveWorkbook.Name And Application.ActiveSheet.Name = "MySheet" Then
Application.CommandBars("MyCommandBar").Visible = True
Else
Application.CommandBars("MyCommandBar").Visible = False
End If

End Sub

Private Sub Workbook_Activate()
On Error Resume Next

If ThisWorkbook.Name = Application.ActiveWorkbook.Name And Application.ActiveSheet.Name = "MySheet" Then
Application.CommandBars("MyCommandBar").Visible = True
Else
Application.CommandBars("MyCommandBar").Visible = False
End If

End Sub