Event code - trigger from another worksheet

[Sheripres]

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

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
   Select Case LCase(Target.Value)
      Case "[COLOR=#ff0000]on site[/COLOR]", "[COLOR=#ff0000]complete[/COLOR]", "[COLOR=#ff0000]pending[/COLOR]"
         Sheets("Step2").Range("C3").Value = "Yes"
      Case Else
         Sheets("Step2").Range("C3").Value = "No"
   End Select
End Sub

Change the values in red to match the criteria for "Yes" and leave the step2 code intact.

 

[Sheripres]

It works in a way. I notice I cannot upload a file, right? I created a small file to show you what is happening, but I don't know how to do that. I did screen shots, but I don't have a URL to upload them too. I am at work, so they might block me if I try to upload to a website (which I have never done before).

Your second code is totally different from your first one. It didn't like something. I am going to keep playing with it. If anything, I will figure out something else.

You really have been super helpful to me! I am keeping these codes for future reference!

Thank you!

Sheri

 

[Fluff]

It works in a way. I notice I cannot upload a file, right?

Correct, you cannot upload files to this site, but you can upload to OneDrive, or dropbox, mark it for sharing & then post the link to the thread.

 

[Sheripres]

Okay - I will have to do this off hours. I am blocked at work.

Thanks!

 

[Sheripres]

I figured out the issue and want to share it here.

Instead of using Worksheet_Change event code, I used Worksheet_Activate and it worked!

 

[Fluff]

Glad you got it sorted & thanks for the feedback

 

[Sheripres]

Thank you!

I try and make sure if I get the answer, go back to the forum and post it.

When I Google a question and I see my same exact question on a thread, I get disappointed when the person says they figured it out and never put the answer in their thread.

It is like telling a joke and not the punchline.