Modification to my VBA code

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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Strawbunny,

I can help if you'll provide a few clarifications...

1. You listed 7 reagent labels and their associated analysis worksheets. Are there only those 7 items on the dropdown? If not, what worksheets should be visible if a reagent that is not one of those 7 is selected.

2. In addition to the associated analysis worksheets, your existing code makes visible sheets: Setup, CE Template, Instructions, CE Hidden and List. Are these to be visible regardless of which item is selected from the dropdown?

3. Does the existing code execute fast enough for your use, or is there some delay when a reagent is selected? The approach could be optimized to work faster, but if the current response time works well for you, then I'd suggest keeping the process as simple as possible instead of making it faster but more complex. One change in the code that I suggest will be that if the Target cell that triggered the Worksheet_Change event isn't $B$6, the execution should exit. Your existing code is resetting the visibility of the worksheets each time any cell is changed on the worksheet.

4. Lastly, if you expect changes such as adding new Reagents or Analysis forms, you might consider storing the mapping of reagents to associated worksheets in a worksheet table. That way you won't have to modify the VBA code with each change. This is especially valuable if someone else might need to maintain this process now or in the future. Please let me know if you would like to pursue that, since the code would be very different.
 
Upvote 0
The dropdown menu contains the following:

REDI-Mix A Subcomponents
REDI-Mix A
REDI-Mix B Subcomponents
REDI-Mix B
DirectPrime
DirectTaq
DirectWash
Reagent Box
Controls
Control Oligo Mix

There's a worksheet with that title associated with each reagent. Apart from the worksheet being visible, I want to keep CE Template and Setup unhidden always.

The code executes right away.

I want it to reset so that I can switch between reagents when I change the dropdown.
 
Upvote 0
Here is some code you can try. The associations of a few sheets weren't clear to me from your response. You might need to modify or add some sheet names but hopefully you can see how to adapt the code.

Code:
Public Sub Worksheet_Change(ByVal Target As Range)
 Dim bHide As Boolean
 Dim sErrMsg As String, sReagent As String
 Dim sSheetsToHide As String, sSheetsToShow As String
 Dim wks As Worksheet
 Dim vSheetName As Variant
 
 If Target.Address <> "$B$6" Then Exit Sub

 On Error GoTo ErrProc
 
 With Application
   .EnableCancelKey = xlErrorHandler
   .EnableEvents = False
   .ScreenUpdating = False
 End With
  
 sReagent = Target.Text
 
 '--step through all sheets. Add to hide/show sheet lists
 For Each wks In ThisWorkbook.Worksheets
   bHide = True
   Select Case wks.Name
      '--these sheets are always visible
      Case "CE Template", "Setup"
         bHide = False

      '--selected reagent to be visible
      Case sReagent
         bHide = False
      
      '--these sheets are dependent on reagent selected

      Case "Reagent Analysis"
         Select Case sReagent
            Case "REDI-Mix A", "REDI-Mix B", "DirectPrime", "DirectWash"
               bHide = False
         End Select
         
      Case "Control Oligo Mix Analysis"
         If sReagent = "Control Oligo Mix" Then bHide = False
         
      Case "Controls Analysis", "NTC Analysis"
         If sReagent = "Controls" Then bHide = False
      
      Case "Reagent Box Analysis"
         If sReagent = "Reagent Box " Then bHide = False
             
      Case Else
         '--hide all other sheets.
         bHide = True
   End Select
   
   '--add to delimited list of sheets to hide/show
   If bHide Then
      sSheetsToHide = sSheetsToHide & "/" & wks.Name
   Else
      sSheetsToShow = sSheetsToShow & "/" & wks.Name
   End If
 Next wks

 '--show and hide lists of sheets
 If Len(sSheetsToShow) Then
   For Each vSheetName In Split(Mid(sSheetsToShow, 2), "/")
      Sheets(CStr(vSheetName)).Visible = True
   Next vSheetName
 End If

 If Len(sSheetsToHide) Then
   For Each vSheetName In Split(Mid(sSheetsToHide, 2), "/")
      Sheets(CStr(vSheetName)).Visible = False
   Next vSheetName
 End If

ExitProc:
 On Error Resume Next
 With Application
   .EnableEvents = True
   .ScreenUpdating = True
 End With

 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub
 
Upvote 0
Revisiting this- there isn't any benefit in building lists of hidden/unhidden sheets.

I'd suggest this simpler version...

Code:
Public Sub Worksheet_Change(ByVal Target As Range)
 Dim bVisible As Boolean
 Dim sErrMsg As String, sReagent As String
 Dim wks As Worksheet
 
 If Target.Address <> "$B$6" Then Exit Sub

 On Error GoTo ErrProc
 
 With Application
   .EnableCancelKey = xlErrorHandler
   .EnableEvents = False
   .ScreenUpdating = False
 End With
  
 sReagent = Target.Text
 
 '--ensure at least one sheet is visible through process
 ThisWorkbook.Worksheets(sReagent).Visible = True
 
 '--step through all sheets. Add to hide/show sheet lists
 For Each wks In ThisWorkbook.Worksheets
   bVisible = False
   Select Case wks.Name
      '--these sheets are always visible
      Case "CE Template", "Setup"
         bVisible = True

      '--selected reagent to be visible
      Case sReagent
         bVisible = True
      
      '--these sheets are dependent on reagent selected

      Case "Reagent Analysis"
         Select Case sReagent
            Case "REDI-Mix A", "REDI-Mix B", "DirectPrime", "DirectWash"
               bVisible = True
         End Select
         
      Case "Control Oligo Mix Analysis"
         If sReagent = "Control Oligo Mix" Then bVisible = True
         
      Case "Controls Analysis", "NTC Analysis"
         If sReagent = "Controls" Then bVisible = True
      
      Case "Reagent Box Analysis"
         If sReagent = "Reagent Box " Then bVisible = True
             
      Case Else
         '--hide all other sheets.
         bVisible = False
   End Select
   
   wks.Visible = bVisible
 Next wks

ExitProc:
 On Error Resume Next
 With Application
   .EnableEvents = True
   .ScreenUpdating = True
 End With

 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub
 
Upvote 0
I got it to work! I had to add more cases, but I figured it out with ease. Ty so much!

Is it possible you could explain to me how the case function works and select case? I have been trying to learn VBA as I try to simplify some documents at work so that I will one day be able to figure how to write these codes with little to no help from others. :)
 
Upvote 0
Could you also explain to me the whole ExitProc and ErrProc? I don't understand what it does and the purpose?
 
Upvote 0
I got it to work! I had to add more cases, but I figured it out with ease. Ty so much!

Is it possible you could explain to me how the case function works and select case? I have been trying to learn VBA as I try to simplify some documents at work so that I will one day be able to figure how to write these codes with little to no help from others. :)

I'm glad that worked for you.

Here's two links to references that explain Select Case. The first is just a basic syntax, but it's very easy to follow. The second give some more detailed examples.

https://www.tutorialspoint.com/vba/vba_switch_statement.htm

VBA Select Case – Using VBA Select Case Statement in Excel | Excel & VBA – Databison


Just let me know if you have any questions after reviewing those.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top