Accessing a non-active sheet from a userform

[Ken Loomis]

I have a user form that lets the user enter data to be added to a sheet that
I would like to keep hidden from view.

If I activate the sheet first with this:

Sheets("Replace Info").Activate

then the following works to determine the next row for adding the user's
entries:

NextRow = Application.WorksheetFunction._
CountA(Range("A:A")) + 1

So, I thought this might work if I didn't activate the sheet first:

NextRow = Application.WorksheetFunction.Sheets("Replace Info") _
CountA(Range("A:A")) + 1

Clearly I do not understand something, 'cause that didn't work.

Can anyone help me out?

And, to avoid posting back here after I get an answer to that, the following
code works to put the value into a cell on the sheet if I activate it first:

Cells(NextRow, 1) = Find_What.Text

And, I tried this with the sheet not activated:

Sheets("Replace Info").Cells(NextRow, 1) = Find_What.Text

but it never got there because of the problem described above.

Will that work, and if not, what will work?

Thanks,
Ken Loomis

 

[Tom Ogilvy]

NextRow = Application.WorksheetFunction.CountA(Sheets("Replace
Info").Range("A:A")) + 1

or

NextRow = Sheets("Replace Info").Cells(rows.count,1).End(xlup)(2).Row

 

[Ken Loomis]

Thanks, Tom.

That did it and also helped me figure out how to access the cells on that
non-active sheet.

Ken Loomis