Strawbunny
New Member
- Joined
- Feb 2, 2017
- Messages
- 13
Hi!
I have a script I use that will uses a dropdown menu to select a reagent and the associated form with that reagent is unhidden and the forms for other reagents are hidden.
It works really well for that purpose, but I was wondering if there is a possibility to make this potential change:
In addition to each reagent having an associated form, there is also a sheet for analysis that follows this rule:
REDI-Mix A, REDI-Mix B, DirectPrime, and DirectWash all use the Reagent Analysis sheet
Control Oligo Mix uses control oligo mix analysis sheet
Controls uses Controls Analysis and NTC analysis sheet
Reagent Box uses Reagent Box Analysis sheet
What I'd like is to keep the original function of my script that shows/hides forms based on which reagent is being tested.
What I'd like to add is if the above reagents are selected, then their respective analysis sheet will be unhidden and the others will be hidden.
If that is possible, your help would be appreciated.
I attached my code below:
Public Sub Worksheet_Change(ByVal Target As Range)
Dim S As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each S In ActiveWorkbook.Worksheets
If S.Name = [B6] Or S.Name = "Setup" Or S.Name = "CE Template" Or S.Name = "Instructions" Or S.Name = "Controls Analysis" Or S.Name = "Control Oligo Mix Analysis" Or S.Name = "Reagent Analysis" Or S.Name = "NTC Analysis" Or S.Name = "CE Hidden" Or S.Name = "List" Or S.Name = "Reagent Box Analysis" Then
S.Visible = True
Else
S.Visible = False
End If
Next S
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
I have a script I use that will uses a dropdown menu to select a reagent and the associated form with that reagent is unhidden and the forms for other reagents are hidden.
It works really well for that purpose, but I was wondering if there is a possibility to make this potential change:
In addition to each reagent having an associated form, there is also a sheet for analysis that follows this rule:
REDI-Mix A, REDI-Mix B, DirectPrime, and DirectWash all use the Reagent Analysis sheet
Control Oligo Mix uses control oligo mix analysis sheet
Controls uses Controls Analysis and NTC analysis sheet
Reagent Box uses Reagent Box Analysis sheet
What I'd like is to keep the original function of my script that shows/hides forms based on which reagent is being tested.
What I'd like to add is if the above reagents are selected, then their respective analysis sheet will be unhidden and the others will be hidden.
If that is possible, your help would be appreciated.
I attached my code below:
Public Sub Worksheet_Change(ByVal Target As Range)
Dim S As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each S In ActiveWorkbook.Worksheets
If S.Name = [B6] Or S.Name = "Setup" Or S.Name = "CE Template" Or S.Name = "Instructions" Or S.Name = "Controls Analysis" Or S.Name = "Control Oligo Mix Analysis" Or S.Name = "Reagent Analysis" Or S.Name = "NTC Analysis" Or S.Name = "CE Hidden" Or S.Name = "List" Or S.Name = "Reagent Box Analysis" Then
S.Visible = True
Else
S.Visible = False
End If
Next S
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub