Return Column of Found Data
 

I am using Excel 2003 SP2.

In providing a series of macros for a workgroup who like to customize their
hotkey assignments, I am attempting to allow them to preserve their setup
from version to version without retaining multiple versions. My plan is to
use a table on a hidden worksheet that sets up their custom assignments when
the file loads.

UserNames Sprinks Johnny Default
Macro Name
Macro A a A a
Macro B x t x
....etc.

Ideally, I would like to search a named range for their username, and either
return the column number, or if it is not found, return the column number of
the one named "Default". Can someone tell me how to do this?

Once the column is determined, I will loop through the list set the key
assignments:

' Pseudocode
intColumn = ' Expression that returns column number
i = 4 ' Data begins in 4th row

Do
strMacroName = Cells(i, 1).Value
strKey = Cells(i,intColumn)
Application.MacroOptions Macro:=strMacroName, ShortcutKey:=strKey
i = i + 1
Loop Until Cells(i,1).Value = ""

Thanks for any assistance.

Sprinks

Return Column of Found Data
 

Dim res as Variant, rng as Range
res = Application.Match(Application.UserName,rows(1),0)
if not iserror(res) then
set rng = Range("A1:IV1")(1,res)
else
set rng = Range("F1") ' default
end if

--
regards,
Tom Ogilvy


"Sprinks" wrote:

I am using Excel 2003 SP2.

In providing a series of macros for a workgroup who like to customize their
hotkey assignments, I am attempting to allow them to preserve their setup
from version to version without retaining multiple versions. My plan is to
use a table on a hidden worksheet that sets up their custom assignments when
the file loads.

UserNames Sprinks Johnny Default
Macro Name
Macro A a A a
Macro B x t x
...etc.

Ideally, I would like to search a named range for their username, and either
return the column number, or if it is not found, return the column number of
the one named "Default". Can someone tell me how to do this?

Once the column is determined, I will loop through the list set the key
assignments:

' Pseudocode
intColumn = ' Expression that returns column number
i = 4 ' Data begins in 4th row

Do
strMacroName = Cells(i, 1).Value
strKey = Cells(i,intColumn)
Application.MacroOptions Macro:=strMacroName, ShortcutKey:=strKey
i = i + 1
Loop Until Cells(i,1).Value = ""

Thanks for any assistance.

Sprinks

Return Column of Found Data
 

Thanks, Tom.

Sprinks

"Tom Ogilvy" wrote:

Dim res as Variant, rng as Range
res = Application.Match(Application.UserName,rows(1),0)
if not iserror(res) then
set rng = Range("A1:IV1")(1,res)
else
set rng = Range("F1") ' default
end if

--
regards,
Tom Ogilvy


"Sprinks" wrote:

I am using Excel 2003 SP2.

In providing a series of macros for a workgroup who like to customize their
hotkey assignments, I am attempting to allow them to preserve their setup
from version to version without retaining multiple versions. My plan is to
use a table on a hidden worksheet that sets up their custom assignments when
the file loads.

UserNames Sprinks Johnny Default
Macro Name
Macro A a A a
Macro B x t x
...etc.

Ideally, I would like to search a named range for their username, and either
return the column number, or if it is not found, return the column number of
the one named "Default". Can someone tell me how to do this?

Once the column is determined, I will loop through the list set the key
assignments:

' Pseudocode
intColumn = ' Expression that returns column number
i = 4 ' Data begins in 4th row

Do
strMacroName = Cells(i, 1).Value
strKey = Cells(i,intColumn)
Application.MacroOptions Macro:=strMacroName, ShortcutKey:=strKey
i = i + 1
Loop Until Cells(i,1).Value = ""

Thanks for any assistance.

Sprinks