Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Create an AutoCalc on right click. (Excel 2000 >)

    Hi,

    Am am playing around with an idea that I would like to create in Excel.

    The Idea:
    I would like to select a range of values, right click and in the shortcut menu (below Paste Special) have a option called AutoCalc. If I choose this option, it must automatically insert the sum of the selected values in the cell below the list of values.

    I know that this is similar to autosum, but I am more interested in the construction of the code than the functionality of a simple autosum operation.

    Could I get some of the VBA gurus to point me in the right direction regarding some sample code to do this.
    Tx

    Included is a sample file with a VERY simple macro.
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    If your range contains more than one column, your macro will insert the sum of the entire range into each cell in the row below the range.
    Do you want your AutoCalc feature to work in ranges of one column only, or would you like it to work in ranges comprising multiple columns too?
    Your macro will insert the sum as a constant value. Would you prefer it to be a SUM formula instead?

    If you want the macro to be available in one workbook on;y, you can store it in that workbook.
    If you would like it to be available in all workbooks, you'd have to store it in Personal.xls or in an add-in (.xla).

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    Wow...you certainly have the end in mind!

    The AutoCalc must work in ranges comprising multiple columns and ONLY columns.
    I would prefer it to be a SUM formula. This means it will continue to recalculate.

    For now, it can stay in one workbook. Once the code is established I can save it as a .xla file.

    PS: I am planning to include AVG, MIN, MAX into the right click too....but for now the SUM is the one I am interested in.

    Tx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    See the attached workbook. I have modified the SAutoCalc macro, and added the following:
    - A macro CreateContextItem that creates an 'AutoCalc' item in the right-click menu for a cell. The context item executes SAutoCalc.
    - A macro DeleteContextItem that deletes the 'Autocalc' item.
    - Event procedures for the Workbook_Open and Workbook_BeforeClose events in ThisWorkbook that call the above macros.

    Note: you still don't require explicit declaration of variables - there was no Option Explicit at the top of the module! <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    This is great Hans. It is a wonderful bit of foundation code that i can use to continue to develop. The context menu code is very interesting. Set cbr = Application.CommandBars("Cell") is one of those lines. CommandBars("Cell") is not in the list of CommandBars in Tools | Customise. Is there more of these hidden command bars that you know of. Is CommandBars("Cell") specific to reference the shortcut menu?

    PS: About Option Explicit. I guess I do NOT have an excuse, but I do more from PC to PC very often at the office, and the settings in VBA to have Variable Declaration is not always set on the PC I may be working at. Also, the code I set in the sample was just a quick compilation which I did not put too much emphasis on.

    Sorry anyways! <img src=/S/sad.gif border=0 alt=sad width=15 height=15> and thx for the rap on the knuckles again.

    Cheers for the code!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    Context (right click) menus in Excel can only be manipulated in VBA code, they cannot be edited using Tools | Customize.
    The Cell command bar is the context menu for worksheet cells. There are many others. You can list all command bars:

    Sub ListCommandBars()
    Dim i As Integer
    Cells.ClearContents
    Cells(1, 1) = "Name"
    Cells(1, 2) = "Type"
    Range("A1:B1").Font.Bold = True
    For i = 1 To Application.CommandBars.Count
    Cells(i + 1, 1) = Application.CommandBars(i).Name
    Select Case Application.CommandBars(i).Type
    Case msoBarTypeMenuBar
    Cells(i + 1, 2) = "Menu Bar"
    Case msoBarTypeNormal
    Cells(i + 1, 2) = "Toolbar"
    Case msoBarTypePopup
    Cells(i + 1, 2) = "Popup Menu"
    End Select
    Next i
    Columns("A:B").AutoFit
    End Sub

    (Warning: this code will clear the entire worksheet before creating the list, so be careful)

    PS I work on different PCs too, but I have a roaming profile, i.e. my user settings are stored on the network. Since 'Require Variable Declaration' is a user-level setting, it is 'on' wherever I log in.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    Tx for the code...its going into my archive of Nice Codes...

    PS: That is an interesting statement, about a roaming profile. Is this set up using the Save my Settings Wizard in the Office Folder of the Start Menu??

    Sorry for all the questions this morning. Being away from WOPR for the while I have, I have gathered up a list of questions. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    A roaming profile is not specific to MS Office. It means that your profile Cocuments and Settings<username> (except for the Local Settings subfolder) is copied to the network when you log out, and copied (or merged) to each network PC where you log in. You cannot set this up yourself, it must be done by the network administrator.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    OK. Tx. Its an extremely interesting concept and will really suite my line of business. I must search for more info on this.
    If you know of more info about how to do this, please let me know, but do not go out of your way to type out any procedures!!!

    I am going to Google this and see what I can come oup with!
    TX
    Regards,
    Rudi

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    It seems I was wrong - it appears you *can* change the profile type yourself (but I haven't tried it). See MSKB article How to Create and Copy Roaming User Profiles in Windows XP.

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    I would have thought this profile would only adjust windows settings, like desktop, printer and network settings. Are you sure that it includes Office settings too? Things like Toolbars, VBA options, Formatting Options, Templates...etc???
    Regards,
    Rudi

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    Items such as templates, Personal.xls etc. are files stored in subfolders of the Application Data subfolder of your profile, so they are available wherever your log in if you have a roaming profile.
    The user-level settings for most applications are stored in the HKEY_CURRENT_USER part of the Windows Registry. This is actually a file NTUSER.DAT in your profile, so these settings travel with you too.

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create an AutoCalc on right click. (Excel 2000 >)

    Tx. That clears it up!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

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