Adding a row with macro
 

Hi,
i have a sheet set out with a text box. the ListFillRrange is Contacts!A3:G35
When i goto the contacts sheet and add a row at the bottom, the ListFill
Range increases to Contacts!A3:G36 which is good. however, when i insert the
row through a macro, the ListFillRange does not change. I am using a routine
that finds the next available cell on the Contacts sheet as supplied to me
through this site ( supplied he)

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.rows(iRow).Insert

Can something be done to insert a row but let the ListFillRange increase
automatically? this is required as i am entering alot of information through
this text box and i either have to increase the range to A3:A1000( which
gives me a amssive dropdown box) or keep updating theListFillRange.

Any help appreciated

Nigel

Adding a row with macro
 

Perhaps

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.rows(iRow).Insert
ws.Textbox1.ListFillRrange ="Contacts!A3:G" & iRow


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel" wrote in message
...
Hi,
i have a sheet set out with a text box. the ListFillRrange is
Contacts!A3:G35
When i goto the contacts sheet and add a row at the bottom, the ListFill
Range increases to Contacts!A3:G36 which is good. however, when i insert
the
row through a macro, the ListFillRange does not change. I am using a
routine
that finds the next available cell on the Contacts sheet as supplied to me
through this site ( supplied he)

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.rows(iRow).Insert

Can something be done to insert a row but let the ListFillRange increase
automatically? this is required as i am entering alot of information
through
this text box and i either have to increase the range to A3:A1000( which
gives me a amssive dropdown box) or keep updating theListFillRange.

Any help appreciated

Nigel

Adding a row with macro
 

Hi Bob,

Sorry, i forgot to mention that the Textbox is actually a combobox and it is
on another sheet called "Contacts Page". Everything works from here and the
"Contacts" sheet is hidden. the next cell is found and row inserted without
actually opening the sheet.

Any Ideas?

Nigel


"Bob Phillips" wrote:

Perhaps

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.rows(iRow).Insert
ws.Textbox1.ListFillRrange ="Contacts!A3:G" & iRow


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel" wrote in message
...
Hi,
i have a sheet set out with a text box. the ListFillRrange is
Contacts!A3:G35
When i goto the contacts sheet and add a row at the bottom, the ListFill
Range increases to Contacts!A3:G36 which is good. however, when i insert
the
row through a macro, the ListFillRange does not change. I am using a
routine
that finds the next available cell on the Contacts sheet as supplied to me
through this site ( supplied he)

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.rows(iRow).Insert

Can something be done to insert a row but let the ListFillRange increase
automatically? this is required as i am entering alot of information
through
this text box and i either have to increase the range to A3:A1000( which
gives me a amssive dropdown box) or keep updating theListFillRange.

Any help appreciated

Nigel

Adding a row with macro
 

"Nigel" wrote in message
...
Hi Bob,

Sorry, i forgot to mention that the Textbox is actually a combobox

:-)

and it is
on another sheet called "Contacts Page". Everything works from here and
the
"Contacts" sheet is hidden. the next cell is found and row inserted
without
actually opening the sheet.

Presumably ws points to 'Contacts Page' and the combo is on some other page?

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Rows(iRow).Insert
ActiveSheet.ComboBox1.ListFillRange = "'Contacts Page'!A3:G" & iRow

should work, it did in my tests