Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Method of Button Class Failed

Status
Not open for further replies.

shaunk

Programmer
Aug 20, 2001
402
AU
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.

Code:
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

 
Any error info? You work with ActiveCell and ActiveSheet, I would test their references. There is no need to select cells:
Code:
With Range(Base_QT_Dest).Offset(QT_Columns - 2, 2).Cells(1, 1)
 ActiveSheet.Buttons.Add .Left, .Top, .Width, .Height
End With

combo
 
Thankyou, I'll keep that construct in mind.

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

Code:
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

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

combo
 
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

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top