Barcode Excel File

[mayoub]

Hi Guys,

Is there any possible way on excel, where i can have multiple sheets filled with information, and where i can have a barcode on each sheet,
The needed is a program where i can just insert the barcode or the reference number in a cell and get directed to the sheet with the similar barcode or reference number,

For example:
Sheet 1 will have the name, date of birth and gender for a Jhon - it is referenced under one code 112
Sheet 2 will have the same details for Marie - it is referenced under the code 113
and so on (probably 300 sheets)

What i need is to enter the reference (it will be a barcode with a barcode reader) and the file will direct me to the sheet with that code,

Thanks,

 

[yky]

Can you use the bar code as the name of the sheet, e.g. 112 for Jhon? Seems to me it'd be quite easy to write a code to display the proper barcode page though I haven't done it.

 

[mayoub]

Can you use the bar code as the name of the sheet, e.g. 112 for Jhon? Seems to me it'd be quite easy to write a code to display the proper barcode page though I haven't done it.

Yes YKY it can be the name of the sheet,

 

[yky]

Suppose you have the barcode in the cell A1 of the sheet called Main. Enter the following code in a module:

Sub Activate_Sheet()

Sheets(Sheets("Main").Range("A1").Value).Activate

End Sub

While still on Sheet Main, click Developer\Insert\Form Controls and select the first item, a button. Then, click somewhere on Main, a window will pop up. Select the sub you just added. This is to associate the code to the button.

Now, enter a barcode, say, 112, in A1 of Main and, then, click the button. Sheet 112 will be activated. This is, of course, assuming that you have already named the sheet 112.

 

[mayoub]

Ok it's working, is there a way to automatically name the sheets without having to enter the barcode on each?

Thanks a lot

 

[Yongle]

Is the barcode value in the same cell in every sheet?
Perhaps....

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
   ws.Name = ws.Range("A1"). Value
Next ws

 

[mayoub]

Needs the sheet names to be as per the following:
112,113,114,115...

Total of 300 sheets,

Thanks,

 

[Yongle]

Perhaps...

Dim s As Integer
For s = 1 To ThisWorkbook.Worksheets.Count
  Sheets(s).Name = s + 111
Next s

 

[yky]

With the number of sheets you have, you probably need another button on every sheet except Main so you can click it and go back to Main in case you need to select another sheet.

 

[Yongle]

- alternative to get back to first sheet would be to add a shortcut key to macro below
- Macro / Options / ShortCut Key
- would work from any sheet

Sub GoToFirstSheet()
    Sheets(1).Activate
End Sub