Setfocus from inside of Frame

[chipsworld]

Quick question...

I am trying to set focus to a CMD button outside of a frame from a text box inside the frame.

I get an error msg "Run-time error..... "Unexpected call to method or property.

I have tried the setfocus …. frmlvecalc.cmdAdd2.SetFocus and me.cmdadd2.setfocus.

Is there something special about doing this from inside a frame? I know they have their own Tab Index, etc.

Thanks!

 

[chipsworld]

OK...I have been researching this issue all afternoon, trying various things, and my conclusion is that there just is no way to accomplish this that makes sense.

If someone has any ideas, here is my setup... All I need is a way to exit the textbox and frame and set focus on a command button...this should not be this hard!

The frame has three text boxes that are tabindex 0,1 and 2. The frame is NOT a tab stop. I did this because the text boxes inside are only for a very special case, and not part of the normal flow of data input, and I do not want the end user to have to go through those input spaces. (Trying to keep everything on one form.) Therefore, when the user in inputting data and they get to the end of the normal run, the tab order takes them straight to the command button to copy the data to the sheet. If this special situation arrises, they simply click in the first text box and then can tab through to the second box automatically.

The first two text boxes are simple inputs, and the third is calculated based upon the inputs from 1 and 2. (1+2=3) Pretty straight forward.

What I want to accomplish is: After the data is input into testbox2, the calculation runs on the "afterupdate" trigger. I then wanted to simply jump to the command button that loads it all to the sheet where everything else takes place. The issue is that the Command button is OUTSIDE of the frame.,

I have read about Exit events, and all types of stuff, but it seems like no one has ever found a solution to this issue.

Thanks as always to the Mr Excel crew!

 

[mikerickson]

Something like this should work.

CommandButton1.SetFocus

But, you mention that this is happening in an AfterUpdate event. So the focus is kind of in between controls. Why not set up your tab order so that the command button is next in line after the frame. (Even though the frame isn't a tab stop, its still in the tab order.)

 

[chipsworld]

Mike...
I have tried that way...no go.
The reason it is set up the way it is … as I outlined above...there are three text boxes inside the frame. These three text boxes are only used for a very specific need, and that need is very rare. I put them in the frame so that the end user would not need to enter/tab through them when data entering. I still need to capture the data if this need arises.

What I am trying to do is...If that rare occasion does come up, and the end user needs to enter these values, after entering the second, and the third is generated through code (1+2=3) the focus would then be set to the command button that adds the data to the sheet where everything else takes place.
The frame is set to tabstop = false so that when the end user is inputting data, and they do not need these fields, he tab order will just jump past directly to the same command button.

Currently, the cmd button is the next in the tab order after the last field in the normal input process. however, when I do enter the frame fields and enter data and the afterupdate event runs, the focus jumps to the very first field in the form outside of the frame.

Yes...The 1+2=3 action takes place after the second field's data has been entered and the end user presses he enter/tab button. The third field is enabled, but locked, so no other action is available to the user. Should I maybe use the EXIT event for the action? I am open to ideas...

I am attaching a pic of the userform. The three fields are at the bottom above the yellow info line. Parental Leave Start Date, NO of Days and End Date. THe End Date is auto calculated.
You can't see the frame because I have it that way on purpose...no need for it to show.

I could show you the calculated code in the afterupdate of the second box, but it is a simple "dateadd" to generate the End Date... This could easily be moved to an Exit event instead if the end result would be met to set the focus on the "Click to Continue" button.

I am open to any ideas, but I really don't think it is possible to do what I want without some kind of crazy API work or something else that is WAAAY! outside my skill level. From what I have found in my research, it seems like the frame thing is just a design goof by Microsoft.

Sorry for the run on, just trying to be as thorough as possible...


It really isn't a show stopper, as the end user can simply click on the cmd button manually and complete the action. To be honest, at this point it has become more of a challenge to get it to bend to my will that anything else.

 

[Jaafar Tribak]

Here is a workaround trick :

1- Add a new Standard Module and add this code to it:

Option Explicit

Dim oCmndBttn As CommandButton

Sub SetCommandButtonFocus(ByVal Ctrl As CommandButton)
    Set oCmndBttn = Ctrl
    Application.OnTime Now, "SetFocusNow"
End Sub

Sub SetFocusNow()
    oCmndBttn.SetFocus
End Sub

2- In the _AfterUpdate event of the TextBox in the UserForm Module :

Change TextBox and CommandButton names as required.

Private Sub TextBox1_AfterUpdate()
    Call SetCommandButtonFocus(CommandButton1)
End Sub

 

[chipsworld]

JaaFar,
Works perfectly...thanks! As always, the goal is to minimize the end user from making mistakes as much as possible, and this will definitely help!