How to change table's heads content with VBA?
 

Hi,
In excel 2007, I have a table and need to switch values in the head
cells.
(I intend to have a macro to change words(language) in the sheets'
cells and that include a tables heads.

I have a "language reference sheet" where I can refere to different
cells/words depending on what language it should swap between. I tried
with:

Sub SwapEngTo Swe
cnBusinessBenefits.Range("TAB_EffectValues[[#Headers],[Business
Benefits]]").Value = cnReference.Range("Z2").Value
'
End Sub

(The value is Swedish "VerksamhetsEffekter")

"[Business Benefits]" will next time be altered to
"VerksamhetsEffekter" and I can use:

Sub SwapSweToEng
cnBusinessBenefits.Range("TAB_EffectValues[[#Headers],
[VerksamhetsEffekter]]").Value = cnReference.Range("Y2").Value
End Sub

However, the name is still hard coded.
I try with following more flexible way to enable the option to change
words in the excel user GUI, but the syntax doesn't seem to allow a
reference:

cnBusinessBenefits.Range("TAB_EffectValues[[#Headers],
[cnReference.Range("Z2").Value]").Value =
cnReference.Range("Y2").Value

cnBusinessBenefits.Range("TAB_EffectValues[[#Headers],
[cnReference.Range("Y2").Value]").Value =
cnReference.Range("Z2").Value

I also tried to deal with it as a string:

Sub ChangeTableHeads_BusinessBenefits_EngToSwe()
cnBusinessBenefits.Range("TAB_EffectValues[[#Headers],[" &
cnReference.Range("Y2").Value & "]]").Value =
cnReference.Range("Z2").Value

End Sub
Sub ChangeTableHeads_BusinessBenefits_SweToEng()
cnBusinessBenefits.Range("TAB_EffectValues[[#Headers],[" &
cnReference.Range("Z2").Value & "]]").Value =
cnReference.Range("Y2").Value
End Sub

.... but it didn't swallow that either.


Any suggestions?

Regards
Tskogstrom