Excel-Vba

[TARUNKANTI1964]

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   

[#2 · Accepted solution]

@TARUNKANTI1964 

See the attached sample workbook. Code in Module1 and in ThisWorkbook.

[#3]

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

[#4]

@TARUNKANTI1964 

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.

[#5]

This 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

[#6]

@TARUNKANTI1964 

You're welcome!

[#7]

Bom dia, 

Preciso de uma macro que pesquise por setor toda vez que eu inserir a palavra "pendente" no campo Status Kaizen e apareça como uma lista no campo Manchete das ideias , buscando dentro da aba "AMBIENTE CONSULTA GERAL",permitindo que eu abra com um botão de comando o formulário já filtrado de acordo com o campo Status Kaizen.  E ao selecionar e editar o campo na COLUNA I (prazo Análise Setor) a data apareça no dentro do formulário no campo do Prazo Análise Setor. E tenha um botão de gravar a informação e passar para linha seguinte.Montei o formulário mas não sei qual macro inserir.

Segue exemplo da base dados

[#8]

@Diego_Henrique_Dias 

Você poderia anexar uma pequena pasta de trabalho de amostra demonstrando o problema (sem dados confidenciais) ou, se isso não for possível, disponibilizá-la por meio do OneDrive, Google Drive, Dropbox ou similar?

[#9]

@HansVogelaar 

Não está aparecendo o campo de inserir o arquivo.

[#10]

That's pretty common.
Sub HideSheets() 'hide sheets not named DASHBOARD
Dim i As Integer, Sh1 As String
For i = 1 To ThisWorkbook.Sheets.Count
Sh1 = Sheets(i).Name
On Error Resume Next
If Sh1 <> "DASHBOARD" Then
Sheets(Sh1).Visible = xlSheetHidden
End If
Next i
End Sub

Sub UnHideSheets() 'unhide sheets
Dim i As Integer
For i = 1 To ThisWorkbook.Sheets.Count
On Error Resume Next
Sheets(i).Visible = xlSheetVisible
Next i
End Sub