Sheet Code

Posted by Aaron on December 28, 2001 8:44 AM

Is there any way to have your code automatically change if you change the name of your sheet. For instance, if I have Sheets("Sheet1").Select and I copy that sheet, is there any way that the copied sheet(Sheet2) code will read Sheets("Sheet2").Select? Please let me know if this is unclear. Thanks in advance for your help. -asa

Posted by Tom Urtis on December 28, 2001 8:59 AM

A few options

There are at least 3 ways to do this. One, you can refer to your sheets with the static sheet number name that you will see in the VBAProject pane in the VBE. Two, you can enter the formula
=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))) in any cell to have the current sheet tab name appear, and refer to that cell's value in your code. Or 3, you can use ActiveSheet.Name in your code.

Any help?

Tom Urtis

Posted by Aaron on December 28, 2001 11:54 AM

Re: A few options

Here is my Code. Any suggestions
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets.ActiveSheet.Name.ChartObjects(1).Chart.Axes(xlValue)
.MinimumScale = Worksheets.ActiveSheet.Name.Range("M11").Value
.MaximumScale = Worksheets.ActiveSheet.Name.Range("N11").Value
End With
End Sub

Posted by Aaron on December 28, 2001 11:56 AM

Sorry Any other suggestions

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets.ActiveSheet.Name.ChartObjects(1).Chart.Axes(xlValue)
.MinimumScale = Worksheets.ActiveSheet.Name.Range("M11").Value
.MaximumScale = Worksheets.ActiveSheet.Name.Range("N11").Value
End With
End Sub

Posted by Gary Bailey on December 28, 2001 3:16 PM

Re: Sorry Any other suggestions

If your writing code in the code modules behind a worksheet (as you seem to be below) you could use the Me keyword. That would refer to the worksheet itself.

eg Me.Range("a1").Value="Test"

Gary

Posted by Aaron on December 31, 2001 5:57 AM

Re: Sorry Any other suggestions

Here is my code once again, and what you are saying is to Change "Worksheets("ActiveSheet.Name")" to Me.Worksheet("WorksheetName")? I am very sorry that I am slow, but thanks for your patience.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("ActiveSheet.Name").ChartObjects(1).Chart.Axes(xlValue)
.MinimumScale = Worksheets("ActiveSheet.Name").Range("M11").Value
.MaximumScale = Worksheets("ActiveSheet.Name").Range("N11").Value
End With
End Sub