Hi, the code you posted for keeping certain tabs showing isn't working in the names array, the first code you posted in the video i got to work fine, can you help please?
hi. I have many tabs in my worksheet. If i only want to choose the tabs i want to include in the dropdown list, How will I unhide the tabs that I are not included in the dropdown list?
Hi Used your Code in my office template. However, minor change is required which I am unable to achieve though. Can you modify your code a bit. By default on opening of this file, only sheet "Data" will be visible and rest all other sheets will be hidden, and "Select a sheet" will appear in Range A2. Now depending upon selection in range A2, i.e. North:South:East:West, only respective Sheet will be unhidden first and made active (Assume North is selected in A2 which should make Sheet "North" be unhidden first and appear along with sheet "Data" and Sheet "North" will be the active Sheet for user to carry out Data entry). Once Data entry is done in Sheet "North" and when user selects sheet "Data" or Closes the Workbook, Sheet "North" will again get hidden and "Select a Sheet" will appear in range A2 in Sheet "Data" for user to selected another sheet and Cycle of operation continues. So, in other words, after selection in A2 only two Sheets will appear in any case. one "Data" and other selected from range A2. The Sheet "Data" is locked except for Range A2. All Data Entry in any sheet selected through Range A2 shall be automatically Saved. Thanks in advance.
Hello Hitesh, You can try this, in the WsNames array you can specify the sheets that you want to keep visible every time. I have uploaded the file here: drive.google.com/file/d/1t7SXs208OlcGBL74ory6cFniM67HSQD-/view?usp=sharing In the Sheet Data you have to enter this: ===================================================================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Ws As Worksheet Dim WsNames() As Variant
WsNames = Array(shData.Name, shAntriksh.Name) 'Specify the sheets that you always want to keep visible
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
For Each Ws In ThisWorkbook.Worksheets If UBound(Filter(WsNames, Ws.Name, True, vbTextCompare)) 0 Then If Ws.Name = Target.Value Then Ws.Visible = xlSheetVisible Else Ws.Visible = xlSheetHidden End If End If Next Ws
End If
End Sub ==================================================================== And in the ThisWorkbook Class Module you need to enter: Option Explicit Private Sub Workbook_Open()
Dim Ws As Worksheet Dim WsNames() As Variant
WsNames = Array(shData.Name, shAntriksh.Name) 'Specify the sheets that you always want to keep visible
For Each Ws In ThisWorkbook.Worksheets If UBound(Filter(WsNames, Ws.Name, True, vbTextCompare)) = 0 Then Ws.Visible = xlSheetVisible 'Can change Visible to xlSheetHidden if you want to keep it available in the Right click menu in Excel Else Ws.Visible = xlSheetHidden End If Next Ws
Hello Antriksh, I created all worksheets , then I inserted a new worksheet for filter and entered hide worksheet VB codes and changed the sheet name as per VB , however its not working ???
Rashmi D You will first have to unprotect the workbook then hide/unhide the sheets and then protect the workbook once again. You could add the unprotect code in the workbook open event and protect code in the workbook before close event, both of these are available in ThisWorkbook module
@@AntrikshSharma yes, the detailed query is posted on this link. Could you please have a look and help me understand how to fix this one www.excelforum.com/excel-programming-vba-macros/1329278-error-in-running-codes-in-protected-workbook.html#post5405614
Hi...so thanks for this amazing video🌸🌸🌸 I have lots of sheets and i need to know how can i have several of these sheets with just one option of droplist?
Hi,
the code you posted for keeping certain tabs showing isn't working in the names array, the first code you posted in the video i got to work fine, can you help please?
Awesome！
If we want to view two sheets at the same time, what is the code?
You can just go to View -> New Window -> View side by side
hi. I have many tabs in my worksheet. If i only want to choose the tabs i want to include in the dropdown list, How will I unhide the tabs that I are not included in the dropdown list?
Hi
Used your Code in my office template. However, minor change is required which I am unable to achieve though.
Can you modify your code a bit.
By default on opening of this file, only sheet "Data" will be visible and rest all other sheets will be hidden, and "Select a sheet" will appear in Range A2.
Now depending upon selection in range A2, i.e. North:South:East:West, only respective Sheet will be unhidden first and made active (Assume North is selected in A2 which should make Sheet "North" be unhidden first and appear along with sheet "Data" and Sheet "North" will be the active Sheet for user to carry out Data entry).
Once Data entry is done in Sheet "North" and when user selects sheet "Data" or Closes the Workbook, Sheet "North" will again get hidden and "Select a Sheet" will appear in range A2 in Sheet "Data" for user to selected another sheet and Cycle of operation continues. So, in other words, after selection in A2 only two Sheets will appear in any case. one "Data" and other selected from range A2.
The Sheet "Data" is locked except for Range A2.
All Data Entry in any sheet selected through Range A2 shall be automatically Saved.
Thanks in advance.
Hello Hitesh,
You can try this, in the WsNames array you can specify the sheets that you want to keep visible every time. I have uploaded the file here: drive.google.com/file/d/1t7SXs208OlcGBL74ory6cFniM67HSQD-/view?usp=sharing
In the Sheet Data you have to enter this:
=====================================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ws As Worksheet
Dim WsNames() As Variant
WsNames = Array(shData.Name, shAntriksh.Name) 'Specify the sheets that you always want to keep visible
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
For Each Ws In ThisWorkbook.Worksheets
If UBound(Filter(WsNames, Ws.Name, True, vbTextCompare)) 0 Then
If Ws.Name = Target.Value Then
Ws.Visible = xlSheetVisible
Else
Ws.Visible = xlSheetHidden
End If
End If
Next Ws
End If
End Sub
====================================================================
And in the ThisWorkbook Class Module you need to enter:
Option Explicit
Private Sub Workbook_Open()
Dim Ws As Worksheet
Dim WsNames() As Variant
WsNames = Array(shData.Name, shAntriksh.Name) 'Specify the sheets that you always want to keep visible
For Each Ws In ThisWorkbook.Worksheets
If UBound(Filter(WsNames, Ws.Name, True, vbTextCompare)) = 0 Then
Ws.Visible = xlSheetVisible
'Can change Visible to xlSheetHidden if you want to keep it available in the Right click menu in Excel
Else
Ws.Visible = xlSheetHidden
End If
Next Ws
End Sub
Hello Antriksh, I created all worksheets , then I inserted a new worksheet for filter and entered hide worksheet VB codes and changed the sheet name as per VB , however its not working ???
All good mate, works perfect
Hi, I am looking for a way to hide / unhide tabs in a protected workbook. is there a way? my excel has multiple private sub codes for multiple sheets.
Rashmi D You will first have to unprotect the workbook then hide/unhide the sheets and then protect the workbook once again.
You could add the unprotect code in the workbook open event and protect code in the workbook before close event, both of these are available in ThisWorkbook module
@@AntrikshSharma that’s how typically it is done. I am looking for a way around. I am using VB codes currently. Any thoughts on that
@@rashmid8294 Okay, I am not sure what you meant by way around. Are you automating it through VB.net?
@@AntrikshSharma yes, the detailed query is posted on this link. Could you please have a look and help me understand how to fix this one
www.excelforum.com/excel-programming-vba-macros/1329278-error-in-running-codes-in-protected-workbook.html#post5405614
Hi, I am trying to hide different worksheets based on multiple cell value. Can you help?
I know it is kinda randomly asking but do anyone know a good place to watch newly released tv shows online?
@Ben Raphael i watch on FlixZone. Just google for it =)
@Ben Raphael I watch on Flixzone. You can find it by googling :)
Hi...so thanks for this amazing video🌸🌸🌸 I have lots of sheets and i need to know how can i have several of these sheets with just one option of droplist?
Getting For error
need sample file
Thanks