Adding buttons while looping through files Question
 

Hello,

I have about 170 files that I need to loop through. In the loop I need
to add two buttons and assign macros to them. I can do the part about
assigning the macros, but, even when I just copy two identical buttons
and change their names, they never end up going to the spot where I put
them. I have to go back into the file and move them to their correct
location - which, as you can imagine - would be very, very time consuming.

I hate to attach the code - as it is kind of ugly, but it will give you
an idea of what I am trying to do as I may not be explaining it very
clearly. If the code is needed, please let me know.

Any help would greatly, greatly appreciated.

Thanks much in advance,
Anita

Adding buttons while looping through files Question
 

do you assign a location for the buttons or do you just copy and paste them.
It the latter, turn on the macro recorder while you position the button
manually and this should show you what you need to do.

--
Regards,
Tom Ogilvy

"a" wrote in message
k.net...
Hello,

I have about 170 files that I need to loop through. In the loop I need
to add two buttons and assign macros to them. I can do the part about
assigning the macros, but, even when I just copy two identical buttons
and change their names, they never end up going to the spot where I put
them. I have to go back into the file and move them to their correct
location - which, as you can imagine - would be very, very time consuming.

I hate to attach the code - as it is kind of ugly, but it will give you
an idea of what I am trying to do as I may not be explaining it very
clearly. If the code is needed, please let me know.

Any help would greatly, greatly appreciated.

Thanks much in advance,
Anita

Adding buttons while looping through files Question
 

Tom,

I've tried both and neither seems to work. Using the recorder I thought
would give me the answer - but it didn't work. I then tried copying and
pasting them with the macro recorder, then recording another macro as I
placed them again - and, if I remember correctly - the object number
changed and so that didn't work either.

Do you know of any good source I could go to that might address this -
looping through the files, copying and pasting the buttons and then
placing them?

Thanks much for responding.

Regards,
Anita
Tom Ogilvy wrote:

do you assign a location for the buttons or do you just copy and paste them.
It the latter, turn on the macro recorder while you position the button
manually and this should show you what you need to do.

Adding buttons while looping through files Question
 

for a commandbutton

Sub MoveButton()
Dim OleObj As OLEObject
ActiveSheet.Shapes("CommandButton1").Select
Selection.Copy
Range("G15").Select
ActiveSheet.Paste
Set OleObj = Selection
With Range("B9")
OleObj.Top = .Top
OleObj.Left = .Left
End With
End Sub

for a forms toolbar button
Sub MoveButton()
Dim btn As Button
ActiveSheet.Shapes("Button 1").Select
Selection.Copy
Range("G15").Select
ActiveSheet.Paste
Debug.Print TypeName(Selection)
Set btn = Selection
With Range("B9")
btn.Top = .Top
btn.Left = .Left
End With
End Sub


Of course, selecting B9 before the past would obviate the need to move them.

--
Regards,
Tom Ogilvy


"a" wrote in message
nk.net...
Tom,

I've tried both and neither seems to work. Using the recorder I thought
would give me the answer - but it didn't work. I then tried copying and
pasting them with the macro recorder, then recording another macro as I
placed them again - and, if I remember correctly - the object number
changed and so that didn't work either.

Do you know of any good source I could go to that might address this -
looping through the files, copying and pasting the buttons and then
placing them?

Thanks much for responding.

Regards,
Anita
Tom Ogilvy wrote:

do you assign a location for the buttons or do you just copy and paste
them.
It the latter, turn on the macro recorder while you position the button
manually and this should show you what you need to do.

Adding buttons while looping through files Question
 

Tom,

Thanks! I haven't tried this - but what I was wishing for was adding
some sort of variable - which you did in the second example which
applies to what I'm doing.

Thanks again! It may need some tweaking, but I think it puts me on the
right path.

Best Regards,
Anita

Tom Ogilvy wrote:

for a commandbutton

Sub MoveButton()
Dim OleObj As OLEObject
ActiveSheet.Shapes("CommandButton1").Select
Selection.Copy
Range("G15").Select
ActiveSheet.Paste
Set OleObj = Selection
With Range("B9")
OleObj.Top = .Top
OleObj.Left = .Left
End With
End Sub

for a forms toolbar button
Sub MoveButton()
Dim btn As Button
ActiveSheet.Shapes("Button 1").Select
Selection.Copy
Range("G15").Select
ActiveSheet.Paste
Debug.Print TypeName(Selection)
Set btn = Selection
With Range("B9")
btn.Top = .Top
btn.Left = .Left
End With
End Sub


Of course, selecting B9 before the past would obviate the need to move them.