CODEWITHSUNDEEP
excelvba
Serzh
Serzh

Reputation: 1

Move focus to a button on a worksheet

I have a worksheet with form control buttons. At the end of a macro called by one button I would like to set the focus to another button, so that if I see that the first button did the job, I could just hit Enter.

I searched the site and got this

ActiveSheet.Shapes("CommandButton1").Select

This selects the shape but Enter does not run it.

Methods of CommandButton do not work.

ActiveSheet.Shapes("CommandButton1").SetFocus

returns the "method not supported" error.

I got this other answer:

Me.CommandButton1.SetFocus

I get "Object needed" error.

This leads me to believe that either CommandButton# object does not exist in Excel VBA or that it refers to a form but my buttons are placed directly on the spreadsheet.

Tried

ActiveSheet.CommandButton1.SetFocus
ActiveSheet.Buttons("Button 1").SetFocus

I found an old sample code with something like this:

CommandButton2.Caption = "CONTINUE"
CommandButton1.Enabled = False
CommandButton3.Enabled = False

This works in that sample, but in that sample the buttons behave differently. I suspect they are ActiveX or something else instead of form control buttons. In that sample right-clicking on them does not bring up the context menu.

I tried

ActiveSheet.Buttons("Button 1").ControlFormat

But do not know what to use next. SetFocus does not work.

Upvotes: 0

Views: 7088

Answers (2)

Shai Alon
Shai Alon

Reputation: 990

On a form control button named "CommandButton1", you can call 

UserForm1.CommandButton1.SetFocus

This will make sure the focus is on the CommandButton1.

Upvotes: 0

Ulli Schmid
Ulli Schmid

Reputation: 1167

With an ActiveX control button named "CommandButton1", you can call

Me.CommandButton1.Activate

from the same worksheet.

Upvotes: 2

Related Questions