Excel VBA - How to specify last input row via userform

[kazzy]

Hi VBAers,

Rows are able to be created via a userform via a cmdAdd command
button. My code to add rows works fine but I want to write extra code
to make it check if row 29 has just been saved and then present a
msgbox (stating not allowed to go any further) and ALSO not allow any
further row additions (via the cmdAdd on userform).

I don't know how to make it not allow any further. I suspect I need to
set up variables like currentrow & check if currentrow is row 29 but
I don't know how to go about it.

Can someone please advise how to do this?

 

[Dave Peterson]

If you can look at a single cell to determine if the row is used:

with worksheets("somesheet")
if isempty(.range("a29").value) then
'the row is empty
end if
end with

If you have to check multiple cells, you could use something like:

with worksheets("somesheet")
'adjust the columns for what you need
if application.counta(.range("a29:x29")) = 0 then
'the row is empty
end if
end with

This doesn't actually checked the workbook's saved status. I'm not sure what
you mean by Save when you're writing about rows.

 

[Karen Davison]

If you can look at a single cell to determine if the row is used:

with worksheets("somesheet")
   if isempty(.range("a29").value) then
       'the row is empty
   end if
end with

If you have to check multiple cells, you could use something like:

with worksheets("somesheet")
   'adjust the columns for what you need
   if application.counta(.range("a29:x29")) = 0 then
       'the row is empty
   end if
end with

This doesn't actually checked the workbook's saved status.  I'm not sure what
you mean by Save when you're writing about rows.

Thanks Dave but maybe I didn't explain what I want to do clearly
enough, so I shall try to do so here:

I am starting with a blank worksheet and via the userform I am
populating blank rows when cmdAdd is clicked. However, after row 29 is
populated I want to stop allowing any further population of ongoing
rows. I want to show a msgbox (to say no further rows can be added) &
also actually also disallow it from happening.

However, you gave me an idea, because now I'm thinking that I have to
count the number of populated rows. I would do this when cmdAdd is
clicked (after it saves/populates the current row) and if equal to 29
present the msgbox. Then somehow disallow any further input < not sure
how to do this part yet.

 

[Dave Peterson]

If I can use a single column to determine if that row is used, I like to use
something like:

Dim NextRow as long
With worksheets("somesheet")
'I used column A
Nextrow = .cells(.rows.count,"A").enc(xlup).row + 1
if nextrow > 29 then
msgbox "Too many rows -- you're done!!!"
else
'fill the row with values from the userform
.cells(nextrow,"A").value = me.textbox1.value 'or something
.cells(nextrow,"b").value = me.textbox2.value 'or something
.cells(nextrow,"c").value = me.textbox3.value 'or something
end if
end with

 

[Dave Peterson]

Ps. Watch out for typos.

I see one:
Nextrow = .cells(.rows.count,"A").enc(xlup).row + 1
It should be:
Nextrow = .cells(.rows.count,"A").enD(xlup).row + 1
(end with a D)

 

[kazzy]

Ps.  Watch out for typos.

I see one:
Nextrow = .cells(.rows.count,"A").enc(xlup).row + 1
It should be:
Nextrow = .cells(.rows.count,"A").enD(xlup).row + 1
(end with a D)

Thank you Dave. I put in a workaround to get something done but will
try your code out next week & let you know.