Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Intercept Excel view change (Excel 2000 SR1a)

    I want to run a VBA routine when a user selects the Excel menu option View > Custom Views > xxx > Show, how do I intercept this command?

    StuartR

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Excel view change (Excel 2000 SR1a)

    Since no worksheet events fire when changing custom view, I would suggest creating your own macro that lets the user choose a view and subsequently does the things you would like to do after that. Something like this:

    Sub ChangeView()
    Application.Dialogs(xlDialogCustomViews).Show
    MsgBox "Now do other stuff"
    End Sub

    Sub SetMenuOfChangeViews()
    With Application.CommandBars.FindControl(, 950)
    .OnAction = "changeview"
    End With
    End Sub

    And to stop:

    Sub ResetMenuOfChangeViews()
    With Application.CommandBars.FindControl(, 950)
    .Reset
    End With
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Intercept Excel view change (Excel 2000 SR1a)

    Jan,

    > I would suggest creating your own macro

    I have done that. My problem is that my Macro is fired from a drop-down-list on the first page of the workbook. If I use Tools > Customise to add the Custom View control to a toolbar then this control is updated when I change the view from my control. If I change the view from the built in control then my list box is not updated.

    I want to change the displayed value in the Cell that shows the current view if the user changes the view from the standard Excel menus.

    StuartR

    I have now read your post more carefully and I understand how this could help, by preventing the user from using the built in control at all

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Excel view change (Excel 2000 SR1a)

    <hr>I have now read your post more carefully and I understand how this could help, by preventing the user from using the built in control at all <hr>

    Exactly.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Intercept Excel view change (Excel 2000 SR1a)

    Nearly there,

    After I Show the dialog box it seems to return True if I click the Show button, and false if I do other things.
    Any idea how I get the name of the Custom View that is selected when the Show button is clicked?

    StuartR

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Intercept Excel view change (Excel 2000 SR1a)

    I thought this was too easy.

    The FindControl returns a control. Type 4 (ComboBox).
    If I try to view or change the .OnAction of this control then I get "Run-time error `438': Object doesn't support this property or method
    I can find the selected View by accessing .List(.ListIndex) for this ComboBox Control, but I can't see how to access the .OnAction property of the SHOW button.

    StuartR

    Edited by Stuart after a bit more playing
    This is very strange.

    Application.CommandBars(1).Controls(3).Controls(8)
    Appears to be the correct control. It has a type of 1, a caption of "Custom &Views..." and an ID of 950
    I can set the .OnAction property of this control and it does what I expected.
    This control does not have a .List property

    If I use FindControl to find control with an ID of 950 then it returns a control of type 4, ID 950, caption "Custom &Views..."
    This control doesn't have a .OnAction property, but it does have a .List that lets me access the list of views.

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intercept Excel view change (Excel 2000 SR1a)

    The control from the View menu does not have a list property because it is just a commandbarbutton that shows the custom views dialog.

    There are (at least) TWO controls with Id 950: a commandbutton (in the View menu) and a combobox (the one on a toolbar). The commandbutton -of course- has no list property, the combobox does.

    To find the control you want:

    This finds the one in the worksheet menubar:

    Sub test()
    With Application.CommandBars(1).FindControl(ID:=950, recursive:=True)
    MsgBox .Type
    End With
    End Sub

    And this finds the other one and shows the selected view:

    Sub test()
    With Application.CommandBars.FindControl(ID:=950)
    msgbox .list(.listindex)
    End With
    End Sub

    You would have to find all occurances of the custom views dropdown and commandbutton and make sure each button points to your macro. I would disable all dropdowns and put a button on a dedicated toolbar that is attached to your macro.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •