Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Column Heading based on value in row | New Users to Excel | |||
Offset based on column heading | Excel Programming | |||
Row filtering based on input box entry (column heading) | Excel Worksheet Functions | |||
Formatting a range based on contents of column/row heading | Excel Programming | |||
Formatting a range based on contents of column/row heading | Excel Programming |