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

[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


Capture.JPG

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

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