Stopping Form Buttons From Moving\Resizing\Distorting: XL2003 VBA

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
Hi All,

I have some Form Buttons on an excel sheet that are assigned macros etc. I've locked them to their current sizes, unticked them so they don't print and selected them not to move or resize with cells.

Now, when I execute the below code the 2nd time it doe's some random resizing\moving of these buttons:

Code:
Sub Insert_Line_Pre_Deployment_Preparation()
 
    Dim FindString As String
    Dim Rng As Range
 
With ActiveSheet.Unprotect
 
'Enter Search Value between the ""
    FindString = "1.1"
 
    If Trim(FindString) <> "" Then
 
'Set The Search Range Between The ""
        With ActiveSheet.Range("A:A")
            Set Rng = .Find(What:=FindString, After:=.Cells(1, 1), LookIn:=xlValues, _
                            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                            MatchCase:=False)
 
'Moves To The Cell With The Value In
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
 
'Copys The Row Specified
                Sheets("Format Control").Rows(19).Copy
 
'Inserts The Row Copied Above Beneath The Row Found With The Value In
                Rng.Offset(1).EntireRow.Insert
 
'Moves The Active Cell 1 Row Down and 2 Cells To The Right
                Rng.Offset(1, 2).Select
 
                Else
            End If
        End With
    End If
 
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    AllowInsertingRows:=True, AllowDeletingRows:=True
 
    End With
End Sub

If I unprotect the sheet and insert rows manually it behaves, the same with copying rows and inserting them however, using the macro seems to create this problem and I've stared at it till I'm blind and can't see what is wrong with the code (or me for that matter :eeek:).

If anyone has any pointers of can help I'd be very grateful.

Thank you for your time,

Mark.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,224,603
Messages
6,179,854
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top