Sorting deletes my Buttons

[hstaubyn]

Hi,

I have a table with the cells in the second column devoted to containing command buttons. The buttons are created using a subroutine in another part of my code:

Sub Add_Button(InsertRow)
 
Dim Btn As Object
 
    With Range("C" & InsertRow)
        Set Btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
    End With
 
    With Btn
    .OnAction = "Activate_Job"
    .Characters.Text = "Activate"
 
         With .Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 10
         End With
 
    End With
End Sub

The problem I am having is that when I select the cells in the table and try to sort them by column 'B', the sort works fine, but some of the buttons disappear.

Does anyone know why they disappear and what to do about it? I am at a loss...

Thanks,

H

 

[Sachin.Medhe]

Please assign the value for InsertRow=1 or what ever you want

 

[hstaubyn]

sachin.mehde,

not too sure what you mean...

The value for 'InsertRow' gets passed to the subroutine from another one. You can put any value you like in the code, it will still create a button that executes "Activate_Job" when pressed.

In any case, the code is not the issue here. The problem is that when I try to sort the rows that contain the buttons they get sorted, but when this happens, some of the buttons are deleted, for some bizarre (to my limited mind) reason. It's like the sort command also deletes some of the buttons. This happens seemingly at random, but it's always the same buttons (which ones depends on the number of rows there are to be sorted).


Unfortunately I can't seem to post a picture of the spreadsheet, but picture this instead:

• 20 column by 14 row table (cells B5:U18)
• The cells in Column C have all got buttons in them (sized to fit the cells)
• Select the whole table (B5:U18)
• Sort using values in column B (lowest to highest)

When you do this a number of the buttons in column C disappear. Why is this???

Any help would be much appreciated.