How to keep Inserting Rows Command from moving other cells in a sheet

I have 2 command buttons on excel 2010.
everytime i click on the commandbuttons for each the other tables and cells seems be to effected. like everything is out of place. is there a vba for my vba codes to keep the other cells still while the user select the new row button.

the codes i have for my command buttons:

Code:

---

Private Sub CommandButton2_Click()
Sheets("Sheet1").Range("A32").Select
ActiveCell.EntireRow.Insert Shift:=xlDown


Sheets("Sheet1").Range("A32:D32").Select
Selection.Borders.Weight = xlThin
End Sub


Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A15").Select
ActiveCell.EntireRow.Insert Shift:=xlDown


Sheets("Sheet1").Range("A15:D15").Select
Selection.Borders.Weight = xlThin


End Sub

---

That's what InsertRows does -- pushs everything down

You could save the row number above the data you do not want to move, insert the row with your macro, and then delete the saved row number

Quote:

---

Originally Posted by Paul_Hossler

That's what InsertRows does -- pushs everything down

You could save the row number above the data you do not want to move, insert the row with your macro, and then delete the saved row number

---

how can insert the rows with macros, how can i do that??

i was trying to get my rows to look like this. everytime i click new row. it moves everthing else

It looks like your 2 CommandButtons insert a new row 15 and a new row 32, but you don't want anything pushed down???

This inserts a new row at 15, and deletes the old row at 18 which had been 17

Code:

---

Option Explicit

Private Sub CommandButton1_Click()
    Me.Rows("15:15").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Me.Rows("18:18").Delete Shift:=xlUp
End Sub

---

If you want anything other that that, you'll need to explain a lot more with an attachment and examples

i was saying i have a couple of other stuff on the sheet. its moving everything down and messing up my optionbuttons and causing them not to work. i was trying to see if there is someway i can have set to a limited of rows when u click on the command button.

is there a way to keep the row instead of deleting ----->>> Me.Rows("18:18").Delete Shift:=xlUp

because everytime i click the command button and it overrides and deletes my other tables thats under it.

Quote:

---

Originally Posted by Ladyj205

is there a way to keep the row instead of deleting ----->>> Me.Rows("18:18").Delete Shift:=xlUp

because everytime i click the command button and it overrides and deletes my other tables thats under it.

---

I can't figure out what you're wanting to do.

Post an example workbook with a description of what you'd like to end up with

Set your Option Buttons and other controls to NOT move with cells

Guessing about your columns
Range("B17:C17").Insert w/Shift Down
Range("B18:C18").Delete w/Shift Up

Quote:

---

Originally Posted by Ladyj205

i was saying i have a couple of other stuff on the sheet. its moving everything down and messing up my optionbuttons and causing them not to work. i was trying to see if there is someway i can have set to a limited of rows when u click on the command button.

---

Quote:

---

Originally Posted by SamT

Set your Option Buttons and other controls to NOT move with cells

Guessing about your columns
Range("B17:C17").Insert w/Shift Down
Range("B18:C18").Delete w/Shift Up

---

so it will be like:

Private Sub CommandButton1_Click()
Range("38:38").Insert Shift:=xlDown,
Range("46:46").Delete Shift:=xlUp
End Sub

Quote:

---

Originally Posted by Paul_Hossler

I can't figure out what you're wanting to do.

Post an example workbook with a description of what you'd like to end up with

---

all i was saying was adding rows affects other tables and they were added to the wrong section. you on the right path with me. i was trying to figure how prevent the clashing with my other materials and deleting on my sheet. everytime the new row button has been click. i wanted the user to click the new row button as much they need without messing with other material. sorry for being difficult.


