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

[Ladyj205]

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:



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

[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

[Ladyj205]

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??

[Ladyj205]

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

[Paul_Hossler]

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




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

[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.

[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.

[Paul_Hossler]

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

[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

[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.


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

[Ladyj205]

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

[SamT]

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

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

[Paul_Hossler]

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

[Ladyj205]

sorry :(. i hope this helps

[Paul_Hossler]

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



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


22273


Now I do see 4 empty Group boxes on the worksheet and a lot of Option Button 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???

[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.

[Paul_Hossler]

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

22294


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




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




Private Sub CommandButton1_Click()

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

End Sub

[Ladyj205]

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

[Paul_Hossler]

Yes





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

or

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

[Ladyj205]

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:

[Ladyj205]

here is an example of the problem that im having.

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

[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





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

[Ladyj205]

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





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.

[Ladyj205]

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

[Paul_Hossler]

Ref: Post #22 --


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



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


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

[Ladyj205]

your attachment is showing something totally different.

[Paul_Hossler]

Probably because I attached the wrong file :crying:

This one should be better

[Ladyj205]

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.

[Paul_Hossler]

Run the macro 'SetAlOptionButtonsOnAction'


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

[SamT]

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

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