Move (grouped) shapes based on cell value

RemcoVBA

New Member
Joined
Sep 21, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    33.5 KB · Views: 14

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RemcoVBA

New Member
Joined
Sep 21, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 21, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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'.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,750
Members
425,430
Latest member
corinaas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top