Forum Discussion
TARUNKANTI1964
May 12, 2022Brass Contributor
Excel-Vba
Hi, I have an Excel Workbook Having 5(Five) No’s of Excel Sheet in named 1) DASH BOARD,2) Sheet1,3) Sheet2,4) Sheet3,5) Sheet4. In DASH BOARD Sheet there is 4(Four) No’s Button exist in named GoToShe...
- May 12, 2022
See the attached sample workbook. Code in Module1 and in ThisWorkbook.
HansVogelaar
MVP
See the attached sample workbook. Code in Module1 and in ThisWorkbook.
TARUNKANTI1964
May 14, 2022Brass Contributor
Respected "Mr.Hans Vogelaar"
I think it will help me enough.
I know that the "TEAM MEMBERS OF THIS COMMUNITY" are always very busy to solve many kinds of various Problem "SUBMITTED or DISCUSSED" by inexperienced members like me,at the same time I am very ASHAMED that I could not help all these "IMPORTANT COMMUNITY MEMBERS OF THIS COMMUNITY" in any way.That's why I know that no matter should be "DISCUSSED OR SUBMITTED" twice.
Despite knowing every thing, we are inexperienced like as me often make the same mistake over and over again,such as this i had posted a problem
on 12-05-2022 as given below and got also help almost immediately from Respected "Mr. Hans Vogelaar" the "IMPORTANT MEMBER" of this "COMMUNITY" on 12-05-2022
Hi,I have an Excel Workbook Having 5(Five) No’s of Excel Sheet in named 1) DASH BOARD,2) Sheet1,3) Sheet2,4) Sheet3,5) Sheet4. In DASH BOARD Sheet there is 4(Four) No’s Button exist in named GOTOSheet1, GOTOSheet2, GOTOSheet3, GOTOSheet4, by on which pressing I go to schedule Sheet. For this I have VBA Code as below. This Excel Book looks as is usually the case with normal Excel Book.
Sub Go_To_Sheet1()
ThisWorkbook.Sheets("Sheet1").Activate
End Sub
Sub Go_To_Sheet2()
ThisWorkbook.Sheets("Sheet2").Activate
End Sub
Sub Go_To_Sheet3()
ThisWorkbook.Sheets("Sheet3").Activate
End Sub
Sub Go_To_Sheet4()
ThisWorkbook.Sheets("Sheet4").Activate
End Sub
Now I want VBA Code to hiding of 4(Four)Excel Sheet (Sheet1, Sheet2, Sheet3, Sheet4) except “DASH BOARD” Excel Sheet of this Excel Workbook. Actually when I want to open this Excel Workbook, only “DASH BOARD” Excel Sheet will be visible. When I want to perform any Excel Sheet of this Excel Workbook so that I can open the Excel Sheet by clicking same named BUTTON. After that the schedule Excel Sheet will be disappear by close (X) when task is complete, and again “DASH BOARD” Sheet will be visible only. Being new to VBA looking for VBA Code in this regards.
Help from Respected "Mr.Hans Vogelaar"
Sub Go_To_Dashboard()
Dim i As Long
Worksheets("Dashboard").Activate
For i = 1 To 4
Worksheets("Sheet" & i).Visible = False
Next i
End Sub
Sub Go_To_Sheet(n As Long)
Dim i As Long
For i = 1 To 4
Worksheets("Sheet" & i).Visible = (i = n)
Worksheets("Sheet" & n).Activate
Next i
End Sub
Sub Go_To_Sheet1()
Call Go_To_Sheet(1)
End Sub
Sub Go_To_Sheet2()
Call Go_To_Sheet(2)
End Sub
Sub Go_To_Sheet3()
Call Go_To_Sheet(3)
End Sub
Sub Go_To_Sheet4()
Call Go_To_Sheet(4)
End Sub
but what will be the VBA Code if no's sheets may increas / decrease and name of the sheets maybe change which i did not foolishly post that matter, but that's what I should have done.and what will be the VBA CODE for Below.
such as "If there are 13 sheets instead of five sheets including "DASH BOARD Sheet" and if the name change as A,B,C,D,E,F,G,H,I,J,K,L,M,instead of Sheet1,Sheet2,Sheet3......,and onwards.
Shall be highly obliged ever
Thanking You Mr.Hans Vogelaar
I think it will help me enough.
I know that the "TEAM MEMBERS OF THIS COMMUNITY" are always very busy to solve many kinds of various Problem "SUBMITTED or DISCUSSED" by inexperienced members like me,at the same time I am very ASHAMED that I could not help all these "IMPORTANT COMMUNITY MEMBERS OF THIS COMMUNITY" in any way.That's why I know that no matter should be "DISCUSSED OR SUBMITTED" twice.
Despite knowing every thing, we are inexperienced like as me often make the same mistake over and over again,such as this i had posted a problem
on 12-05-2022 as given below and got also help almost immediately from Respected "Mr. Hans Vogelaar" the "IMPORTANT MEMBER" of this "COMMUNITY" on 12-05-2022
Hi,I have an Excel Workbook Having 5(Five) No’s of Excel Sheet in named 1) DASH BOARD,2) Sheet1,3) Sheet2,4) Sheet3,5) Sheet4. In DASH BOARD Sheet there is 4(Four) No’s Button exist in named GOTOSheet1, GOTOSheet2, GOTOSheet3, GOTOSheet4, by on which pressing I go to schedule Sheet. For this I have VBA Code as below. This Excel Book looks as is usually the case with normal Excel Book.
Sub Go_To_Sheet1()
ThisWorkbook.Sheets("Sheet1").Activate
End Sub
Sub Go_To_Sheet2()
ThisWorkbook.Sheets("Sheet2").Activate
End Sub
Sub Go_To_Sheet3()
ThisWorkbook.Sheets("Sheet3").Activate
End Sub
Sub Go_To_Sheet4()
ThisWorkbook.Sheets("Sheet4").Activate
End Sub
Now I want VBA Code to hiding of 4(Four)Excel Sheet (Sheet1, Sheet2, Sheet3, Sheet4) except “DASH BOARD” Excel Sheet of this Excel Workbook. Actually when I want to open this Excel Workbook, only “DASH BOARD” Excel Sheet will be visible. When I want to perform any Excel Sheet of this Excel Workbook so that I can open the Excel Sheet by clicking same named BUTTON. After that the schedule Excel Sheet will be disappear by close (X) when task is complete, and again “DASH BOARD” Sheet will be visible only. Being new to VBA looking for VBA Code in this regards.
Help from Respected "Mr.Hans Vogelaar"
Sub Go_To_Dashboard()
Dim i As Long
Worksheets("Dashboard").Activate
For i = 1 To 4
Worksheets("Sheet" & i).Visible = False
Next i
End Sub
Sub Go_To_Sheet(n As Long)
Dim i As Long
For i = 1 To 4
Worksheets("Sheet" & i).Visible = (i = n)
Worksheets("Sheet" & n).Activate
Next i
End Sub
Sub Go_To_Sheet1()
Call Go_To_Sheet(1)
End Sub
Sub Go_To_Sheet2()
Call Go_To_Sheet(2)
End Sub
Sub Go_To_Sheet3()
Call Go_To_Sheet(3)
End Sub
Sub Go_To_Sheet4()
Call Go_To_Sheet(4)
End Sub
but what will be the VBA Code if no's sheets may increas / decrease and name of the sheets maybe change which i did not foolishly post that matter, but that's what I should have done.and what will be the VBA CODE for Below.
such as "If there are 13 sheets instead of five sheets including "DASH BOARD Sheet" and if the name change as A,B,C,D,E,F,G,H,I,J,K,L,M,instead of Sheet1,Sheet2,Sheet3......,and onwards.
Shall be highly obliged ever
Thanking You Mr.Hans Vogelaar
- HansVogelaarMay 14, 2022MVP
With so many sheets, I'd do it differently. Instead of a button for each sheet, I'd use a drop-down list to select the sheet, and just one button. See the attached version.
- TARUNKANTI1964May 16, 2022Brass ContributorThis is exactly what I wanted. Excellent done for me indeed.
Respected, Mr. Hans Vogelaar, I have no language to thank You, nevertheless I thank You very much. But I am very sorry that I could not make any contribution to this “MICROSOFT TECH COMMUNITY” for not having enough education on this subject of mine, and I have no problem admitting it. Thank You Very Much Again.
TARUNKANTI1964- HansVogelaarMay 16, 2022MVP
You're welcome!