Excel macro - Opening a sheet in accordance with the value in a particular cell
 

this is what i want to do.

the first sheet is named "SheetZero", in it the cell A1 contains
value which can vary between 1 and 20. SheetZero also contains a butto
with a macro assigned to it.

additionally there are twenty more sheets named Sheet1, Sheet2, ..
etc., to Sheet20.

when the value in cell A1 in SheetZero is changed, and the macro i
activated by clicking the button, i want another sheet to be opened, i
such a manner that it is based on the value in cell A1.

if the value is 1, then Sheet1 should open. if the value is 2, the
Sheet2 should open. if the value is 3, then Sheet3 should open... so o
and so forth ... if the value is 20, then Sheet20 should open.

please help me with the VB code for the macro. i am a novice in macr
writing, so please help me accordingly.

thanks

--
Message posted from http://www.ExcelForum.com

Excel macro - Opening a sheet in accordance with the value in a particular cell
 

Hi
try something like
Worksheets("Sheet" & activesheet.range
("A1").value).activate

-----Original Message-----
this is what i want to do.

the first sheet is named "SheetZero", in it the cell A1
contains a
value which can vary between 1 and 20. SheetZero also
contains a button
with a macro assigned to it.

additionally there are twenty more sheets named Sheet1,
Sheet2, ...
etc., to Sheet20.

when the value in cell A1 in SheetZero is changed, and
the macro is
activated by clicking the button, i want another sheet to
be opened, in
such a manner that it is based on the value in cell A1.

if the value is 1, then Sheet1 should open. if the value
is 2, then
Sheet2 should open. if the value is 3, then Sheet3 should
open... so on
and so forth ... if the value is 20, then Sheet20 should
open.

please help me with the VB code for the macro. i am a
novice in macro
writing, so please help me accordingly.

thanks.


---
Message posted from http://www.ExcelForum.com/

.

Excel macro - Opening a sheet in accordance with the value in a particular cell
 

Hi

you could add this bit of code to the buttons click event

either one or the other

Code:
--------------------

Dim mySheet As Integer
'get value from Cell A1
mySheet = Range("A1").Value
'activates the sheet by position
Worksheets(mySheet + 1).Activate

'or you could do it this way
Dim MySheetName As String
'get name of sheet
MySheetName = "sheet" & Range("A1").Value
'open the sheet by name
Worksheets(MySheetName).Activate

--------------------



HTH

David


---
Message posted from http://www.ExcelForum.com/