codes for command button

March 27, 2009 at 18:31:40
Specs: Windows Vista
I created an Excel (2003) worksheet on which I have placed numerous macro buttons. One button puts the date in column "a" on sheet 2. Another puts the time in column "b" on sheet 2. The other buttons place a the value "1" in the appropriate column. I have a touch screen, (Toshiba, running Vista) so as I walk around and make my observations I just click the appropriate buttons.
As time goes on, the columns are summed and the values are graphed on sheet 3.

This works great, but it would be better if the values remained on the same line as the date and time stamp. This will help me to look for correlations. Since not all the buttons are chosen on a given day I can't do this because the value goes to the next available cell, which does not end up on the same line as the date and time.

I am using this code: Sheets("TALLY").Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).Value = "1"

"Tally" by the way is sheet 2

Do you know how to alter the code so that the value of all buttons stay on the same line as the date and time stamp?
I hope I have described my purpose and end clearly.

Thank you for your time.

See More: codes for command button

Report •

March 27, 2009 at 19:17:31
You have done a great job of describing the purpose and the end, but I think there may be one thing missing.

You gave us the code that puts the "1' in the next available cell in Column C, but not the code that tells us where the date and time are placed.

If we can assume that the Time is placed in the next available cell in Column B, then you can place the values in the same row by altering the Column your code looks at and Offset values it uses.

Since the syntax is .Offset(RowOffset, ColumnOffset) right now your code finds the last used cell in Column C and Offsets 1 Row and 0 Columns. i.e. the next empty cell in Column C.

This line will find the last used cell in Column B and Offset 0 rows and X columns, where X should be set to the (Column Number - 2) that you want the value placed in.

For example, if the "1" should be placed in Column 3 ("C") for a given button, X should be 1, which means Offset 1 Column from B.

Sheets("TALLY").Cells(Rows.Count, "B").End(xlUp).Offset(0, X).Value = "1"

i.e. Find the last cell in Column B and Offset 0 rows and X columns.

Report •

March 28, 2009 at 11:35:16
Dear Derbydad,

Thank you! It was exactly what I needed. You guessed correctly that my time was in the next available cell in column "B".
I appreciated the explanation along with the solution. It enabled me to better understand how the code is formulated.

I am thrilled to have gotten the solution, and so quickly!
God bless you.

Report •

Related Solutions

Ask Question