i need to have a button at the beginning of each row, that copies the
row that the button is on to the next row below it.....

but when i copy a row to the next line, it doesnt copy the button. it
will copy it if im in design mode, but this worksheet is for other
users and it will be protected and not allowed to go into design mode.
can this be done?

(on a sidenote....is it possible to snap a button into a cell, making
the entire cell a button?)

Use a CommandButton not a form button.
They copy with the code too.

--
Regards

Corey
"Nigel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>i need to have a button at the beginning of each row, that copies the
> row that the button is on to the next row below it.....
>
> but when i copy a row to the next line, it doesnt copy the button. it
> will copy it if im in design mode, but this worksheet is for other
> users and it will be protected and not allowed to go into design mode.
> can this be done?
>
> (on a sidenote....is it possible to snap a button into a cell, making
> the entire cell a button?)
>

I take it you are using a worksheet control from the controls toolbox menu.
By default these are formatted to "Move but don't size with cells". You
could change to "Move & size with cells" but a copied button will not be
linked to any code. There are ways to do that but problems can occur
(particularly if doing on the active sheet).

Suggest use a Forms button and ensure format is "Move but don't size with
cells".

> (on a sidenote....is it possible to snap a button into a cell, making
> the entire cell a button?)

There is a snap to grid icon, customize toolbars > Drawing toolbar. Or, try
holding Alt when repositioning / resizing. Otherwise programmatically be
setting size to cell dimensions.

Regards,
Peter T

"Nigel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> i need to have a button at the beginning of each row, that copies the
> row that the button is on to the next row below it.....
>
> but when i copy a row to the next line, it doesnt copy the button. it
> will copy it if im in design mode, but this worksheet is for other
> users and it will be protected and not allowed to go into design mode.
> can this be done?
>
> (on a sidenote....is it possible to snap a button into a cell, making
> the entire cell a button?)
>

ok so this is what i have so far....

Sub AddSize()
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
rowNum = r.Row
cellValue = r.Value
Rows(rowNum).Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

its pretty buggy tho....
first thing, is that it copies the last row rather than the row the
button is on. every row has its own button so any row could be copied,
but it always just copies the last row. Which is wierd because it
doesnt seem like there is anything that would tell it to find the last
row.

also when it copies the row, for some reason it creates a button that i
cant delete....ive tried everything......right click>cut......design
mode (even tho its a form button and not a control toolbox
button)......when i manually copy and insert a new row, i can delete
the button, so i thinks it something in the code...

any thoughts? thnx

On Nov 10, 3:43 am, "Peter T" <peter_t@discussions> wrote:
> I take it you are using a worksheet control from the controls toolbox menu.
> By default these are formatted to "Move but don't size with cells". You
> could change to "Move & size with cells" but a copied buttonwill not be
> linked to any code. There are ways to do that but problems can occur
> (particularly if doing on the active sheet).
>
> Suggest use a Formsbuttonand ensure format is "Move but don't size with
> cells".
>
> > (on a sidenote....is it possible to snap abuttoninto a cell, making
> > the entire cell abutton?)There is a snap to grid icon, customize toolbars > Drawing toolbar. Or, try
> holding Alt when repositioning / resizing. Otherwise programmatically be
> setting size to cell dimensions.
>
> Regards,
> Peter T
>
> "Nigel" <horror1...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> > i need to have abuttonat the beginning of eachrow, that copies the
> >rowthat thebuttonis on to the nextrowbelowit.....
>
> > but when icopyarowto the next line, it doesntcopythebutton. it
> > willcopyit if im in design mode, but this worksheet is for other
> > users and it will be protected and not allowed to go into design mode.
> > can this be done?
>
> > (on a sidenote....is it possible to snap abuttoninto a cell, making
> > the entire cell abutton?)

See how you get on with this -

Sub AddSize2()
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
' cellValue = r.Value
r.EntireRow.Copy
r.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

Regards,
Peter T


"Nigel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> ok so this is what i have so far....
>
> Sub AddSize()
> Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
> rowNum = r.Row
> cellValue = r.Value
> Rows(rowNum).Select
> Selection.Copy
> Selection.Insert Shift:=xlDown
> End Sub
>
> its pretty buggy tho....
> first thing, is that it copies the last row rather than the row the
> button is on. every row has its own button so any row could be copied,
> but it always just copies the last row. Which is wierd because it
> doesnt seem like there is anything that would tell it to find the last
> row.
>
> also when it copies the row, for some reason it creates a button that i
> cant delete....ive tried everything......right click>cut......design
> mode (even tho its a form button and not a control toolbox
> button)......when i manually copy and insert a new row, i can delete
> the button, so i thinks it something in the code...
>
> any thoughts? thnx
>
> On Nov 10, 3:43 am, "Peter T" <peter_t@discussions> wrote:
> > I take it you are using a worksheet control from the controls toolbox
menu.
> > By default these are formatted to "Move but don't size with cells". You
> > could change to "Move & size with cells" but a copied buttonwill not be
> > linked to any code. There are ways to do that but problems can occur
> > (particularly if doing on the active sheet).
> >
> > Suggest use a Formsbuttonand ensure format is "Move but don't size with
> > cells".
> >
> > > (on a sidenote....is it possible to snap abuttoninto a cell, making
> > > the entire cell abutton?)There is a snap to grid icon, customize
toolbars > Drawing toolbar. Or, try
> > holding Alt when repositioning / resizing. Otherwise programmatically be
> > setting size to cell dimensions.
> >
> > Regards,
> > Peter T
> >
> > "Nigel" <horror1...@gmail.com> wrote in
messagenews:(E-Mail Removed)...
> >
> > > i need to have abuttonat the beginning of eachrow, that copies the
> > >rowthat thebuttonis on to the nextrowbelowit.....
> >
> > > but when icopyarowto the next line, it doesntcopythebutton. it
> > > willcopyit if im in design mode, but this worksheet is for other
> > > users and it will be protected and not allowed to go into design mode.
> > > can this be done?
> >
> > > (on a sidenote....is it possible to snap abuttoninto a cell, making
> > > the entire cell abutton?)
>