Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I have reference links in table's heads? | Excel Discussion (Misc queries) | |||
Transforming table's content/display | Excel Worksheet Functions | |||
Dynamically Change a Pivot Table's Data Range | Excel Programming | |||
Change Pivot Table's query | Excel Programming | |||
Change the name of column heads from A,B, C etc to something else | Excel Programming |