Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Right-Click Conditional Formatting (97 / 2000)

    One of my top irritating issues in the EXCEL GUI is the absence of the "Conditional Formating" option if I right-click on a cell <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>.

    Anybody who knows if someone has programmed a functionality like this? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

    I'd appreciate a copy of the source: it would be used daily <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Erik Jan

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    The command bar id for conditional formatting is 3058

    HTH

    Brooke

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    CommandBars("Cell").Controls("Conditional Formatting").Delete
    With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton, 3058)
    .Caption = "Conditional Formatting"
    End With
    End Sub

    HTH

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    Neat, Bob! Where and how would I attach this code so it is available Application-wide? (I might just go nuts with the Excel cell right-click short-cut menu.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    You can do it for all the sheets in a workbook by using Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    (code same as above)
    End Sub

    and it'll work for any sheet. To have it work for any workbook you'd have to use a class module and I'm just not up to speed on how to do this. Alternatively, you can create a book.xlt template which has this code in it and save it in your xlstart, so that every NEW workbook will have the functionality.

    HTH

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    OK, here's how you can do it for all workbooks created/opened (I decided to get smarter!)
    In a new workbook, go to the VBE, add a class module, name it, say, clsAllApps, and enter this:
    Public WithEvents App As Application


    Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    App.CommandBars("Cell").Controls("Conditional Formatting").Delete
    With App.CommandBars("Cell").Controls.Add(msoControlBut ton, 3058)
    .Caption = "Conditional Formatting"
    End With
    End Sub

    In the "Thisworkbook" project, add this code:
    Dim App As New clsAllApps
    Private Sub Workbook_Open()
    Set App.App = Application
    End Sub

    Back in Excel, save the workbook as something, say CondFmt, and PUT IT IN THE XLSTART Directory.
    Then, to keep it hidden (since it will always open), in the immediate pane type
    thisworkbook.save

    That should do it!
    Thanks for the exercise, and good luck with your adding stuff to the right-click!

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    Now here's a mystery... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> After the first reply to my original request, I of course immediately copied the code over to EXCEL and was also thrilled: it worked <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    Then, as I found it a bit strange to delete & re-create a menu-entry on each right mouse click AND to implement this code for each sheet (which was later covered by another reply)... I removed the code from my sheet (in fact I just closed the test sheet without saving).

    Now guess what happens: the right click sticks!!! <img src=/S/warts.gif border=0 alt=warts width=59 height=24> Even if I fully close down EXCEL and restart it, it is still there.

    To me (but who am I), it seems as if adding the command to the COMMANDBARS("Cell") collection ONCE is enough. Unless you remove it, it seems to stay. Moreover, it seems to have nothing to do with activecells etc.

    So, run once, always happy??? <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    Anyone who can reproduce??

    Erik Jan

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    Sorry, I should have given you more. It sticks for me too - the sub Bob gave you is the same as manually customising a toolbar, which is stored in your xlb file (I think) - well, I have paste special and freeze panes, but it's the same principle. If you want to play with the position use .before, draw a line use .begingroup. Also, if you give a commandbar a commandbar id (as opposed to a faceid) you don't need the .caption: the code below is what I use:

    Sub brooke_modify_menu21()
    'On Error Resume Next
    With Application.CommandBars(21).Controls
    With .Add(msoControlButton)
    .id = 3058
    .begingroup = true
    '.before = 5
    End With
    End With
    End Sub

    if you are going to play around, the following may be useful to you:

    Application.Commandbars(21).Reset


    One other point: you probably won't be getting your conditional formatting showing when you right click a row or column header - that's because they are three seperate menu's that just happen to normally look the same - cell is 21, column 22 and row 24. this means that I have freeze panes on cell and row but don't have to bother with it on column. If you look you'll see MS have done the same with insert comment, as you can't insert one on a row or column label.

    Bob, do you know what 25, 26 and 27 (also called cell row and column are for? I've always assumed they were for pivot tables - is this correct? and am I also correct in my assumption that if there are two cb's of the same name, it's the lower index that is affected if you call it by name?


    Brooke

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    Thank you all. I will no doubt spend my weekend trying to understand this stuff! I will also be examining some Application Level Event code published by Rob Bruce to help me along. Thanks again.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    This is great! Coolest little trick I have seen in a while. Is it possible to add your own macro/code to the right click menu?

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=40384&page=&view= &sb=&o=&vc=1>yes</A>

    Brooke

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    Brooke, if I understand it, your code sets the menus ("Cells", or 21, in this case) without any need for setting up a class module, correct?

    Any idea where I can find a FaceId directory/list? My several minutes looking in Excel VBA Help did not locate one.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    You understand correctly - I'm not clever enough to get involved with class modules(doesn't mean I wouldn't like to be!) Attached is a .bas of the module I keep the code in. The code is similar to the faceid code that you occaisionally see variants of posted. I based mine on j walkenbachs originally, though I think it's fairly different these days, and you can have both for the price of one. The main difference between the two is the on error statement - the id routines will fall over. I'm posting as is and if anyone has any comments then please feel free.kick them off by calling bcrtfaceidset1 or bcrtidset1 . Below is code that I think came from here or the old lounge that tells you what toolbars are currently in your system and gives you their commandbar id - always useful.

    Sub ComBarList()
    For Each ComBar In CommandBars
    Debug.Print ComBar.Name, ComBar.NameLocal, ComBar.Visible, ComBar.Index
    Next
    End Sub
    Sub ComBarListVBE()
    For Each ComBar In application.VBE.CommandBars
    Debug.Print ComBar.Name, ComBar.NameLocal, ComBar.Visible, ComBar.Index
    Next
    End Sub



    HTH - any questions, just ask

    Brooke
    Attached Files Attached Files

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    <not clever enough to get involved with class modules>

    I doubt it very much Brooke

    Andrew C

  15. #15
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Right-Click Conditional Formatting (97 / 2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Gee, thanks - that's the nicest thing anyones said to me in a long time!

    Brooke

Page 1 of 3 123 LastLast

Posting Permissions

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