Insert Macros (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote