Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple code needed (2K)

    Hi,
    As I'm not fluent in Excel code I need some assistance.
    I have a command button that I would simply like to add a value to a cell with. So when its clicked it adds one to that cell.
    Is it possible to have this as a generic button as I need to duplicate this function for several cells.

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

    Re: Simple code needed (2K)

    If you want the button to add 1 to the value of the active cell (wherever that cell is), try this:

    Sub AddOne
    On Error Resume Next
    ActiveCell = ActiveCell + 1
    End Sub

    In Excel, select Tools | Customize, create a custom toolbar button and assign this macro to it.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple code needed (2K)

    Sorry, not too sure how to assign a macro to a toolbar.

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

    Re: Simple code needed (2K)

    Select Tools | Customize... (or right-click a toolbar and select Customize...)
    If you want to create a new toolbar, activate the Toolbars tab, click New... and specify a name (for example Nigel's Macros, or whatever you like).
    Activate the Commands tab.
    Select Macros in the list of Categories on the left hand side.
    Select Custom Button in the list of Commands on the right hand side.
    Drag it to a convenient location on a toolbar, and drop it there.
    Right-click the new toolbar button to
    - Change its name.
    - Change the button image.
    - Assign a macro.
    Finally, close the Customize dialog.

    If you want to remove a toolbar button, select Tools | Customize again, then drag the button away from the toolbar area and drop it.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple code needed (2K)

    Thanks Hans.
    Sorry one final question. Is it possible to make the toolbar follow the cell.
    So for instance the user clicks in B5, the toolbar moves to this area.
    Then D6, again the toolbar moves.

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

    Re: Simple code needed (2K)

    As far as I know, toolbars don't work that way. You can add an item to the right-click menu for a cell, however. This menu will always pop up near the cell.

    Unlike custom toolbar buttons, you cannot create custom items in the right-click menu in the Excel interface. You must use VBA code:

    Sub CreateShortcutItem()
    With Application.CommandBars("Cell").Controls.Add(Type: =msoControlButton)
    .Caption = "&Increment"
    .OnAction = "AddOne"
    .Visible = True
    End With
    End Sub

    You only need to run this code once. If you right-click on a cell, you'll see a new Increment item at the bottom of the popup menu.

    If you want to remove the custom item, run the following macro:

    Sub RemoveShortcutItem()
    On Error Resume Next
    Application.CommandBars("Cell").Controls("&Increme nt").Delete
    End Sub

Posting Permissions

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