You could also use the INDIRECT function to avoid using VBA altogether. To do this, you will need to reference a cell on the worksheet that contains the user's choice for worksheet name. Let's suppose this is cell F2. NateO's VLOOKUP formula can then be placed directly in the cell:
=VLOOKUP(INDIRECT(F2 & "!G2"),{-10000,"";31,"Informational";55,"First";63,"Second";71,"Final";79,"Discharge"},2)