I need help writing code that will allow me to enter a name into a list on
Sheet1 that will triger a macro on Sheet2 that will use the name entered on
Sheet1.

The name is for a supplier and the macro would generate a new sheet
providing the supplier's information and carying the suppliers name on the
page tab.

I am using XP Pro and Excel 2003.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then Worksheets.Add.Name = .Value
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
I need help writing code that will allow me to enter a name into a list on
Sheet1 that will triger a macro on Sheet2 that will use the name entered
on
Sheet1.

The name is for a supplier and the macro would generate a new sheet
providing the supplier's information and carying the suppliers name on the
page tab.

I am using XP Pro and Excel 2003.

Thank you very much. That worked just fine. I do not understand how it works
but am happy it does.

I would like to know how to adjust the code so it only works when a name is
entered into the list. As is, it adds a sheet regarless of what was done in
the list. I do not want a page added if I delete a name from the list. In
fact I would like for it to remove the supplier sheet when the name is
deleted from the list.

And can the code be changed to enter the name used on the tab into A1 on the
new page?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then Worksheets.Add.Name = .Value
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
I need help writing code that will allow me to enter a name into a list on
Sheet1 that will triger a macro on Sheet2 that will use the name entered
on
Sheet1.

The name is for a supplier and the macro would generate a new sheet
providing the supplier's information and carying the suppliers name on the
page tab.

I am using XP Pro and Excel 2003.

Deleting is tricky, there is no delete sheet event.

To put the sheet name in A1, use

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then
Worksheets.Add.Name = .Value
Activesheet.Range("A1").Value = .Value
End If
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
Thank you very much. That worked just fine. I do not understand how it
works
but am happy it does.

I would like to know how to adjust the code so it only works when a name
is
entered into the list. As is, it adds a sheet regarless of what was done
in
the list. I do not want a page added if I delete a name from the list. In
fact I would like for it to remove the supplier sheet when the name is
deleted from the list.

And can the code be changed to enter the name used on the tab into A1 on
the
new page?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then Worksheets.Add.Name = .Value
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
I need help writing code that will allow me to enter a name into a
list on
Sheet1 that will triger a macro on Sheet2 that will use the name
entered
on
Sheet1.

The name is for a supplier and the macro would generate a new sheet
providing the supplier's information and carying the suppliers name on
the
page tab.

I am using XP Pro and Excel 2003.

Thank you again! That works just fine. From this answer I assume that I can
continue including the remainder of the sheet format code under the change
you just offered but before the "End If " statement. Is my assumption
correct?

"Bob Phillips" wrote:

Deleting is tricky, there is no delete sheet event.

To put the sheet name in A1, use

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then
Worksheets.Add.Name = .Value
Activesheet.Range("A1").Value = .Value
End If
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
Thank you very much. That worked just fine. I do not understand how it
works
but am happy it does.

I would like to know how to adjust the code so it only works when a name
is
entered into the list. As is, it adds a sheet regarless of what was done
in
the list. I do not want a page added if I delete a name from the list. In
fact I would like for it to remove the supplier sheet when the name is
deleted from the list.

And can the code be changed to enter the name used on the tab into A1 on
the
new page?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then Worksheets.Add.Name = .Value
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
I need help writing code that will allow me to enter a name into a
list on
Sheet1 that will triger a macro on Sheet2 that will use the name
entered
on
Sheet1.

The name is for a supplier and the macro would generate a new sheet
providing the supplier's information and carying the suppliers name on
the
page tab.

I am using XP Pro and Excel 2003.

Hi Ralph,

I would add it after the On Error Goto 0 so that any errors get caught.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
Thank you again! That works just fine. From this answer I assume that I
can
continue including the remainder of the sheet format code under the change
you just offered but before the "End If " statement. Is my assumption
correct?

"Bob Phillips" wrote:

Deleting is tricky, there is no delete sheet event.

To put the sheet name in A1, use

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then
Worksheets.Add.Name = .Value
Activesheet.Range("A1").Value = .Value
End If
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
Thank you very much. That worked just fine. I do not understand how it
works
but am happy it does.

I would like to know how to adjust the code so it only works when a
name
is
entered into the list. As is, it adds a sheet regarless of what was
done
in
the list. I do not want a page added if I delete a name from the list.
In
fact I would like for it to remove the supplier sheet when the name is
deleted from the list.

And can the code be changed to enter the name used on the tab into A1
on
the
new page?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then Worksheets.Add.Name = .Value
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
I need help writing code that will allow me to enter a name into a
list on
Sheet1 that will triger a macro on Sheet2 that will use the name
entered
on
Sheet1.

The name is for a supplier and the macro would generate a new
sheet
providing the supplier's information and carying the suppliers
name on
the
page tab.

I am using XP Pro and Excel 2003.