Macro User Input Directs to Tab

[Jer-XL-emy]

Hello, It been awhile since I have ask for help and come to the place where the macro genuises reside.

My question, (I thought would be an easy one to search for but I guess my key words do not work)

I want a user input box to pop up when a workbook is open and depending on the user input direct to a certain tab (worksheet).

To be more specific I have one worksheet dedicated and formatted for on screen viewing and I have another formatted for print.

depending for what the user needs it for is the tab where they will get directed.

Any ideas.

Thanks in advance for your help : )

 

[iliace]

Simplest way, with obvious modifications. Put this in the ThisWorkbook module.

Private Sub Workbook_Open()
  Dim answer As VBA.VbMsgBoxResult
 
  answer = VBA.MsgBox("Click Yes for printing." & vbCrLf & _
              "Click No for viewing." & vbCrLf & _
              "Click Cancel to exit.", vbYesNoCancel)
 
  If answer = vbCancel Then
    ThisWorkbook.Close False
  ElseIf answer = vbYes Then
    ThisWorkbook.Worksheets("Printing").Activate
  Else
    ThisWorkbook.Worksheets("Viewing").Activate
  End If
 
End Sub

 

[Jer-XL-emy]

Thank you very much iliace - it works great manually - although how do I get it to do it automatically when the workbook opens? - what step am I missing?

 

[iliace]

There are two types of code modules available in Excel VBA: Standard Module and Class Module. There are three special instances of class modules as well: Form Module, Sheet Module, and Workbook Module. Any event that pertains to a class (user-defined classes, form classes, sheet classes, and workbook classes) must go in that object's code module.

Workbook_Open is a workbook event, therefore it must be placed in the workbook module. By default, the module is called ThisWorkbook. The code will not auto-run when placed in a standard module, or any of the other special class modules.

 

[Jer-XL-emy]

It Works like a charm - Thank you very much iliace - sorry I forgot how the VBA editor interface works as it been a while since I need a macro - thanks the script and showing my monkey brain how to use it - have a great day

 

[iliace]

Enjoy yours