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!

Using Command buttons in Excel

Status
Not open for further replies.

bikerted

Technical User
Nov 7, 2003
221
GB
I'm getting error 1004 "select method of range failed" when I try the following:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("OrgTable").Select
Columns("H:H").Activate
Selection.Find(What:=Range("H1"), After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Application.ScreenUpdating = True
ActiveCell.EntireRow.Select
End Sub
It allows me to select the another worksheet ("OrgTable") but not to perform other actions within the other worksheet.
Is there any way round this, please.

Ted
 
First off, if at all possible, try to avoid using the Select/Activate methods and the Active<object> objects as this can present some unwanted issues down the road. There are few cases to use these, but a good majority of the times, it's highly recommended not to use them.

Example code that would work is shown below:

Private Sub CommandButton1_Click()

'Declare all of your variables
Dim WB as Workbook, WS as Worksheet, SearchRange as Range, CriteriaRange as Range, C as Range, ResultRange as Range

'Set your Object Variables
Set WB = Thisworkbook 'This assumes the code is ran on the same workbork as the macro is in.
Set WS = WB.Worksheets(&quot;OrgTable&quot;)
Set CriteriaRange = WS.Range(&quot;H1&quot;)
Set SearchRange = WS.Range(&quot;H:H&quot;)
Set C = SearchRange.Find(CriteriaRange.Value,CriteriaRange,xlValues,xlPart,,xlNext,False)
If Not C is Nothing Then
'Criteria was found within search range
Set ResultRange = C.EntireRow
End If
End Sub

In the sample code that I have provided, you are not manipulating the code via the activation of the different cells and worksheets like you are in your code, but rather through references to the different objects, which weeds out a lot of the different types of issues that you tend to have otherwise via selection/activation methods. You probably learned the activation method via the macro recorder, which is how I started learning VBA, but I also learned of the issues early on by using the activation methods as I tend to be one of those users that wants to use the computer for other tasks (outside of the instance of Excel that's currently running the code of course), thus had me find better ways to refer to the different objects and forced me to learn VBA much quicker.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
 
Thank you for you assistance - It's good to know that the experts started at my basic level. I can see I have a long way to go.

Thanks Again.

Regards, Ted.
 
Also - make sure that the &quot;TakeFocusOnClick&quot; property of the commandbutton is set to false (if you are using a controls toolbox commandbutton)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top