Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Modification to my VBA code

  1. #1
    New Member Strawbunny's Avatar
    Join Date
    Feb 2017
    Location
    Los Angeles, CA
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Modification to my VBA code

    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

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Modification to my VBA code

    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.
    Using Excel 2016

  3. #3
    New Member Strawbunny's Avatar
    Join Date
    Feb 2017
    Location
    Los Angeles, CA
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modification to my VBA code

    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.

  4. #4
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Modification to my VBA code

    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
    Using Excel 2016

  5. #5
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Modification to my VBA code

    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
    Using Excel 2016

  6. #6
    New Member Strawbunny's Avatar
    Join Date
    Feb 2017
    Location
    Los Angeles, CA
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modification to my VBA code

    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.

  7. #7
    New Member Strawbunny's Avatar
    Join Date
    Feb 2017
    Location
    Los Angeles, CA
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modification to my VBA code

    Could you also explain to me the whole ExitProc and ErrProc? I don't understand what it does and the purpose?

  8. #8
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Modification to my VBA code

    Quote Originally Posted by Strawbunny View Post
    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/v..._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.
    Using Excel 2016

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •