Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Macros (Excel 2003)

    I am trying to create a shortcut key Macros that will insert a new row under the header (Row 8 with Autofilter, adopt the formating of the subsequent rows as well as the data validation of Columns B and F and the formula of Column C. I would like the SUBTOTAL function in row 7 to include the new records as well.

    Even if I were to insert a row below the first record, the formula in Column C isn't adopted for the new record, though everything else is take care of.

    Thanks
    Amy
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Insert Macros (Excel 2003)

    Hi Amy,
    I've attached your worksheet with a macro that should do what you'd like it to do (press the pink button... I've attached the macro to it). I've inserted comments into the code so that you can see what I did. Also, please note that I changed the vlookup formula (in column C), likewise I changed the subtotal formula in A7... hopefully I assumed correctly in making these changes.
    Regards,
    Lana
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Macros (Excel 2003)

    Lana,
    Thank you. That worked like a charm. I ended up not using the 1 up numbering code, but that is interesting how that works. Why use 65536? Is that the record limit for Excel or just something beyond the horizon?

    As I played with it myself, I surely forgot the ClearContects step.

    Amy

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert Macros (Excel 2003)

    65536 is the number of rows in an XL sheet before XL2007. If the macro may someday converted to XL2007, I would suggest using

    Cells(cells.Rows.Count,2)

    instead of

    Range("B65536")

    This will work no matter how many rows it has...

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Insert Macros (Excel 2003)

    Hi Amy,
    I'm glad it worked! The 65,536 you asked about is the last row in Excel... at least it is the limit in the older versions of Excel (I use Excel 2002). The code basically starts with the last cell in that row (cell B65536) and then counts how many rows up from that cell to the bottom of the where the data is located (cell B29). This "count" is then used as the "x" to determine the bottom of the database for the "autofill" portion of the code.
    Regards,
    Lana

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Insert Macros (Excel 2003)

    Hi Lana...often, I want to change the color of a button...I know how to Format the button to change the color of the text, but cannot figure out how to change the background color of the button (mine are always gray)....is this something that is on Excel 2007 (I am using Excel 2003)? If not, how can I do it in Excel 2003...thanks?

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

    Re: Insert Macros (Excel 2003)

    The background color of a command button from the Forms toolbar cannot be changed in Excel - it is determined by your Windows color scheme as set in the Appearance tab of the Display Properties control panel (it is the color for "3D Objects").

    You *can* change the background color of a command button from the Control Toolbox toolbar: it's the BackColor property.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert Macros (Excel 2003)

    You must use the command button from the Control Toolbox and not the FORMS toolbar.
    Get into "Design Mode" (control toolbox button with pencil ruler and triangle)
    Right-click the object - properties
    change the "Back Color" property.
    Press the button with the triangle and ruler to "Exit Design Mode".

    The buttons on the FORMS toolbar are simpler to use, but you have less control over them (like changing color, etc)

    Steve

Posting Permissions

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