Shift Worksheet Tab

[tzinn1]

I am very new to VBA, but I am writing code to populate a spreadsheet with data retrieved from a barcode scanner. The overall purpose is to manifest all products placed on a trailer. I have written code to create a worksheet for every PO #. I would like to shift or move between the worksheet tabs by scanning a barcode with string "LEFT" or "RIGHT"

The PO numbers are not sequential or would utilize them. Not sure what to do. Any help is greatly appreciated.

 

[Norie]

What code do you currently have?

 

[Logit]

.
This following macro is from resource : http://www.vbaexpress.com/kb/getarticle.php?kb_id=536
There is a ZIP file containing a complete workbook example available for download.

Option Explicit


Sub GoForth()
    MoveSheet (1)
End Sub
 
Sub GoBack()
    MoveSheet (-1)
End Sub
 
Sub MoveSheet(iMove As Integer)
    Dim iSheetNum As Integer
     
    iSheetNum = ActiveSheet.Index
    On Error Resume Next
    Do
        Err.Clear
        iSheetNum = iSheetNum + iMove
        If iMove > 0 Then
            If iSheetNum > Sheets.Count Then iSheetNum = 1
        Else
            If iSheetNum < 1 Then iSheetNum = Sheets.Count
        End If
        Sheets(iSheetNum).Select
    Loop Until Err.Number = 0
     
End Sub

Have your scanner activate the GoForth or GoBack sub as required.

I would caution you using the terms LEFT and RIGHT as a variable. These are key words used by EXCEL VBA and may cause a conflict in your code. Having a command button with the caption LEFT or RIGHT ... or a label with the same caption should not cause an issue though.

 

[tzinn1]

.
This following macro is from resource : http://www.vbaexpress.com/kb/getarticle.php?kb_id=536
There is a ZIP file containing a complete workbook example available for download.

Option Explicit


Sub GoForth()
    MoveSheet (1)
End Sub
 
Sub GoBack()
    MoveSheet (-1)
End Sub
 
Sub MoveSheet(iMove As Integer)
    Dim iSheetNum As Integer
     
    iSheetNum = ActiveSheet.Index
    On Error Resume Next
    Do
        Err.Clear
        iSheetNum = iSheetNum + iMove
        If iMove > 0 Then
            If iSheetNum > Sheets.Count Then iSheetNum = 1
        Else
            If iSheetNum < 1 Then iSheetNum = Sheets.Count
        End If
        Sheets(iSheetNum).Select
    Loop Until Err.Number = 0
     
End Sub

Have your scanner activate the GoForth or GoBack sub as required.

I would caution you using the terms LEFT and RIGHT as a variable. These are key words used by EXCEL VBA and may cause a conflict in your code. Having a command button with the caption LEFT or RIGHT ... or a label with the same caption should not cause an issue though.

I got it working thanks to your help. I did not know about the index. Make sense. I am using the following code. I am not a structured programmer ...yet... but I will get there. I have not programmed since the 80s...I only knew basic and the code I am writing needs work....but its working. I did get into trouble with using the string left and right so I change to "left move" and right move". Thanks

If Target.Address(False, False) = "A1" And a = "LEFT MOVE" Then
Range("A1") = ""
idx = idx - 1
If idx < 1 Then idx = idx + 1
Sheets(idx).Select
End If
If Target.Address(False, False) = "A1" And a = "RIGHT MOVE" Then
Range("A1") = ""
idx = idx + 1
If idx > Sheets.Count - 1 Then idx = Sheets.Count - 1
Sheets(idx).Select
End If

 

[Logit]

.
Glad to know you found the answer. Wonderful !

Please don't forget to mark this thread SOLVED (up top, THREAD TOOLS) if this does indeed solve your question.

Cheers.