Moving Objects With VBA

Will S

Board Regular
Joined
Oct 29, 2013
Messages
69
Is there a way in VBA to send an object (Groups made of Text boxes and Check boxes) to a set cell rather than using what record macro does of having the selected object move up or down with a numerical value.

The controls move whenever I copy a sheet that has a lot of hidden rows, they move to the highest unhidden row, so all I need to do is move them down. Is there a code of either moving the various groups to a specified cell or maybe counting the row height of a set range so I have the value to move it down by?

I'm having a major problem with this and I'm hoping I don't need to Ungroup it all and have the VBA run through things individually.

Any help is greatly appreciated, Thanks.
~Will S
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try something like

Code:
Sub test()
With ActiveSheet.Shapes("Group 3")
    .Top = Range("F6").Top
    .Left = Range("F6").Left
End With
End Sub
 

Will S

Board Regular
Joined
Oct 29, 2013
Messages
69
Thank you loads! I've just tested it out and looks to be exactly what I'm after. Now all I need to do is shape the group so it aligns with the cell. you've been a big help and the quick reply is much appreciated.

~Will S
 

aeroman98

New Member
Joined
Jun 28, 2016
Messages
1
Try something like

Code:
Sub test()
With ActiveSheet.Shapes("Group 3")
    .Top = Range("F6").Top
    .Left = Range("F6").Left
End With
End Sub
I have a question. I have a toggle button that I've coded to be green and when clicked turns to red. I'm also trying to fix it so that when the toggle button is pressed it locks a group object in a certain position and when unclicked the group object is free to be moved within a certain range. The range is cells BC23 to BX23. I also want this to work on a protected sheet but doesn't have to. My code is just for the toggle button color so far. Any help would be much appreciated!

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
ToggleButton1.BackColor = vbRed
Else
ToggleButton1.BackColor = vbGreen
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,396
Messages
5,444,233
Members
405,275
Latest member
RefreshSQL

This Week's Hot Topics

Top