MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

activate a sheet based upon cell value

  Asked By: Manju    Date: Dec 22    Category: MS Office    Views: 1067

I have a sheet with a number of radio buttons. Depending upon which
one is clicked depends which sheet the rest of the code works on.

I want to be able to read a cell on this sheet which will determine
which sheet the rest of the code will work with.

I get a runtime error with:

Private Sub baseline(yr)
ActiveSheet.Columns("A:AE").Select 'first select everything
Selection.EntireColumn.Hidden = False 'then unhide the columns
ActiveSheet.Columns("l:aa").Select 'Hide unwanted columns
Selection.EntireColumn.Hidden = True
End Sub

The baseline sub I call as follows:

Where A4 contains the text 'Sheet 6', the same as the sheet name.

Can anyone help me out?



3 Answers Found

Answer #1    Answered By: Ty Thompson     Answered On: Dec 22

"A4" is a string containing A4. So it's looking for a sheet  called "A4".
If you want to pass the value of A4, then it's A4.Value (no quotes).

Answer #2    Answered By: Grady Stewart     Answered On: Dec 22

You must pass the cell  value to the sub something like this:
Call baseline([a4].Value)

Answer #3    Answered By: Brendan Smith     Answered On: Dec 22

One line to rule them all!!!

Application.Run (Sheets(ActiveCell.Value).Select)

Didn't find what you were looking for? Find more on activate a sheet based upon cell value Or get search suggestion and latest updates.