Select Method of Button Class Failed

[shaunk]

Hi,

Excel 2003.
I am adding a series of buttons to a worksheet by selecting a particular cell, obtaining it's coordinates on the sheet, and using the add/select method.
I am getting the above message on the add/select method in the code below.

Range(Base_QT_Dest).Offset(QT_Columns - 2, 2).Range("A1").Select
    CellX = ActiveCell.Left
    CellY = ActiveCell.Top
    CellH = ActiveCell.Height
    CellW = ActiveCell.Width
    ActiveSheet.Buttons.Add(CellX, CellY, CellW, CellH).Select

It has been working correctly, and is in fact adding one button successfully before crashing on the second button.

Any help is much appreciated.


The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[combo]

Any error info? You work with ActiveCell and ActiveSheet, I would test their references. There is no need to select cells:

With Range(Base_QT_Dest).Offset(QT_Columns - 2, 2).Cells(1, 1)
 ActiveSheet.Buttons.Add .Left, .Top, .Width, .Height
End With

combo

 

[shaunk]

Thankyou, I'll keep that construct in mind.

I ended getting this code to work which also avoids the select.

Range(Base_QT_Dest).Offset(QT_Columns - 2, 2).Range("A1").Select
     
    With ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height)
        .OnAction = "View_Reporting_Unit"
        .Characters.Text = "Menu"
        .Name = Button_Name
        .Font.Name = "Arial"
        .Font.Size = 7
    End With

It still baffles me as to why the select is suddenly causing a problem.

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[combo]

What is the error message? Have you tested what you try to select (what is the value of QT_Columns-2)?

combo

 

[shaunk]

In the original code, it was failing on this statement:
ActiveSheet.Buttons.Add(CellX, CellY, CellW, CellH).Select.

The message was "Select method of button class failed"

The range is itself is perfectly OK. I don't have the problem with the new code so I'll be avoiding selects in future. I'm sure they degrade performnce anyway.

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[xlbo]

Probably to do with the 1st button you added taking the focus. If the 1st button has focus, there is no activesheet.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question