In keeping with SSIS' apparent campaign to stamp out encapsulation, the sheet name variable is hidden in the Excel Source\Advanced Editor\Component Properties\ OpenRowsetVariable, which in my case I set to User::TabName. (I did not have advanced knowledge of the tabs/range names, which I had to find in a Script task which I copied from a MS MSDN page and set the TabName variable.)

UPDATE:

I probably led you astray with the ablve answer, which I left after inspecting a task in which I also had to specify an Excel sheet name. The more direct way is in the Excel Source Editor (your input data flow task) -- not the Advanced Editor. There, set your "Data access mode:" to "Table name or view name variable", and set the "Variable name:" field to the name of your variable containing the range or sheet name, in my case "User::TabName". Keep in mind that, unlike range names, the Tab Names are suffixed with a "$", e.g. "My Sheet1$".

How in the world does anyone know whether to look in an Editor", an "Advanced Editor, the parameters of a task, or its Expressions to find/use a needed setting? It is madness!