Using Command buttons in Excel

[bikerted]

I'm getting error 1004 "select method of range failed" when I try the following:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("OrgTable&quot.Select
Columns("H:H&quot.Activate
Selection.Find(What:=Range("H1&quot, 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&quot but not to perform other actions within the other worksheet.
Is there any way round this, please.

Ted

 

[rdodge]

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

 

[bikerted]

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.

 

[xlbo]

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