Button Focus
 

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

Button Focus
 

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" wrote in message
...
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

Button Focus
 

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" wrote in message
...
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

Button Focus
 

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" wrote in message
...
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