Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
LinkedCell problem TFriis Excel Programming 4 November 27th 07 12:30 PM
setting LinkedCell using VBA michael.beckinsale Excel Programming 2 September 1st 06 04:20 PM
Export Linkedcell racerman Excel Programming 2 September 5th 05 08:39 PM
Change linkedcell color for a minute only when value changes TJ[_3_] Excel Programming 0 September 2nd 05 03:34 PM
Linkedcell Problems... cornishbloke[_25_] Excel Programming 3 January 21st 04 02:45 PM


All times are GMT +1. The time now is 01:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"