Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selectively Deactivate Menu Command (Excel 97, SR-2)

    Hi all and thanks for help,

    I'm new to working with events and can't figure out which one will do the following: if any cell/range is selected, enable a menu command I've added. If something other than a cell is selected, disable the command. I already have the code to do it, just don't know where to put it. Have consulted three books with no luck. Any suggestions?

    Thanks,
    Howard

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    Check out the SelectionChange event for the worksheet. It fires every time the selection changes (click on or tab to another cell).

    Name your range or use cell refs to test if the range in question has been "changed to", etc. etc.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    Kevin,

    Thank you for the suggestion but, unless I'm doing something wrong (which is a distinct possibility), the SelectionChange event seems to occur only when the selection changes from one cell/range to another cell/range. I need to detect when the selected item is NOT a cell/range. The add-in I have designed is only applicable to cells/ranges, so to leave it active when a chart is selected is counter-intuitive. I would like to work like the AutoSum button, for example. When a cell/range is selected, AutoSum is active and enabled. However, when a chart is selected, AutoSum is deactivated and disabled.

    I know that I can use TypeName(Selection) to what it is that's selected (range, chart, drawing object, etc). I can't figure out how to get Excel to test the selected item when the selection changes though.

    Any other way to do this?

    Howard McDonald

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    It might be easier to add a message box to your procedure informing the user that the command does not work in the present context if the type is not what you want. Not as slick, but a short term workaround, and less of a performance drag than constantly monitoring the selection.

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    SelectionChange is not very good. A user could click a Chart and then click in the same cell as was selected before they clicked the Chart. The SelectionChange would not fire in thiscase.

    See if this helps. The example is hardcoded for a Chart on Sheet1 and a user defined commandbar called 'Test' , having a menu item called 'MyMenuItem'.
    This will toggle the MyMenuItem's Enable property.
    When the Chart is selected, it will dim the menu item.
    When it is deactivated (a cell has focus) the menu item is enabled.

    Add a Class Module to your workbook. (make sure its a Class Module and not a standard Module)
    Change the name of the Class Module to "EventClassModule".

    Put these three subs In the Class Module;
    Public WithEvents myChartClass As Chart '<--this is in the general declarations.

    Private Sub myChartClass_Deactivate()
    ToggleMenuItem True
    End Sub

    Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    ToggleMenuItem False
    End Sub

    Private Sub ToggleMenuItem(blnON As Boolean)
    'This example has a menu bar called "test" with a menu item called "MyMenuItem"
    'change this to fit your menu bar needs.
    Application.CommandBars("test").Controls("MyMenuIt em").Enabled = blnON

    End Sub

    Now open the code window for Sheet1 and place this code there;

    Dim myClassModule As New EventClassModule '<--this is in the general declarations.

    Public Sub InitializeChart()
    'You must run this first in order for this to work.
    ' this could be in the auto start (autoexec) whatever of the workbook.
    Set myClassModule.myChartClass = _
    Worksheets(1).ChartObjects(1).Chart
    End Sub

    After running the InitializeChart sub you should be able to see the menu item toggle enable/disabled.
    I don't know all the technical stuff but by creating the "EventClassModuel" and delcaring myChartClass
    as Chart with the "Public WithEvents" you get a fistfull of events to trap on. Check it out once you have the class module set up. Select myChartClass from the Object combo on the left and then check the combo on the right for all the events available. (mouse click, mouse move, etc...)

    If you had more than one chart on a page I imagin you could create a user defined collection of them and still make this work. Haven't tried but I would think this would work with other objects too.

    Later,
    Bruce

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    I agree. I had it set up sort of like this (No dialog box, just Exit Sub) this but figured there might be an event that I could use to be a little more sly. Oh well. Thanks for the suggestion.

    Howard.

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    Bruce,

    Hmm, I'll have to work on this as I'm learning as I go. I don't, right now, understand a lot of what you've posted here but I'll research it and see what it will let me do.

    Thanks for your time and suggestion.

    Howard

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    OK, How about an example. Attached is a sample spreadsheet with a chart.

    Note that the code in this spreadsheet is a bit different than my earlier post but the concept is the same.
    The main difference is an additional call for the Workbook module WorkSheet_activate.
    This covers the situation of the user moving from one sheet to another. (One sheet has the chart selected the other does not(or chart doesn't exist). Also the ToggleMenu has been changed.

    If user selects the chart, the menu item is disabled.
    If user selects a cell (chart looses focus) the menu item is enabled.

    Later,
    Bruce
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Nov 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectively Deactivate Menu Command (Excel 97, SR-2)

    Bruce,

    Thanks - I'll definately take a look at this when I get a chance. Thanks again.

    Howard

Posting Permissions

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