Identify column header name instead of column number

excel 2003
vba

The following code executes if in column 3

Problem:
I need to execute the code only if the (target) column header is "Customer Mfgnumber Search"

else

msgbox "Not in correct column to execute search"


If target.Column = 3 Then
        KeyHook
    Else
        KeyUnhook
    End If

End Sub


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Assuming the header is in row 1, how about

If Cells(1, target.column) = "Customer Mfgnumber Search" then
' rest of your code

Open in new window


cheers, teylyn
0
 
jppintoCommented:
If (target.Column = 3) And (target.cells(1,3).Value="Customer Mfgnumber Search") Then
        KeyHook
    Else
        KeyUnhook
    End If
End Sub
0
 
jppintoCommented:
Picking teylyn sugestion, you could try like this:

If (target.Column = 3) And (Cells(1,target.column).Value="Customer Mfgnumber Search") Then
        KeyHook
    Else
        KeyUnhook
    End If
End Sub
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
As I understand it, the code should run if the column header is "Customer Mfgnumber Search", regardless of which column it is in. That's what my suggestion does.

cheers, teylyn
0
 
jppintoCommented:
OK teylyn, I can agree with that but at the same time I have some doubts...let's see what the author has to say.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Well, the title says "instead of column number" ....
0
 
zorvek (Kevin Jones)ConsultantCommented:
This should work. Change "Rows(1)" to the row containing the headers is they are not in row 1.

    If Target.Columns(1).EntireColumn.Rows(1) = "Customer Mfgnumber Search" Then
        KeyHook
    Else
        KeyUnhook
    End If

Kevin
0
 
FordraidersAuthor Commented:
teylyn,
"Well, the title says "instead of column number" ...."   correct..
because it will vary  from workbook/sheet to workbook/sheet...


0
 
FordraidersAuthor Commented:
Thanks to all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.