Drop-down Data Validation List Value (1 specific value in a range) >> Move User to Different Sheet Upon Selection

B

BuckyBoyRx

New Member
Joined
Mar 22, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,
Help, please. I have a form where a user selects from a list of values (referencing my master source list in a different sheet called 'master'). The drop-down is a data validation list. I was hoping to find vba code where a user, using this form selects a very specific value ('No, Research / No Template') from the list they, are automatically taken to a different sheet (called 'chemo+'). If the 'Chemo+' sheet could remain hidden in all other values ('Yes' & 'No, I must deviate from NCCN') that would be fantastic! I much appreciate any help you can give.

-BuckyBoyRx
 

Attachments

  • MrExcel help on beacon form.png
    MrExcel help on beacon form.png
    29.9 KB · Views: 19

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
What you want is that the worksheet checks which changes are being made to itself. If the change is that this cell's value becomes 'No, Research / No Template', then it unhides the 'Chemo+' sheet and activates it.
This can be achieved in the sheet's own code module.
To get to the code module, right click on the sheet name tab and select view code...

Now paste the following code in the opened window. Then read through the comments and adjust accordingly.
VBA Code: 
Option Explicit


Const sNRNT As String = "No, Research / No Template"    '<<<< Adjust text if necessary
Const sCHPL As String = "Chemo+"                        '<<<< Adjust name if necessary
Const sCellADDRESS As String = "E13"                    '<<<< Adjust address if necessary


Private Sub Worksheet_Activate()
    
    ' Check cell E13
    With Sheets(sCHPL)
        If Range(sCellADDRESS) Like sNRNT Then
            ' show chemo sheet and activate
            .Visible = xlSheetVisible
        Else
            ' hide chemo sheet
            .Visible = xlSheetHidden
        End If
    End If
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range(sCellADDRESS)) Is Nothing Then
    ' cell E13 has changed. So check change
        With Sheets(sCHPL)
            If Target Like sNRNT Then
                ' show chemo sheet and activate
                .Visible = xlSheetVisible
                .Activate
            Else
                ' hide chemo sheet
                .Visible = xlSheetHidden
            End If
        End If
    End If
    
    
End Sub

The code addresses two things:
If the cell is changed it will hide or show the Chemo+ page
If this sheet is activated it will check the value and hide or show the chemo page.

I have added this last touch because I am assuming the user can switch between the two (or more) pages.
 
Upvote 0
Elegant solution. Thank you.

I tried plugging into my workbook and adding the code to the 'Protocol Form' sheet and am getting a "compile error: Ambiguous name detected: Worksheet_Change."

1680287142952.png

I have a data validation list in E13 for the list. Not sure if that is contributing to the issue?

Thanks for the help.

BuckyBoyRx
 
Upvote 0
BuckyBoyRx said:
am getting a "compile error: Ambiguous name detected: Worksheet_Change."
Click to expand...
That is telling you that you have two separate procedures in the same module with the same name ("Worksheet_Change").
That is not allowed. You cannot repeat procedure names within a single module.
If you have two "Worksheet_Change" procedures, you will need to combine them into one.
 
Upvote 0
Joe4 said:
That is telling you that you have two separate procedures in the same module with the same name ("Worksheet_Change").
That is not allowed. You cannot repeat procedure names within a single module.
If you have two "Worksheet_Change" procedures, you will need to combine them into one.
Click to expand...
Thank you Joe4. That helped. I combined the code. The pop-up dialog box instructed me I was missing "End With" conditions/statements (apologies...I am a novice waiting for time to take my VBA course).

The code now looks like this:
Option Explicit
Const sNRNT As String = "No, Research / No Template" '<<<< Adjust text if necessary
Const sCHPL As String = "Chemo+" '<<<< Adjust name if necessary
Const sCellADDRESS As String = "E13" '<<<< Adjust address if necessary
Private Sub Worksheet_Activate()

