Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007-macro enabled, multi-sheet opening in minimized mode | Excel Discussion (Misc queries) | |||
Excel blank sheet opening but the sheet has data | Excel Discussion (Misc queries) | |||
Opening at specific cell - No Macro | Excel Discussion (Misc queries) | |||
opening sheet, next available cell displayed | New Users to Excel | |||
Opening an excel sheet but i don't see it | Excel Discussion (Misc queries) |