i insert what problem i keep running into :(:(

thank u

Quote:

---

so it will be like:

Private Sub CommandButton1_Click()
Range("38:38").Insert Shift:=xlDown,
Range("46:46").Delete Shift:=xlUp
End Sub

---

That is the same as

Code:

---

Private Sub CommandButton1_Click()
Rows(38).Insert
Rows(46).Delete
End Sub

---

Insert and Delete Rows is always Shift down and shift up, respectively. How can be otherwise?

I was trying to show you how to Insert and Delete only part of a row. You only need to specify the Shift direction when working on part of a Row.

We have no idea what your worksheet looks like, so we can not give you any precise advice. Your pretty pictures don't carry any usable information at all.

You can upload a workbook by clicking the Go Advanced button and the Manage Attachments button on that page

Quote:

---

Originally Posted by Ladyj205

i insert what problem i keep running into :(:(

thank u

---

It would be a lot easier to suggest options if you would attach a workbook like people have suggested

sorry :(. i hope this helps

OK, using your workbook, I clicked [New Row] two times ...

Code:

---

Private Sub CommandButton1_Click()
Me.Rows("16:16").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Me.Rows("26:26").Delete Shift:=xlUp
End Sub

---

... and it inserted a new row 16 and deleted row 26 and then another new row 16 and then deleted row 26 again

That all seems reasonable to me -- it did what you asked it to do


Attachment 22273


Now I do see 4 empty Group boxes on the worksheet and a lot of Option Button code ….

Code:

---

Private Sub OptionButton_1_0_Click()
  If OptionButton_1_0 Then
  Range("D60").Value = 0
  End If
End Sub

Private Sub OptionButton_1_1_Click()
  If OptionButton_1_1 Then
  Range("D60").Value = 25
  End If
End Sub

---

… so I suspect that there are some things on your worksheet that were not in your attachment???

yes it was somethings on my worksheet not mention. i was trying to re-do the whole thing. i forgot to take it out. sorry :( if there anyway to overide the new rows to keep it from deleting other rows.

Quote:

---

Originally Posted by Ladyj205

yes it was somethings on my worksheet not mention. i was trying to re-do the whole thing. i forgot to take it out. sorry :( if there anyway to overide the new rows to keep it from deleting other rows.

---

Don't know what 'override the new rows' means

If you insert a row like that, then it will push the other rows down, as well as any controls unless you mark each control to "Don't Move or Resize with Cells" like SamT said in #9

Attachment 22294


If you just want to INSERT a new row 16 without deleting, just remove the second like

Code:

---

Private Sub CommandButton1_Click()

Me.Rows("16:16").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Me.Rows("26:26").Delete Shift:=xlUp

End Sub

---

If you want to REPLACE row 16 then maybe

Code:

---

Private Sub CommandButton1_Click()

Me.Rows("16:16").Delete Shift:=xlUp
Me.Rows("15:15").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

---

is there a way i can hide a number of rows if the user wanted to use a lot of rows.

Yes

Code:

---


rows("10:20").hidden=true

or

rows(30).resize(15).hidden = true

---

Hi Paul,

Everytime i insert a new row. my table with optionbuttons change and it places the value elsewhere.

is there a way i can have my table to look like this when there are new rows insert. im still pulling hair but not as much.

: pray2:

here is an example of the problem that im having.

if one of the optionbutton is selected. the number is elsewhere :(

Try these

Few items to be aware of

1. The button name has to end with 0, 1, 2, or 3

2. The Group Box and embedded OP have to be in one cell

Look at the

Code:

---

Option Explicit

'This process a OB click and uses the current top left cell to determine row number to update
Sub OB_Clicked()
    Dim R As Long
    R = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
   
    Application.EnableEvents = False
       
    Select Case Right(Application.Caller, 1)
        Case "0":      Range("A" & R).Value = 0
        Case "1":      Range("A" & R).Value = 25
        Case "2":      Range("A" & R).Value = 50
        Case "3":      Range("A" & R).Value = 75
    End Select
   
    Application.EnableEvents = True
   
End Sub


'This assigns the macro "OB_Clicked" to all Form Option Buttons on the Activesheet
Sub SetAllOptionButtonsOnAction()
    Dim oShape As Shape
   
    For Each oShape In ActiveSheet.Shapes
        If oShape.Type = msoFormControl Then
            If oShape.FormControlType = xlOptionButton Then
                oShape.OnAction = "OB_Clicked"
            End If
        End If
    Next
End Sub

---

Edit -- put the right file in this time

Quote:

---

Originally Posted by Paul_Hossler

Try these

Few items to be aware of

1. The button name has to end with 0, 1, 2, or 3

2. The Group Box and embedded OP have to be in one cell

Look at the

Code:

---

Option Explicit

'This process a OB click and uses the current top left cell to determine row number to update
Sub OB_Clicked()
    Dim R As Long
    R = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
   
    Application.EnableEvents = False
       
    Select Case Right(Application.Caller, 1)
        Case "0":      Range("A" & R).Value = 0
        Case "1":      Range("A" & R).Value = 25
        Case "2":      Range("A" & R).Value = 50
        Case "3":      Range("A" & R).Value = 75
    End Select
   
    Application.EnableEvents = True
   
End Sub


'This assigns the macro "OB_Clicked" to all Form Option Buttons on the Activesheet
Sub SetAllOptionButtonsOnAction()
    Dim oShape As Shape
   
    For Each oShape In ActiveSheet.Shapes
        If oShape.Type = msoFormControl Then
            If oShape.FormControlType = xlOptionButton Then
                oShape.OnAction = "OB_Clicked"
            End If
        End If
    Next
End Sub

---

---

so u saying my optionbuttons has to be something like:

Optionbutton_0
Optionbutton_3
Optionbutton_2
Optionbutton_1

Is that what u meant....this stuff is stressing me out and im still new to this stuff. i have place a groupbox over the 4 Optionbuttons and nothing working for me. :(i was using active x optionbuttons but i remove them in place of form buttons.

i had assign the macro to the form buttons and this what i got. i been trying to get this working.

Ref: Post #22 --

Quote:

---

2. The Group Box and embedded OP have to be in one cell

---

It looks like you have the 4 OB's in different cells in 4 different rows

Quote:

---

i have place a groupbox over the 4 Optionbuttons and nothing working for me.

---

Groupbox first, and then option buttons


Make the OB row taller, insert the form Group Box and then insert the 4 OB

Look at my attachment in 22


Attachment 22312

I did it that way so that all OB's have the same .TopLeftCell and therefore that same .Row wihich is where the result goes

Because I used .TopLeftCell when you insert rows above and things push down, you will get a new .TopLeftCell row, but the Range("A" & r) will be the row that the OB's are in

your attachment is showing something totally different.

Probably because I attached the wrong file :crying:

This one should be better

did u have to assign any macros for each optionbutton to work.

when i try to do it on my own...nothing is working :(. i did everything. i set my form control optionbuttons their assign numbers and etc.

Run the macro 'SetAlOptionButtonsOnAction'


It assigns the macro 'OB_Clicked' to all option buttons on the activesheet

If you want to insert or delete rows in a table, then specify the Column as well as the row

Code:

---

Range("C15:J15").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C16:J16").Delete Shift:=xlUp
'Edit ranges to suit

---

Then nothing outside that range should be affected