original question:
i would to create VBA code that will bring up a dialogue box if the
words "TOOL ASSEMBLY" are input under column "F". I would like to
place certain words A, B, C, D whatever in a corresponding column on
the same row. Is this possible? any help is greatly appreciated,
thanks.

answer:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col F
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 Then
n = Target.Row
If Excel.Range("F" & n).Value = "TOOL ASSEMBLY" Then
Excel.Range("G" & n).Value = "A, B, C, D or whatever"
End If
End If
enditall:
Application.EnableEvents = True
End Sub

this works great thanks. just one thing, is it possible to look for a
column heading such as "Name" instead of column "F", thanks again for
the help.

It happens that john taiariol formulated :
original question:
i would to create VBA code that will bring up a dialogue box if the
words "TOOL ASSEMBLY" are input under column "F". I would like to
place certain words A, B, C, D whatever in a corresponding column on
the same row. Is this possible? any help is greatly appreciated,
thanks.

answer:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col F
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 Then
n = Target.Row
If Excel.Range("F" & n).Value = "TOOL ASSEMBLY" Then
Excel.Range("G" & n).Value = "A, B, C, D or whatever"
End If
End If
enditall:
Application.EnableEvents = True
End Sub

this works great thanks. just one thing, is it possible to look for a
column heading such as "Name" instead of column "F", thanks again for
the help.

You can define Names for your columns. This would usually be something
that relates to the headers for each column.

Example:

$A:$A:
Header: "Field1"
Defined Name: "Sheet1!Field1"
..where Sheet1 is the name of the worksheet
RefersTo: ='Sheet1'!$A:$A

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS used his keyboard to write :
It happens that john taiariol formulated :
original question:
i would to create VBA code that will bring up a dialogue box if the
words "TOOL ASSEMBLY" are input under column "F". I would like to
place certain words A, B, C, D whatever in a corresponding column on
the same row. Is this possible? any help is greatly appreciated,
thanks.

answer:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col F
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 Then
n = Target.Row
If Excel.Range("F" & n).Value = "TOOL ASSEMBLY" Then
Excel.Range("G" & n).Value = "A, B, C, D or whatever"
End If
End If
enditall:
Application.EnableEvents = True
End Sub

this works great thanks. just one thing, is it possible to look for a
column heading such as "Name" instead of column "F", thanks again for
the help.

You can define Names for your columns. This would usually be something that
relates to the headers for each column.

Example:

$A:$A:
Header: "Field1"
Defined Name: "Sheet1!Field1"
..where Sheet1 is the name of the worksheet
RefersTo: ='Sheet1'!$A:$A

This will allow you to refer to this column in code as...

ActiveSheet.Range("Field1")
OR
Cells(ActiveCell.Row, Range("Field1").Column)
OR
=SUMIF(Field1,"TOOL ASSEMBLY",[SumRange])

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Apr 7, 6:50*pm, GS wrote:
GS used his keyboard to write :





It happens that john taiariol formulated :
original question:
i would to create VBA code that will bring up a dialogue box if the
words "TOOL ASSEMBLY" are input under column "F". I would like to
place certain words A, B, C, D whatever in a corresponding column on
the same row. Is this possible? any help is greatly appreciated,
thanks.

answer:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col F
On Error GoTo enditall
* * Application.EnableEvents = False
If Target.Cells.Column = 6 Then
* * * * n = Target.Row
* * * * If Excel.Range("F" & n).Value = "TOOL ASSEMBLY" Then
* * * * * * Excel.Range("G" & n).Value = "A, B, C, D or whatever"
* * * * End If
* * End If
enditall:
* * Application.EnableEvents = True
End Sub

this works great thanks. just one thing, is it possible to look for a
column heading such as "Name" instead of column "F", thanks again for
the help.

You can define Names for your columns. This would usually be something that
relates to the headers for each column.

Example:

* $A:$A:
* * Header: *"Field1"
* * Defined Name: *"Sheet1!Field1"
* * ..where Sheet1 is the name of the worksheet
* * RefersTo: *='Sheet1'!$A:$A

This will allow you to refer to this column in code as...

* ActiveSheet.Range("Field1")
OR
* Cells(ActiveCell.Row, Range("Field1").Column)
OR
* =SUMIF(Field1,"TOOL ASSEMBLY",[SumRange])

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

so if I have column heading called "Name" how would you write the code?

john taiariol has brought this to us :
so if I have column heading called "Name" how would you write the code?

What code are you refering to? If you mean how would I use "Name" in
code it would be same as I demonstrated with the column heading
"Field1". Though, I'd be a bit more descriptive using "Name". For
example, if it was the heading for a list of items where each item had
a name I'd use "ItemName" for the range name.

If you mean how do you name a range, use the namebox on the left side
of the Formula Bar. Make sure, though, that you give it local (sheet
level) scope. Here's how:

Select the column to be named.
Click in the namebox and type '<Sheet name'!ItemName
..where <Sheet name is the name of the active worksheet.
Press Enter.

Now, whenever your code refs Range("ItemName") it will refer to that
column. If you ref this column in formulas on another worksheet then
include the sheetname.

Example: =SUMIF(Sheet1!ItemName,"TOOL ASSEMBLY", [SumRange])

If you refer to it in code then specify the worksheet as follows:

Sheets("Sheet1").Range("ItemName")

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Apr 8, 1:07*am, GS wrote:
john taiariol has brought this to us :

so if I have column heading called "Name" how would you write the code?

What code are you refering to? If you mean how would I use "Name" in
code it would be same as I demonstrated with the column heading
"Field1". Though, I'd be a bit more descriptive using "Name". For
example, if it was the heading for a list of items where each item had
a name I'd use "ItemName" for the range name.

If you mean how do you name a range, use the namebox on the left side
of the Formula Bar. Make sure, though, that you give it local (sheet
level) scope. Here's how:

* Select the column to be named.
* Click in the namebox and type *'<Sheet name'!ItemName
* ..where <Sheet name is the name of the active worksheet.
* Press Enter.

Now, whenever your code refs Range("ItemName") it will refer to that
column. If you ref this column in formulas on another worksheet then
include the sheetname.

* Example: *=SUMIF(Sheet1!ItemName,"TOOL ASSEMBLY", [SumRange])

If you refer to it in code then specify the worksheet as follows:

* Sheets("Sheet1").Range("ItemName")

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I tried to put it in here and it didnt' work??

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col F
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 Then
n = Target.Row
If Excel.Range("F" & n).Value = "TOOL ASSEMBLY" Then
Excel.Range("G" & n).Value = "A, B, C, D or whatever"
End If
End If
enditall:
Application.EnableEvents = True
End Sub