How to reference a separate worksheet in VBA

[mgchurch77]

I have a macro for one worksheet that is set to run based on a specific cell input from the user. The target cell is on a separate sheet from that of the macro. When I run the macro on the target sheet it works, however when I try to run it using a sheet reference it fails.

I am guessing that i have to somehow define the sheet name before I can reference it in code. Here it is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Report Data").Range("H39").Value = 0 Then
Columns("C").EntireColumn.Hidden = True
Else
Columns("C").EntireColumn.Hidden = False
End If
End Sub

 

[BarCodeWiz]

What you have should work. Perhaps you misspelled the sheet name?

How does it fail? What's the error message?

 

[mgchurch77]

Nope seems to be right. I just changed the name to "a" to make sure that I didn't have issues with spaces or caps. Failed. I have only named the worksheet via the "rename" function that is accessed via clicking on a worksheet tab. Do I need to do any renaming via VBA editor?

 

[mgchurch77]

Got it working...kind of. The sheet with the code had to be activated to actually fire the macro. Now I have two other issues that I'll post in a new thread.

 

[BarCodeWiz]

My guess is there isn't an error message. The event simply does not fire. Am I correct? This macro should be in the code section of the sheet that's changing.

 

[mgchurch77]

Right was not firing. The code is on the sheet where the action is to be done. Found the problem. After I changed the target cell to something other than 0 the macro did not fire when I opened the sheet with the code and expected column hide action. However it did fire when I clicked into the sheet. Anyway to activate that sheet? Would like the column to hide before the user is taken to the sheet.

 

[BarCodeWiz]

You can activet a sheet like this:

Sheets("report data").Activate

 

[mgchurch77]

The sheet that I want activated is differen than the sheet where the target cell is located. I know I would substitute the name of that sheet where u have "report data" but where exactly in the code would it go?