' Check cell E13
With Sheets(sCHPL)
If Range(sCellADDRESS) Like sNRNT Then
' show chemo sheet and activate
.Visible = xlSheetVisible
Else
' hide chemo sheet
.Visible = xlSheetHidden
End If
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("E3"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Outpatient (OP)": Rows("17:18").EntireRow.Hidden = False
Rows("19:20").EntireRow.Hidden = True
Case Is = "Inpatient (IP)": Rows("17:18").EntireRow.Hidden = True
Rows("19:20").EntireRow.Hidden = False
Case Is = "Research (RSH)": Rows("17:18").EntireRow.Hidden = True
Rows("19:20").EntireRow.Hidden = False
Case Is = "Outpatient / Inpatient(OP / IP)": Rows("17:18").EntireRow.Hidden = False
Rows("19:20").EntireRow.Hidden = False
Case Is = "Other": Rows("17:18").EntireRow.Hidden = False
Rows("19:20").EntireRow.Hidden = False
End Select
End If
If Not Intersect(Target, Range(sCellADDRESS)) Is Nothing Then
' cell E13 has changed. So check change
With Sheets(sCHPL)
If Target Like sNRNT Then
' show chemo sheet and activate
.Visible = xlSheetVisible
.Activate
Else
' hide chemo sheet
.Visible = xlSheetHidden
End If
End With
End If
End Sub


The hidden 'Chemo+' sheet will appear with the correct value in E13 selected, but it no longer goes directly to the sheet. In fact, I can't even select the sheet to stay on it...I'm returned to the 'Protocol Form' sheet (sheet 1). I am lost as to what I did wrong when combining the sheet code (I'm also hiding rows based on what the user selects in E3.

Thank you,

BuckBoyRx
 
Upvote 0
Just going through your code adjustment a few comments
you have:
VBA Code: 
    If Not Application.Intersect(Range("E3"), Range(Target.Address)) Is Nothing Then
Target is a range, so Range(Target.Address) is identical to Target, but less efficient, as the compiler first needs to get the address of Target and then make a range of the address. A range which it already had.
So write it as
VBA Code: 
    If Not Intersect(Range("E3"), Target) Is Nothing Then
(you can keep 'Application.' in front of 'Intersect' but it is not necessary. Whichever you prefer)


You write:
VBA Code: 
    ActiveSheet.Activate
That is a totally meaningless statement. "Display the sheet that is being displayed"
But it does take processor time. So leave it out


You write:
VBA Code: 
            Case Is = "Outpatient (OP)": Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = True
Nothing wrong with that, but difficult to read. The ':' is a separator between two bits of code. So for clarity, might as well write it as:
VBA Code: 
            Case Is = "Outpatient (OP)"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = True

The cleaned up code is as follows. Replace everything in the sheet codemodule with this code:

VBA Code: 
Option Explicit


Const sNRNT As String = "No, Research / No Template"    '<<<< Adjust text if necessary
Const sCHPL As String = "Chemo+"                        '<<<< Adjust name if necessary
Const sCellADDRESS As String = "E13"                    '<<<< Adjust address if necessary




Private Sub Worksheet_Activate()

    ' Check cell E13
    With Sheets(sCHPL)
        If Range(sCellADDRESS) Like sNRNT Then
            ' show chemo sheet and activate
            .Visible = xlSheetVisible
        Else
            ' hide chemo sheet
            .Visible = xlSheetHidden
        End If
    End With
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("E3"), Target) Is Nothing Then
        Select Case Target.Value
            Case Is = "Outpatient (OP)"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = True
            Case Is = "Inpatient (IP)"
                Rows("17:18").EntireRow.Hidden = True
                Rows("19:20").EntireRow.Hidden = False
            Case Is = "Research (RSH)"
                Rows("17:18").EntireRow.Hidden = True
                Rows("19:20").EntireRow.Hidden = False
            Case Is = "Outpatient / Inpatient(OP / IP)"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = False
            Case Is = "Other"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = False
        End Select
    End If
    If Not Intersect(Target, Range(sCellADDRESS)) Is Nothing Then
        ' cell E13 has changed. So check change
        With Sheets(sCHPL)
            If Target Like sNRNT Then
                ' show chemo sheet and activate
                .Visible = xlSheetVisible
                .Activate
            Else
                ' hide chemo sheet
                .Visible = xlSheetHidden
            End If
        End With
    End If
End Sub


Oh, yes. One last thing: When you post code, do it between code brackets to get nice formatting as above. In your post window, click on the small VBA icon and paste your code.
 
Upvote 0
Just a couple of minor tweaks that I would make...

You do need to use Target in the Intersect check, but really shouldn't after that in this case when comparing values, because Target can be a range of more than one cell. If that is the case, the code will throw an error when you try to get the value from it. To test this, choose a bunch of cells including either of the cells we are checking and click the delete button...

So I'd replace Target.Value here:
VBA Code: 
    If Not Intersect(Range("E3"), Target) Is Nothing Then
        Select Case Range("E3")           ' instead of:   Select Case Target.Value

...and then down further, replace the use of Target here as well:
VBA Code: 
    If Not Intersect(Target, Range(sCellADDRESS)) Is Nothing Then
        ' cell E13 has changed. So check change
        With Sheets(sCHPL)
            If Range(sCellADDRESS) Like sNRNT Then       'instead of:     If Target Like sNRNT Then


And actually, when I look at the row hiding code in the Select Case, it seems like the logic is far more trivial than needing all of those cases. Looks like there are limited times hidden would be true for either pair of rows, so I'd personally simplify it. Actually, I'd simplify several things, as follows... (I'll just put the full code):

VBA Code: 
Option Explicit

Const sNRNT As String = "No, Research / No Template"    '<<<< Adjust text if necessary
Const sCHPL As String = "Chemo+"                        '<<<< Adjust name if necessary
Const sCellADDRESS As String = "E13"                    '<<<< Adjust address if necessary

Private Sub Worksheet_Activate()
    SetChemoSheetVisibility False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim checkRange As Range
    
    Set checkRange = Range("E3")
    If Not Intersect(checkRange, Target) Is Nothing Then
        Rows("17:18").EntireRow.Hidden = checkRange = "Inpatient (IP)" _
                                      Or checkRange = "Research (RSH)"
        Rows("19:20").EntireRow.Hidden = checkRange = "Outpatient (OP)"
    End If
    
    If Not Intersect(Target, Range(sCellADDRESS)) Is Nothing Then
        SetChemoSheetVisibility True
    End If
End Sub

Private Sub SetChemoSheetVisibility(activateOnVisible As Boolean)
    With Sheets(sCHPL)
        .Visible = Range(sCellADDRESS) Like sNRNT
        If activateOnVisible And .Visible Then .Activate
    End With
End Sub

...just my 2 cents
 
Upvote 1
Solution
sijpie said:
Just going through your code adjustment a few comments
you have:
VBA Code: 
    If Not Application.Intersect(Range("E3"), Range(Target.Address)) Is Nothing Then
Target is a range, so Range(Target.Address) is identical to Target, but less efficient, as the compiler first needs to get the address of Target and then make a range of the address. A range which it already had.
So write it as
VBA Code: 
    If Not Intersect(Range("E3"), Target) Is Nothing Then
(you can keep 'Application.' in front of 'Intersect' but it is not necessary. Whichever you prefer)


You write:
VBA Code: 
    ActiveSheet.Activate
That is a totally meaningless statement. "Display the sheet that is being displayed"
But it does take processor time. So leave it out


You write:
VBA Code: 
            Case Is = "Outpatient (OP)": Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = True
Nothing wrong with that, but difficult to read. The ':' is a separator between two bits of code. So for clarity, might as well write it as:
VBA Code: 
            Case Is = "Outpatient (OP)"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = True

The cleaned up code is as follows. Replace everything in the sheet codemodule with this code:

VBA Code: 
Option Explicit


Const sNRNT As String = "No, Research / No Template"    '<<<< Adjust text if necessary
Const sCHPL As String = "Chemo+"                        '<<<< Adjust name if necessary
Const sCellADDRESS As String = "E13"                    '<<<< Adjust address if necessary




Private Sub Worksheet_Activate()

    ' Check cell E13
    With Sheets(sCHPL)
        If Range(sCellADDRESS) Like sNRNT Then
            ' show chemo sheet and activate
            .Visible = xlSheetVisible
        Else
            ' hide chemo sheet
            .Visible = xlSheetHidden
        End If
    End With
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("E3"), Target) Is Nothing Then
        Select Case Target.Value
            Case Is = "Outpatient (OP)"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = True
            Case Is = "Inpatient (IP)"
                Rows("17:18").EntireRow.Hidden = True
                Rows("19:20").EntireRow.Hidden = False
            Case Is = "Research (RSH)"
                Rows("17:18").EntireRow.Hidden = True
                Rows("19:20").EntireRow.Hidden = False
            Case Is = "Outpatient / Inpatient(OP / IP)"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = False
            Case Is = "Other"
                Rows("17:18").EntireRow.Hidden = False
                Rows("19:20").EntireRow.Hidden = False
        End Select
    End If
    If Not Intersect(Target, Range(sCellADDRESS)) Is Nothing Then
        ' cell E13 has changed. So check change
        With Sheets(sCHPL)
            If Target Like sNRNT Then
                ' show chemo sheet and activate
                .Visible = xlSheetVisible
                .Activate
            Else
                ' hide chemo sheet
                .Visible = xlSheetHidden
            End If
        End With
    End If
End Sub


Oh, yes. One last thing: When you post code, do it between code brackets to get nice formatting as above. In your post window, click on the small VBA icon and paste your code.
Click to expand...
Wow, thank you for taking the time to walk me through this! Being new to VBA and posting on an expert forum can be intimidating. Your guidance is much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Mirror selections from multi-select drop down list in another cell
Replies
4
Views
290
Eccymarge
E
S
  • Question Question
Excel Drop Down Multiple Select/Remove & Auto Alphabetize Selections
Replies
0
Views
241
Sactown Steve
S
I
  • Solved
If value isnt in userform drop down list then allow user to add it
2 3
Replies
20
Views
855
ipbr21054
I
S
  • Question Question
Copy and Paste from Different Sheet base upon Drop Down Criteria
Replies
3
Views
223
Joe4
Joe4
O
  • Solved
Copy a sheet which is a template based on a drop down list and rename each copied tab with each value in the drop down list
2 3
Replies
21
Views
1K
dmt32
D

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

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