Hi all, I have macro below in Worksheet Module. It works fine but
when ever I open any other excel file at the same time when I got
current file (in which I got the below macro) opened, I get error
message pop up saying "Run-time error '9': Subscript out of Range"
Please can any friend can help that how can i get rid of this error
message.

Private Sub Worksheet_Calculate()
Dim SHT As Worksheet
Set SHT = Sheets("Template")
If Range("C6").Value = "GD" Then
SHT.Shapes("BACKTEMP").Width = 398
SHT.Shapes("NUMBERS").Visible = True
SHT.Shapes("ACCRUE").Visible = True
Me.TextBox3.Visible = True
Me.ComboBox2.ListFillRange = "S33:S43"
Me.ComboBox2.ListRows = 11
Else
SHT.Shapes("NUMBERS").Visible = False
SHT.Shapes("ACCRUE").Visible = False
Me.TextBox3.Visible = False
SHT.Shapes("BACKTEMP").Width = 302
Me.ComboBox2.ListFillRange = "S33:S45"
Me.ComboBox2.ListRows = 13
End If
End Sub

Specify what workbook contains the Template sheet:

Set SHT = me.parent.Sheets("Template")

Me is the worksheet that owns the code.
Me.Parent is the workbook that owns the worksheet that owns the code.



K wrote:

Hi all, I have macro below in Worksheet Module. It works fine but
when ever I open any other excel file at the same time when I got
current file (in which I got the below macro) opened, I get error
message pop up saying "Run-time error '9': Subscript out of Range"
Please can any friend can help that how can i get rid of this error
message.

Private Sub Worksheet_Calculate()
Dim SHT As Worksheet
Set SHT = Sheets("Template")
If Range("C6").Value = "GD" Then
SHT.Shapes("BACKTEMP").Width = 398
SHT.Shapes("NUMBERS").Visible = True
SHT.Shapes("ACCRUE").Visible = True
Me.TextBox3.Visible = True
Me.ComboBox2.ListFillRange = "S33:S43"
Me.ComboBox2.ListRows = 11
Else
SHT.Shapes("NUMBERS").Visible = False
SHT.Shapes("ACCRUE").Visible = False
Me.TextBox3.Visible = False
SHT.Shapes("BACKTEMP").Width = 302
Me.ComboBox2.ListFillRange = "S33:S45"
Me.ComboBox2.ListRows = 13
End If
End Sub

--

Dave Peterson

On May 21, 3:31*pm, Dave Peterson wrote:
Specify what workbook contains the Template sheet:

Set SHT = me.parent.Sheets("Template")

Me is the worksheet that owns the code.
Me.Parent is the workbook that owns the worksheet that owns the code.





K wrote:

Hi all, *I have macro below in Worksheet Module. *It works fine but
when ever I open any other excel file at the same time when I got
current file (in which I got the below macro) opened, I get error
message pop up saying "Run-time error '9': Subscript out of Range"
Please can any friend can help that how can i get rid of this error
message.

Private Sub Worksheet_Calculate()
Dim SHT As Worksheet
Set SHT = Sheets("Template")
If Range("C6").Value = "GD" Then
SHT.Shapes("BACKTEMP").Width = 398
SHT.Shapes("NUMBERS").Visible = True
SHT.Shapes("ACCRUE").Visible = True
Me.TextBox3.Visible = True
Me.ComboBox2.ListFillRange = "S33:S43"
Me.ComboBox2.ListRows = 11
Else
SHT.Shapes("NUMBERS").Visible = False
SHT.Shapes("ACCRUE").Visible = False
Me.TextBox3.Visible = False
SHT.Shapes("BACKTEMP").Width = 302
Me.ComboBox2.ListFillRange = "S33:S45"
Me.ComboBox2.ListRows = 13
End If
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks lot Dave