Results 1 to 6 of 6

Thread: Macro Button

  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Button

    Hi all,

    I want to create a Button in Excel that will be able to Paste Special... as Values the range of cells that I have highlighted into, for instance, starting cell D5.

    I have no idea where to begin since I am a novice user. Also, I have attached a my sample spreadsheet for more clarification.

    Any help would be appreciated. Hanan.

    By the way, I am using Excel'97.
    Attached Files Attached Files

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

    Re: Macro Button

    Attached find a copy of your Workbook with a macro button attached, which runs a macro to do what I think you want.

    As you have 2 types of highlighting, green and yellow, I included both.

    Perhaps you can make any changes required - the code follows :<pre>Sub CopyPaste()
    Range("A9,A14,A21,A26,A33,A38,A45,A50,A57,A62,A69, A74,A81,A86").Select
    Selection.Copy
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    End Sub</pre>

    Hope it helps

    Andrew
    Attached Files Attached Files

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

    Re: Macro Button

    Andrews' macro will do very specifically what you want to do, but if you are a novice user, would it help to know there's already an icon to paste values, which you can drag onto the standard toolbar? One way to do this follows:
    Click menu View, Toolbars, Customize, Commands tab, under Categories, click on Edit, under Commands, scroll down a little until you see Paste Values. Drag the "clipboard with the number 12 on it" to where you want on the Excel toolbar (mine is immediately to the right of the paste icon).

    Customizing your toolbars this way can really speed some things up.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Button

    Thanks to you all. This help a great deal!

    Hanan.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Button

    In the interest of keeping things in order, I am posting a related question here:
    I can place a command button on a WS in 3 ways 1) with the Forms toolbar; 2) the VB Toolbar; or 3) the Control Toolbox Toolbar. When should you use each and why is it that for some of the buttons I have created a Right Click will allow me access to the "assign Macro" function and with other I can only click on "View code"?

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

    Re: Macro Button

    You can place a buttton in any number of ways but there are just two types, the Button on the Forms toolbar (often called a macro button) and the command button from the control tool bar, which is an ActiveX control. These controls are also accessible via the Macro toolbar, and are the objects used to create user forms, in the VBA environment.

    The Forms toolbar is really supplied for backward compatibility with Excel 5.0/95. However the Forms button can be preferable to the ActiveX variety for running general purpose macros. When you place one of these on the worksheet, you should be prompted to assign a macro from your collection, if there are any. You can also trap the click event and run a routine when that is triggered. To do that select the button and on the Forms toolbar click the Edit Code button and you will be taken to the VBA editor where you should see something like Sub Button3_Click() ... End Sub, where you can place some code. As a matter of interest you can do the same with drawing objects and AutoShapes that you place on the sheet, including triggering a click event.

    The Control toolbar gives you access to the more recent (first seen in Office 97) ActiveX controls. To use these you can assign code to a number of different events, such as Button Click, KeyPress etc. Before using these you should be aware of a button on the control toolbar which toggles design mode on and off. To assign code etc to the button, ensure that you are in design mode. To see some of the events available, add one of these buttons to your worksheet, and whilst in design mode double click the button. You should be brought to the VBA editor and should see a blank code sheet with two dropdowns at the top. The left dropdown should read commandbutton1 or similar. If not select that from the dropdown. Click the right dropdown and select the MouseMove event. Between the lines of code that appear on the code sheet enter MsgBox("Hello"). Now go to the worksheet, switch off design mode and move the mouse over the button - no need to click - and you see the message box.

    Hope some of that goes somewhere towards answering you question.

    Andrew

Posting Permissions

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