Positioning an AutoShape on the Worksheet

[Cyberdude]

I have a worksheet that has only 3 columns. After creating the worksheet, I add 6 AutoShape rectangles which serve as command buttons (delete sheet, sort column 2, etc.). My problem is that the width of the columns change depending on when and where I create the worksheet. The column widths remain fixed after the sheet is created. When I create the "buttons", I position them to what seems like a "safe" location on the right side of the sheet, but after the sheet is created, in some cases, the columns are sufficiently wide that some of the data in the rightmost column is covered up by the buttons.
What can I do in VBA to be guaranteed that the buttons are located, say, over the 4th column, which is always empty?

[Jacob Hilderbrand]

You can use the Left and Top property for the cells you want. So if you want that shape at D5 then use Range("D5").Top and Range("D5").Left to position the shape.

[Cyberdude]

Thanx, DRJ...I'll give it a try. Say, I see you live in California.
What city (if that's not too persoonal)? I'm in Morgan Hill, just outside of San Jose.

[Jacob Hilderbrand]

I live in Citrus Heights (close to Sacramento).

[Zack Barresse]

To cover D5, use the two properties DRJ mentioned, then use the Height and Width properties to set the other two sides.

An example can be found here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=120

[Killian]

I'd create a number of named rectangles I wanted to reposition on a sheet like this

Sub Cre8NuSheet()
     '(Create & format sheet logic here)
    Call AddButtons(6, True)
End Sub

Sub RefreshButtons()
    Call AddButtons(6, False)
End Sub

Sub AddButtons(number As Integer, newButtons As Boolean)
'routine to create a number of buttons
    Dim Button As Shape
    Dim i As Integer
    'Dim Button As New Collection
For i = 1 To number
        If newButtons = True Then
            Set Button = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, 0, 50, 25)
        Else
            Set Button = ActiveSheet.Shapes("myButton" & i)
        End If
        With Button
            .Left = ActiveSheet.Columns("D").Left
            .Top = (50 * (i - 1)) + (10 * i)
            .Name = "myButton" & i
        End With
    Next
    Set myButton = Nothing
End Sub

Although if you set the Placement property of the shape to = xlMove when you create it, it will move with the column if it resizes anyway

[Cyberdude]

Thank all of you for some great help. I have managed to complete the project because of you, and it works grrrrrrreat!
Oh, the project ... it started out as an attempt to find a way to search the list of macros for a specific one I couldn't seem to find. The resulting "system" of macros not only lists all the macro names and their associated module names, it will sort them, look for a name containing ... , and search for duplicates. It also provides a count of how many macros I have in each workbook. In my Test workbook I was astonished to see that there are 243! And nearly that many in Personal. Now I can do some house cleaning. Thanx again, gang.

[Zack Barresse]

.. there are 243! And nearly that many in Personal..

Holy ****!!! Wow!! That's gotta be some kind of record!

[Cyberdude]

I gotta admit that my Test workbook DOES close VERY slowly.

[Zack Barresse]

You know, I may be opening a bag of worms here, but may I inquire as to what your 243 macros actually do? I realize this may include worksheet/workbook level events as well, or maybe even some Class module code. With that many, I'm thinking that there may either be 1) easier native resources you can be utilizing, or 2) consolidating and/or shortening of what you do have.

I can see 50 .. I can even see upwards of 100 - maybe. I'm just shocked..

[Jacob Hilderbrand]

Yes, that truly is a lot. I am certain that many of those macros can be combined. Just some suggestions:

• Use a Class Module to group similar controls that all have a similar event.

• Use WorkBook level Worksheet events instead of repeating Worksheet level events for multiple sheets.

• Create Subs and Functions that can be supplied with various Arguments to combine multiple macros.

[Cyberdude]

OK, guys, I hear you. That's not a typical count for my workbooks. I keep one workbook named "Test" that I store snippits of code I collect or I'm developing. I've had that workbook for many years, and while a lot of that stuff has no immediate use, you would be surprised how many times I've gone back looking for stuff I did partially years ago. Maybe I didn't understand it then, and now I think it's worth reviving. I've already started weeding out some REALLY useless stuff. I gotta admit, I was surprised too. My "production" stuff doesn't have nearly that many. Thanx for your concern.