VBA code based on column heading

J

john taiariol

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.
 
G

GS

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.
Click to expand...

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
 
G

GS

GS used his keyboard to write :
It happens that john taiariol formulated :

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
Click to expand...

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])
 
J

john taiariol

GS used his keyboard to write :




It happens that john taiariol formulated :
Click to expand...
You can define Names for your columns. This would usually be something that
relates to the headers for each column.

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

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 -
Click to expand...

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

GS

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

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")
 
J

john taiariol

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

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
Click to expand...

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
 
G

GS

john taiariol explained on 4/8/2011 :
john taiariol has brought this to us :
so if I have column heading called "Name" how would you write the code?
Click to expand...

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
Click to expand...

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
Click to expand...

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>
 
G

GS

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
 
G

GS

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
 
G

Gord Dibben

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


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

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
Click to expand...

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
Click to expand...
 
G

GS

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
 
G

GS

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
 
J

john taiariol

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
Click to expand...

works great, thankyou Gentlemen for all your help!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA triggers on tabkey 8
VBA code if/then 2
Automatically Copy Information when data is entered 2
Have Cell To Left Show Fixed Time When Cell Changes 5
Ambiguous name detected: worksheet_change 2
Word VBA Copy Heading and Content 4
Event recognition. 11
Need a accumulator driven from second cell 4

Top