PDA

View Full Version : [SOLVED] How to keep Inserting Rows Command from moving other cells in a sheet



Ladyj205
05-11-2018, 02:07 PM
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
05-11-2018, 02:20 PM
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
05-14-2018, 08:00 AM
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
05-14-2018, 08:17 AM
i was trying to get my rows to look like this. everytime i click new row. it moves everthing else

Paul_Hossler
05-14-2018, 08:55 AM
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
05-14-2018, 11:46 AM
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
05-16-2018, 12:58 PM
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
05-16-2018, 01:12 PM
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
05-16-2018, 03:40 PM
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
05-17-2018, 08:08 AM
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
05-17-2018, 10:02 AM
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
05-17-2018, 04:59 PM
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
05-17-2018, 06:52 PM
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
05-18-2018, 12:41 PM
sorry :(. i hope this helps

Paul_Hossler
05-18-2018, 01:24 PM
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
05-21-2018, 07:08 AM
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
05-21-2018, 08:31 AM
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
05-22-2018, 01:31 PM
is there a way i can hide a number of rows if the user wanted to use a lot of rows.

Paul_Hossler
05-23-2018, 04:03 AM
Yes





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

or

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

Ladyj205
05-23-2018, 10:13 AM
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
05-23-2018, 12:16 PM
here is an example of the problem that im having.

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

Paul_Hossler
05-23-2018, 12:21 PM
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
05-23-2018, 12:51 PM
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
05-23-2018, 01:01 PM
i had assign the macro to the form buttons and this what i got. i been trying to get this working.

Paul_Hossler
05-23-2018, 02:15 PM
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
05-24-2018, 06:20 AM
your attachment is showing something totally different.

Paul_Hossler
05-24-2018, 06:53 AM
Probably because I attached the wrong file :crying:

This one should be better

Ladyj205
05-24-2018, 09:07 AM
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
05-25-2018, 01:40 PM
Run the macro 'SetAlOptionButtonsOnAction'


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

SamT
05-27-2018, 08:39 AM
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