Thread: ActiveX Command Buttons - Dont stick in the same place?

Hi,

Anyone know much about how to get some command buttons to stick in one place?

I currently have two in a summary sheet. Each button runs a similar code which clears and deletes (by shifting cells upward) at the start of the process, then populates data.

It's rather annoying though that I cant have the buttons stick to one zone, they end up moving left or right depending on which button I press. Each report also changes column sizes, dont know if that's relevant?

Anyhow, thanks for the help! The code is below in case it helps too.

Code:

Private Sub CommandButton2_Click()

    Dim ws As Worksheet 'Define WS as worksheet
    Application.ScreenUpdating = False 'don't update the screen (reduces lag time)
     
     
    Sheet3.Activate 'select report tab and clear contents (necesairy to allow macro to run properly
    Range("A3:AZ100").Select
    Selection.Delete Shift:=xlUp
    
    'Enter date of report
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Subscriber Report"
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Report as of:"
    Range("C1").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Range("D1").Select
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("D1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    
    'Enter Column Titles
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Region"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Country"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "Legal Name"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "TV Group"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "ACCT#"
    
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "Platform"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Deal Type"
    
    
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "Total Operator Subs"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "Total BBCWN Subs"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "Prev. Month Subs"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "Gain/Loss"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "Penetration"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "Comments"
    
    

        For Each ws In Worksheets 'for each worksheet (WS), take the specified range and paste special value in the named column
        If ws.Name <> "Dashboard" And ws.Name <> "TEMPLATE" And ws.Name <> "Market" Then
            'if the named sheet is not Dashboard, Template or Market, then run the if statement to grab data
            
            'Subscriber Report Data
            
            
            ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0) = "South America" 'Region
            ws.Range("C3").Copy 'Country
            ActiveSheet.Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            ws.Range("C1").Copy 'Legal Name
            ActiveSheet.Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            ws.Range("G4").Copy 'TVGroup
            ActiveSheet.Range("D65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            ws.Range("G1").Copy 'Account Number
            ActiveSheet.Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
            ws.Range("G7").Copy 'Platform
            ActiveSheet.Range("F65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            ws.Range("K3").Copy 'DealType
            ActiveSheet.Range("G65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
            ws.Range("I7").Copy 'Total Operator Subs
            ActiveSheet.Range("H65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            ws.Range("I8").Copy 'Total BBCWN Subs
            ActiveSheet.Range("I65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
            'Previous Month BBCWN Subs
            '???what to put here??? = "=VLOOKUP((INDEX(B11:L500,ROW(INDEX(D$11:D$500,MATCH(9.99999999999999E+307,D$11:D$500)))-10,1)-28),B11:L500,3)"
            'ActiveSheet.Range("J65536").End(xlUp).Offset(1, 0).PasteSpecial x1PasteValues
            
            'Simple Formulas
            ActiveSheet.Range("K65536").End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-1]-RC[-2]" 'Gain/Loss Formula: Subtract current month from previous month value
            ActiveSheet.Range("L65536").End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-3]/RC[-4]" 'Penetration FORMULA: Divide Total Current CLIENT Subscribers by Total OPERATOR Subscribers
            

        End If 'end affiliate data grab, continue to next sheet (aka next affiliate)
    Next ws 'end worksheet (WS) loop
    
    'Format Column sizes
    
    Columns("A:A").ColumnWidth = 14.43 'Region
    Columns("E:E").ColumnWidth = 11 'Account Number
    Columns("H:H").ColumnWidth = 20.14 'Total Operator Subs
    Columns("I:I").ColumnWidth = 20.14 'Total BBCWN Subs
    Columns("J:J").ColumnWidth = 19 'Prev. Month
    Columns("K:K").ColumnWidth = 13 'Gain/Loss
    Columns("L:L").ColumnWidth = 13 'Penetration
    Columns("M:M").ColumnWidth = 35 'Comments


    
        
    Rows("3:100").Select
    Selection.AutoFilter

    
    Application.ScreenUpdating = True 'display results
    ActiveWindow.ScrollColumn = 1

End Sub

You probably need to place the buttons in an area that does not get affected by your column or row manipulation.