Worksheet CommandButtons with conflicting names

[MikeO]

On a worksheet, I have several commandbuttons. For many of the buttons the name in the "name box" is different than the name in the "(name)" field under Properties. I'm not sure how it's possible, or how to recreate such a situation...but, it happened.

One button, for example would have:
In the name box: "CommandButton5"
In the (name) properties field: "RunProcess"

The real problem arises when I'm using code to loop through the objects on the worksheet, and build an array of the object names. I'm trying to match the name of the buttons to their click events. In the code, "btn.name" returns the name found in the "name box". The one that I want is the name in the "(name)" field under properties because this is the one that matches the click event.

How can I determine, in code, what is entered in the "(name)" field for an object?

[MikeO]

I found this on msdn2.microsoft.com:


An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other isn't automatically changed to match.


I still haven't found a solution to my problem though. Anybody have any ideas?

[Bob Phillips]

It sounds as though you have a control toolbox button, and the issue here is that you have an OLEObject which is the container for the control, and the ActiveX control object itself.

But why aren't you just using using the Click event of each button for your code?

Can you post the code, or better, the workbook?

[MikeO]

Unfortunately, the workbook is on a computer that cannot be connected to the internet. I'm building a tool that can break down an entire workbook (or at least 90% of it) into a text file. This would include contents, formats, objects, and vba code. The text file would then be able to be transferred safely to other computers, where to tool would use that text file to recreate the entire workbook. Everything works great, except for the buttons with two names. It's not a huge problem, because I could manually edit each button's name after the tool recreates them, but it just irritates me that I can't make the tool do exactly what I want.

Here's the small portion of the code that writes the buttons properties to the text file:
Dim MyObjects As OLEObjects
Dim Btn As OLEObject
Dim ThisButton As CommandButton

Set MyObjects = TestSheet.OLEObjects
For Each Btn In MyObjects
If Btn.progID = "Forms.CommandButton.1" Then
Set ThisButton = Btn.Object
Print #ThisFileNum, Btn.Name
Print #ThisFileNum, GetObjectProperties(Btn)
End If
Next Btn



For the example I gave earlier, "Btn.Name" gives me "CommandButton5". But, I really just want to record the name in the "(Name)" field under the button's properties...which is "ProcessData". The click event for the button is "Private Sub ProcessData_Click()".
When I recreate the button with the tool, the only name that the tool can assign it is the name that was retrieved in the previous process. So now the new button's name ("CommandButton5") does not match the click event code that was recreated in the vba module for that worksheet...and the button does nothing when clicked.

[Norie]

Mike

Why do you need this tool?

Couldn't the workbook just be copied?

That would cover 100% of it not just 90%.

Or perhaps a template is an idea.

[Bob Phillips]

If I can't see it, I don't think that I can help. All of my buttons have just the one name, but I have seen what you mention, I just can't recall where and when. I do remember that I just renamed them and had no more problems.

[Bob Phillips]

I think I have re-created it.

Does this do what you want?



Dim MyObjects As OLEObjects
Dim Btn As OLEObject
Dim ThisButton As CommandButton

Set MyObjects = TestSheet.OLEObjects
For Each Btn In MyObjects
If TypeName(Btn.Object) = "CommandButton" Then
Set ThisButton = Btn.Object
Debug.Print IIf(ThisButton.Name = Btn.Name, Btn.Name, ThisButton.Name)
Debug.Print GetObjectProperties(Btn)
End If
Next Btn

[MikeO]

Unfortunately, workbooks (or any microsoft product) cannot be transferred between computers. Without getting into too much detail, the computers have different caveats, and all files need to be screened before being approved for moving between computers. Microsoft products cannot be thoroughly screened because there is too much hidden data involved. Text files can.

[MikeO]

xld...you're a genius! Thanks so much, that worked perfectly. I don't understand why I couldn't get ThisButton.Name to work before. Regardless, thanks a lot.