Event code - trigger from another worksheet

Sheripres

Board Regular
Joined
Sep 7, 2011
Messages
91
Hello:

I have two sheet tabs: Step 1 and Step 2.

Step 2 has a drop down where you can either choose Yes or No in various cells. Depending on the answer, rows either hide or unhide. No problem.

But, now I am being asked to go up a notch:

in Step 1, I have cell G8 that has a drop down menu and when something is chosen, the cell in the Step 2 sheet tab automatically puts in YES (instead of having the drop down list).

The problem is, I cannot seem to trigger the event code for Step 2 to unhide the rows. It will only unhide them if I am clicking on the cell.

I know it has to do with "active sheet", but I don't know how to have an event code run when the worksheet is inactive.

The event code in Step two is Worksheet_Change.

Thank you for your time and help.

Sheri
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Could you please supply both codes that you currently have?
 
Upvote 0
I only have this code (it is longer than this, but it just repeats the same scenario).

This is on Step 2. Example: If cell G8 in Step 1 sheet tab equal "On Site", then there is an IF statement that will trigger cell C3 on Step 2 sheet tab to be YES, but, it won't open up row 4.

Thank you for the quick response!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Range("c3").Value
    Case Is = "Yes": Rows("4").EntireRow.Hidden = False
    Case Is = "No": Rows("4").EntireRow.Hidden = True
    Case Else:
End Select
    
Select Case Range("c5").Value
    Case Is = "Yes": Rows("6:7").EntireRow.Hidden = False
    Case Is = "No": Rows("6:7").EntireRow.Hidden = True
     Case Else:
End Select




End Sub
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Select Case Range("c3").Value
Case Is = "Yes": Rows("4").EntireRow.Hidden = False
Case Is = "No": Rows("4").EntireRow.Hidden = True
Case Else:
End Select


</body>
 
Upvote 0
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
   Select Case Target
      Case Is = Range("G8")
         If LCase(Target.Value) = "on site" Then
            Sheets("Step2").Rows(4).Hidden = False
         Else
            Sheets("Step2").Rows(4).Hidden = True
         End If
   End Select
End Sub
This goes in the Step1 module & delete the code you have in the Step2 module
 
Upvote 0
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
   Select Case Target
      Case Is = Range("G8")
[B][COLOR="#FF0000"]         If LCase(Target.Value) = "on site" Then
            Sheets("Step2").Rows(4).Hidden = False
         Else
            Sheets("Step2").Rows(4).Hidden = True
         End If
[/COLOR][/B]   End Select
End Sub
This goes in the Step1 module & delete the code you have in the Step2 module
An alternate to what is highlighted in red above...
Code:
Sheets("Step2").Rows(4).Hidden = (LCase(Target.Value) = "on site")
 
Upvote 0
That would work, but unfortunately, another part of the puzzle is that if they do not choose "On Site", the end user can still choose Yes or No on Step 2.

This is not my creation; it is a boss' request. So, if there is not a way to do this, I understand. I have been searching the internet since 10 am this morning and I can't find anything that will allow the Step 2 event code to activate from the Step 1 sheet tab.

I will ask him about your option.

Thank you so much!
 
Upvote 0
You can keep both options just change the Step1 as follows
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
[COLOR=#ff0000]Application.EnableEvents = False[/COLOR]
   Select Case Target
      Case Is = Range("G8")
         Sheets("Step2").Rows(4).Hidden = (LCase(Target.Value) = "on site")
   End Select
[COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
End Sub
I've also modified the code to incorporate Rick's much neater solution
 
Upvote 0
I went through the internet and found you CANNOT have the Worksheet_Change event code work properly if there is an IF statement or other formula involved, so, your would be perfect for me to use with one more question.

If it is NO, can row 4 be hidden?

If we can do that, I think adding this to Step 1 will work perfectly.

Thank you so very much!
 
Upvote 0
Do you mean that if G8 is "No" rather than "on site" hide row 4, or if G8 is either "on site" or "no" then hide row 4
 
Upvote 0
Well, if G8 is NOT "on Site", then I guess hide row 4.

I am taking your formula and manipulating it a bit.

Here is the total story:

Step 1 has a drop down that allows you to choose SERVICE TYPE (cell G8). You get to choose thru many options from the drop down.

In Step 2, there are nine questions that are YES or NO with opening and closing of cells depending on your answer. If the SERVICE TYPE matches any of the nine questions (via the IF statement), then the RESPONSE column automatically types in YES and opens certain rows that ask more questions. If it does not match, it is NO and those rows will collapse or remain collapsed.

So, it is tricky because there are situations where you can go to Step 2 and choose YES or NO and the original event code takes care of it.

Bottom line is that everything was good with YES / NO on the drop down, but the boss wants to have it auto-fill with YES or NO depending on what they choose in cell G8 in Step 1.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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