Simulate tab order for form controls dropped into spreadsheet

[Chats]

I am trying to achieve the same thing discussed previously in thread707-602893

I have textbox objects dropped directly onto an Excel spreadsheet (i.e. not in a VBA form). I would like to provide the functionality for my users to tab between textbox fields on my spreadsheet. Because I have dropped the form objects directly onto the spreadsheet, I do not have the property available to set the tab order.

The previous discussion (referenced above) hints at how to do this, but does not give me enough to be able to put it into practice. Can anybody suggest a way to do this?

Thanks

Adrian

 

[fumei]

As Geoff mentions, you can mess around with events for ActiveX objects (eg. textboxes from the Controls toolbar), but you can NOT do so with Forms objects (eg. textboxes from the Forms toolbar).

If you are using Forms textboxes....you can't.
If you are using ActiveX textboxes...you can.

Gerry
My paintings and sculpture

 

[Chats]

Thanks for responding so fast... I am using ActiveX textboxes, so can do something with the events, but I am not sure which event to use for this - if I click into a textbox in my "form" and then press the tab key, the cursor stays in the same text box, so this won't (I presume) trigger any of the events such as lostfocus etc.

Any suggestions?

 

[Chats]

Bingo - I have found a solution, here it is for anybody struggling with the same thing in the future.

In this example, two textboxes (ActiveX version) embedded in a spreadsheet. Set the following code as an event for textbox1

Private Sub textbox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then Textbox2.Activate
End Sub

The only downside is that I have to manually code an event like this for every textbox on the form. Still, it works.

Can anybody suggest a way to do this with one piece of code for the whole sheet?

 

[fumei]

I don't understand. You are activating an explicit object

[COLOR=red]Textbox2

.Activate[/code]
What would one piece of code DO???

The code states for textbox1 keycode 9 activates textbox2. It is the explicit event code for textbox1. No matter what, you will have to have an event code procedure for each object.

Gerry
My paintings and sculpture

 

[Chats]

Fumei,

The code responds to the keyup event and checks to see if the keycode of the keypress detected = 9 (tab key). If it does, the .activate on textbox 2 switches the cursor to textbox2 (from textbox1).

I will then need a similar piece of code in textbox2 which switches the cursor to textbox3 (with textbox3.activate) and so on for all the textboxes on the spreadsheet.

My question is whether there is another way of coding this, for instance to detect all instaces of a tabkey press, then ideentify whether the cursor is currently inside a textbox (rather than elsewhere on the spreadsheet) - if it is, identify which textbox it is in, then shift the cursor focus to the next textbox in a pre-coded sequence.

This would be tidier in terms of coding as with 100 textboxes I would need 100 sub procedures, however the method I have generated does do what I need.

Thanks

Ade