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

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