VBA code based on column heading
 

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.

VBA code based on column heading
 

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

VBA code based on column heading
 

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

VBA code based on column heading
 

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?

VBA code based on column heading
 

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

VBA code based on column heading
 

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

VBA code based on column heading
 

john taiariol explained on 4/8/2011 :
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

You tried to put what in here? I don't see anything I was talking about
in this code sample!

Since the cell you're checking IS 'Target' then why are you refering to
it as Range("F" & n)? I revised your code as follows and tested it.
Works fine for me!

<aircode
'Column 'F' is named "ItemName"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("ItemName")) Is Nothing Then
If Target.Value = "TOOL ASSEMBLY" Then _
Target.Offset(, 1) = "A, B, C, D or whatever"
End If
End Sub
</aircode

--
Garry

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

VBA code based on column heading
 

In case user doesn't use caps...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("ItemName")) Is Nothing Then
If UCase$(Target.Value) = "TOOL ASSEMBLY" Then _
Target.Offset(, 1) = "A, B, C, D or whatever"
End If
End Sub

--
Garry

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

VBA code based on column heading
 

Sorry.., wrong event! (I didn't notice that SelectionChange was the
default for Worksheet objects)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ItemName")) Is Nothing Then
If UCase$(Target.Value) = "TOOL ASSEMBLY" Then _
Target.Offset(, 1) = "A, B, C, D or whatever"
End If
End Sub

--
Garry

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

VBA code based on column heading
 

John

This code assumes top cell in a column has heading value of "Name" and operates
on cells in that column that contain the words TOOL ASSEMBLY

Any other column will be ignored.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Cells(1, ActiveCell.Column).Value = "Name" Then
n = Target.Row
If Cells(n, ActiveCell.Column).Value = "TOOL ASSEMBLY" Then
ActiveCell.Offset(-1, 1).Value = "A, B, C, D or whatever"
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP


On Fri, 8 Apr 2011 10:55:47 -0700 (PDT), john taiariol
wrote:

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

VBA code based on column heading
 

Hi Gord,

Not sure why you offset to the next column, previous row when the OP
wants the value entered in the same row, next column.

I also don't understand why events are being disabled since the
executable code is wrapped in an IF construct.

In retrospect, I offer this revised approach for consideration:

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(1, Target.Column) = "Name" Then
If UCase$(Target.Value) = "TOOL ASSEMBLY" Then _
Target.Offset(, 1) = "A, B, C, D or whatever"
End If
End Sub

--
Garry

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

VBA code based on column heading
 

Again.., to obviate case sensitivity:

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase$(Cells(1, Target.Column)) = "NAME" Then
If UCase$(Target.Value) = "TOOL ASSEMBLY" Then _
Target.Offset(, 1) = "A, B, C, D or whatever"
End If
End Sub

--
Garry

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

VBA code based on column heading
 

On Apr 8, 5:03*pm, GS wrote:
Hi Gord,

Not sure why you offset to the next column, previous row when the OP
wants the value entered in the same row, next column.

I also don't understand why events are being disabled since the
executable code is wrapped in an IF construct.

In retrospect, I offer this revised approach for consideration:

Private Sub Worksheet_Change(ByVal Target As Range)
* If Cells(1, Target.Column) = "Name" Then
* * If UCase$(Target.Value) = "TOOL ASSEMBLY" Then _
* * * * * * * Target.Offset(, 1) = "A, B, C, D or whatever"
* End If
End Sub

--
Garry

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

works great, thankyou Gentlemen for all your help!!