Solved: move to textbox in other sheet if is "TAB" pressed

[danovkos]

Hi all,
pls. how can i do following.
I want anytime, whne i press key TAB in sheet "view" to move to my TEXTbox in sheet "ALL".
I tried this, but it didnt works

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
   If Sheets = Sheets("view").Select Then
    With TextBoxALL
        If KeyCode = vbKeyTab Then
                    With TextBoxALL
                    TextBoxALL.Value = ""
                    Call VlozCIFdoVIEW_Click
        End If
    End With
End Sub

[GTO]

Greetings danovkos,

Just a start, but see if this is on the right path...

In a throwaway copy of your wb:

Change the codename of the sheet named (tab name) "ALL" to 'shtAll'.

In the worksheet module for "View":
[vba]
Option Explicit

Private Sub Worksheet_Activate()
Application.OnKey "{TAB}", "GoToTextBox"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{TAB}"
End Sub
[/vba]

In a Standard Module:
[vba]
Option Explicit

Sub GoToTextBox()
shtAll.Activate
'// Change to codename of textbox //
shtAll.TextBox1.Activate
End Sub
[/vba]

I would also incude code in Workbook_Deactivate abd Workbook_Activate.

Does that help?

Mark

[danovkos]

thx for try
this code return error - variable not defined
and stops - debug in this code

Sub GoToTextBox()
    shtAll.Activate
     '// Change to codename of textbox   //
    shtAll.TextBoxALL.Activate
'    All.TextBoxALL.Activate
End Sub

on row

shtAll.Activate

and question
is it neccessary to rename my sheet "ALL" to "shtALL" because i have more codes with shotcut to this sheet ALL
thx

[GTO]

Hi there,

I did not say to rename the sheet. I said to change the sheet's codename. While sheet "All" is active, right-click the sheet's tab and select View Code.

In the properties window, you will see that the worksheet has two Name properties. The one at the top with the parenthesis around it is the sheet's codename. Change that one; it will not affect code that refers to the worksheet name.

Mark

[danovkos]

sorry i didnt understand it first time..

but great it works very good as i wish

thank you very much for you time and help

[GTO]

Dear Danovkos,

I just looked at my last as well as your response. If I sounded the least bit terse, that was not my intent and my apologies if you took it that way.

I am glad it is working, adn remember what I said about the workbook activate/deactivate events. This way if you have two workbooks open, we don't accidently leave the OnKey calling the procedure. Maybe something like:

In ThisWorkbook Module:
[vba]
Option Explicit

Private Sub Workbook_Activate()
If ActiveSheet.Name = "View" Then
Application.OnKey "{TAB}", "GoToTextBox"
End If
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{TAB}"
End Sub
[/vba]

A great day to your and yours,

Mark

[danovkos]

thank you
have a nice day

[danovkos]

i dont know, but it sometimes doesnt works after opening wb
know anyone where can be a problem?

[danovkos]

now i figured out, that it doesnt works after start
but when i click on other tab and return in sheet view, it start working
???

[GTO]

Sorry about that. I just spotted that you have the sheet named in lower-case. Change to:
[vba]
Private Sub Workbook_Activate()
If ActiveSheet.Name = "view" Then
Application.OnKey "{TAB}", "GoToTextBox"
End If
End Sub
[/vba]

I think that should catch it :-)

Mark

[danovkos]

thx, but it doesnt helps.
but it helps, when i add this code to
"thisworksbook"

Private Sub Workbook_Open()
...
Application.OnKey "{TAB}", "GoToTextBox"
...

now it works immediately
but thx

[GTO]

Gosh Danovkos, that's a mystery to me. Even in my 'poor ol' laptop' the wb activate and deactivate events occur w/o anything in wb_open.

Just to make sure, I tested:
[vba]
Option Explicit

Private Sub Workbook_Activate()
MsgBox "Workbook_Activate()"
End Sub

Private Sub Workbook_Deactivate()
MsgBox "Workbook_Deactivate()"
End Sub
[/vba]

Both fired. Well anyways, sure glad its working now

Mark

[danovkos]

OK
thank you for your help.