How to remane Excel CommandButton in VBA

[dkathrens77]

Thanks for all your help, folks. I have another question:

How do I programmatically rename a commandbutton in Excel 97? I don't have VBA help installed on this office computer or I'd look it up myself.

I've just about driven myself bonkers trying to guess the correct incantation for this. Excel is soooo different from Access!

Thanks,

Dennis

 

[SkipVought]

Hi,

Do you really know what you are doing?

When a commandbutton is added, it's CommandButton1, 2, 3 etc

It's simple to change

Sub test()
   ActiveSheet.CommandButton1.Name = "SomeOtherName"
End Sub

But now, all your code must refer to SomeOtherName...

  With ActiveSheet.SomeOtherName
    .Top = 20
    .Left = 50
  End With

Is this what you want?



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dkathrens77]

Thanks, Skip, for the timely tip.

I can make Access jump through flaming hoops, but no, I don't really know what I am doing in Excel.

If I at least had the VBA help files on this office machine, or could install them myself, I'd be asking far fewer simplistic questions.

 

[SkipVought]

dkathrens77,

It's just the flip for me. I cut my teeth on Excel 95 VBA over 10 years ago and had the extreem good fortune to have a former Microsoft tech give me some early help.

Did this answer the question or are there more?

BTW, you'll get lots of great Excel VBA help here with many expert contributors.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dkathrens77]

PS Yes this is exactly what I needed. I have the code for the command buttons in a module, and a button named "cmdNewSheet" on the existing worksheet that copies the worksheet it is on and then renames the new sheet using an InputBox.

I noticed what you said, that my command buttons get renamed CommandButton1, 2, 3 when the sheet is copied and then they aren't associated with the ButtonClick events in the form which call the code in the module.

This solved my problem, thanks so much.

Dennis