make button fill next available cell in column

[cnstarz]

in my spreadsheet (attached), i want it so that when someone clicks a button, it will fill in the next available cell in column A. For example, when I click on the "Create" button on the green NIPRNet side, then it'll fill in the next available cell in column A (cell A4) with "(N) Created Account". Then, if I click on "Unlock" on the red SIPRNet Side, then it will fill in cell A5 with "(S) Unlocked Account". and so on, and so on, down column A.

At the moment, i have it so that all the cells in column A are dropdown menus. But I want to eliminate the drop-down menus and use buttons instead.

[Chance2]

You could use something like Range("A1").End(xlDown).Row+1 to find the first empty row in column A and then use that for your button clicks.

[cnstarz]

that statement makes sense to me, but i dont know how to use it cuz im an excel novice. what do i do with it?

edit: okay so i recorded a macro, and it looks like this:

Please Login or Register  to view this content.

i assigned the macro to a button, and i get this error:

Please Login or Register  to view this content.

i click OK, and it highlights ".Row" as if there's a problem with ".Row"

i pasted Range("A1").End(xlDown).Row+1 directly under ActiveCell.FormulaR1C1 = "(N) Created Account", and it replaced the "+" with a space. so it ends up looking like this:

Please Login or Register  to view this content.

[Chance2]

The Range("A1").End(xlDown).Row+1 should be used as a variable to get your row assignment for the formula. Assuming you are using this off of the button click you would not use the ActiveCell to reference your data.

Basically this is all you need (assumes you are using the "Create" button named Button17):

Please Login or Register  to view this content.

[cnstarz]

yes! i love you. thank you very much!! :D

[cnstarz]

okay i have one last request. when i press a button, i would like it to not only fill in the next available cell in column A on the current worksheet, but also on another worksheet (labeled "Life") too. so it's updating two worksheets at the same time.

[Chance2]

The easiest way would be to run the code twice using the sheet name (index or by name)...

Please Login or Register  to view this content.

[cnstarz]

okay i've run into a problem with the first example you gave me:

Please Login or Register  to view this content.

if i already have something in A2, then the macros will work fine. however, if A2 is empty, then i will get this error:

run-time error '1004':

Application-defined or object-defined error

[windknife]

try this

Please Login or Register  to view this content.

Hope this helps,
windknife

[Chance2]

You could work this the other way (xlUp)...

Please Login or Register  to view this content.

This will look from the bottom up!

[cnstarz]

hooray that worked! thanks a lot guys!

ONE LAST QUESTION THO!!! since there will easily be hundreds of records created, is there a way to:

1) Make the next empty cell active so that the page scrolls down everytime a new record is created.

2) Keep the Niprnet and Siprnet boxes "floating" so that when you scroll down the page the boxes will always be right there in view

3) Automatically make all Niprnet records green, and all Siprnet records red.

hope that makes sense. i attached the latest copy of the spreadsheet. thanks for all your help so far!