Move (grouped) shapes based on cell value

[RemcoVBA]

Good morning,

For our local football club i'm responsible for planning of the pitches on saturday mornings. The football asociation sends me a schedule on a weekly basis which includes an initial planning of all matches (teams, lockerrooms, fields, starting time) and which I want to reschedule in some case. In order to make things more visual and easier to fine-tune I'm creating a sheet that shows the fields and time slots on the X and Y axes and grouped shapes (first shape represents the lockerroom, second shape represents the team name; together the two shapes represents the total time the match will take).

So what I'm looking for is 2 things
1) From the intial list I want to automatically create an overview which means that each grouped shape needs to move to the corresponding cell. Each team will have 1 line in the list (as they will only play one match) and their name is unique.
So in the example sheet the group blue shape should move to cell C4 and the orange shape to D3.

This will be my starting point for manually moving around the shapes to reschedule time slots. I've set the shape settings to 'snap to grid' so that things will look tidy.

2) Next step would be that once I've manually moved the grouped shapes the new time slot / field combination should be returned. So for example: I've manually moved U18 to 8:00 on field 4 and I want this to be visible in the adjusted schedule.

I hope I'm explaining clearly and hope I'm not overasking. I tried to figure it out myself but can't get it right.

Thanks in advance, your' help is greatly appreciated.

Remco

 

[RemcoVBA]

Hi all,

I think I solved part of my problem. With below script I can move rectangle 1 based on the cell value I put in cell A1. Works exactly how I want it.
But now I have a new challenge. Rectangle 1 should move together with Rectangle 2 as one shape (they are grouped). Rectangle 1 should be leading. Move should still be based on the value in A1.

I think I should use the array function for this but running into errors all the time.

Can anyone advise?

Thanks
Remco



Sub Shifshape2()

'
Dim oShape As Shape

Set oShape = ActiveSheet.Shapes("Rectangle 1")


Dim oCell As Range

Set oCell = Range(Range("A1").Value)

'the value of Range("A1) will be a text saying which cell the shape will go to.


oShape.Top = oCell.Top
oShape.Left = oCell.Left
'
End Sub

 

[RemcoVBA]

Hi all,

I think I solved part of my problem. With below script I can move rectangle 1 based on the cell value I put in cell A1. Works exactly how I want it.
But now I have a new challenge. Rectangle 1 should move together with Rectangle 2 as one shape (they are grouped). Rectangle 1 should be leading. Move should still be based on the value in A1.

I think I should use the array function for this but running into errors all the time.

Can anyone advise?

Thanks
Remco



Sub Shifshape2()

'
Dim oShape As Shape

Set oShape = ActiveSheet.Shapes("Rectangle 1")


Dim oCell As Range

Set oCell = Range(Range("A1").Value)

'the value of Range("A1) will be a text saying which cell the shape will go to.


oShape.Top = oCell.Top
oShape.Left = oCell.Left
'
End Sub

The answer on above question turned out to be very simple: just replace 'rectangle 1' with 'group 1'.