Excel 2003
I have a button from the Forms toolbar named "Register".
The button is on the sheet - not on a form.

I would like to set the focus to this button in the following code

Code>>
Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value <> "" Then
Buttons("Register").SetFocus
Else
........... other code..........
End if

End Sub
<<Code

Obviously this does not work - it produces the following error:-

"Run-Time error '438':
Object doesn't support this property or method"

Any help appreciated

Sandy

Controls from the Forms menu do not have a SetFocus method. You could use
'Select' but I suspect you don't want to do that. You may want to change to
an ActiveX button, simply call the original code from its click event in the
worksheet module. You'll then be able to do -

ActiveSheet.OLEObjects("CommandButton1").Activate

Regards,
Peter T

"Sandy" <(E-Mail Removed)> wrote in message
news:802ABD16-C7D7-4CB0-96E7-(E-Mail Removed)...
> Excel 2003
> I have a button from the Forms toolbar named "Register".
> The button is on the sheet - not on a form.
>
> I would like to set the focus to this button in the following code
>
> Code>>
> Sub Worksheet_Change(ByVal Target As Range)
>
> If Range("A1").Value <> "" Then
> Buttons("Register").SetFocus
> Else
> ........... other code..........
> End if
>
> End Sub
> <<Code
>
> Obviously this does not work - it produces the following error:-
>
> "Run-Time error '438':
> Object doesn't support this property or method"
>
> Any help appreciated
>
> Sandy

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value <> "" Then
''Buttons("Register").SetFocus
ActiveSheet.Shapes("Register").Select
Else
'........... other code..........
End If

End Sub





"Sandy" <(E-Mail Removed)> wrote in message
news:802ABD16-C7D7-4CB0-96E7-(E-Mail Removed)...
> Excel 2003
> I have a button from the Forms toolbar named "Register".
> The button is on the sheet - not on a form.
>
> I would like to set the focus to this button in the following code
>
> Code>>
> Sub Worksheet_Change(ByVal Target As Range)
>
> If Range("A1").Value <> "" Then
> Buttons("Register").SetFocus
> Else
> ........... other code..........
> End if
>
> End Sub
> <<Code
>
> Obviously this does not work - it produces the following error:-
>
> "Run-Time error '438':
> Object doesn't support this property or method"
>
> Any help appreciated
>
> Sandy

Whats the point of selecting the button? Why don't you just trigger some
code with an input box ? I assume that you're "asking" the user to do
something?

"Sandy" <(E-Mail Removed)> wrote in message
news:802ABD16-C7D7-4CB0-96E7-(E-Mail Removed)...
> Excel 2003
> I have a button from the Forms toolbar named "Register".
> The button is on the sheet - not on a form.
>
> I would like to set the focus to this button in the following code
>
> Code>>
> Sub Worksheet_Change(ByVal Target As Range)
>
> If Range("A1").Value <> "" Then
> Buttons("Register").SetFocus
> Else
> ........... other code..........
> End if
>
> End Sub
> <<Code
>
> Obviously this does not work - it produces the following error:-
>
> "Run-Time error '438':
> Object doesn't support this property or method"
>
> Any help appreciated
>
> Sandy