Forum Discussion

TARUNKANTI1964's avatar
TARUNKANTI1964
Brass Contributor
May 12, 2022

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 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   

    • TARUNKANTI1964's avatar
      TARUNKANTI1964
      Brass 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
  • 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

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP

      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?

  • Jim_Stiene's avatar
    Jim_Stiene
    Copper Contributor

    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

Resources