Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change LinkedCell with VBA
After searching in this group and lots of trial and error, I've
finally managed to get the following working: 1. Select a range on my sheet with form object in it, which have linked cells to some cells (possibly on another sheet). 2. Have a macro change those links all at once to cells a couple of rows lower. This makes copying several comboboxen and listboxes a lot easier.. the code is for future users to spare them the work: Sub TransposeLinkedCell() offset = inputbox("Hoeveel regels moeten de verwijzigen in de selectie veranderen? (+1 = naar onderen, -1 = naar boven)", "Verander verwijzing", 15) For Each s In ActiveSheet.Shapes With s.BottomRightCell If .Row Selection.Row And .Row < Selection.Row + Selection.Rows.Count Then If s.ControlFormat.LinkedCell < "" Then Dim sheet, addr, fulladdr As String fulladdr = s.ControlFormat.LinkedCell Dim index As Integer On Error Resume Next 'if no exclamation is found, just resume index = Application.WorksheetFunction.Search("!", fulladdr) If index 0 Then sheet = Left(s.ControlFormat.LinkedCell, index) End If addr = Right(s.ControlFormat.LinkedCell, Len(fulladdr) - index) addr = ActiveSheet.Range(addr).offset(15).Address s.ControlFormat.LinkedCell = sheet & addr End If End If End With Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LinkedCell problem | Excel Programming | |||
setting LinkedCell using VBA | Excel Programming | |||
Export Linkedcell | Excel Programming | |||
Change linkedcell color for a minute only when value changes | Excel Programming | |||
Linkedcell Problems... | Excel Programming |