What's the code for 'if commandbutton1 is clicked?

[Oliver Dewar]

Hi All,

I has an exit event in a textbox on a userform that sets focus on a combobox.

I have a 'Exit' button on the userform as well.

I simply need to say in the textbox exit event to setfocus on the combobox, unless the exit button is pressed.

Something like

private sub textbox1.exit (ByVal Cancel As MSForms.ReturnBoolean)
 
if not commandbutton1.click = True then
combobox1.setfocus
end if
 
end sub

But I don't know what the correct way to say commandbutton1.click = true is.

Thoughts ladies and gentlemen?

 

[Trebor76]

Hi there,

Try...

Application.Caller

...which will return the name of control clicked, except for labels.

Robert

 

[mikerickson]

If the Exit button closes the userform, why does it matter where the focus is?

Also, if the CommandButton1's TakeFocus******* property is True, the Exit event will run before the command button's code is run.

There is no way for the Exit event to tell why the textbox is being exited.

If it does matter, the focus could be controlled in the first line of the CommandButton1_Click event.

 

[Oliver Dewar]

Hi Mike,

Not totally following you yet...

To answer your question, the problem is that when I click on the exit button the setfocus overides the buttons code and instead of the userform closing, the focus simply moves.

 

[mikerickson]

Trebor,

The value of Application.Caller depends on how normal excel was left when going into VBA.

If the userform is invoked by a control on a worksheet, it will return the name of that control.
If the UF is invoked by F5 from the VBEditor,it will return an error.
If by a worksheet function, it will return the calling cell.

It will not return the value of a control on a userform.

 

[mikerickson]

Set the CommandButton's .TakeFocus******* property to False and all will be well. (Unless the user clicks on the corner X, in which case there is an error because the code is trying to set the focus to an invisible ComboBox.)

It might be best if you were to just change the Tab Order of the userform so that the ComboBox comes after the text box.

Or use the AfterUpdate event of the textbox.

 

[Oliver Dewar]

Sounds like tab order might be the one... I've already been over the tabs once but these are at the start... can you use negative numbers -3 -2 -1 is tab order?

 

[Oliver Dewar]

Actually, setting the button's takefocus seems to work well. What did you mean that the combobox would be invisible?

 

[Trebor76]

Hi Mike,

Thanks for the info

Robert

 

[mikerickson]

In the VBEditor under the View Menu, select Tab Order and use that to put things in the order you want.

No you can't use negative numbers for the TabIndex.
From VBEditor Help:

TabIndex:...An integer from 0 to one less than the number of controls on the form that have a TabIndex property. Assigning a TabIndex value of less than 0 generates an error. If you assign a TabIndex value greater than the largest index value, the system resets the value to the maximum allowable value.