Accessing Command Buttons or Drop Down Lists, etc from another Excel sheet

Hi Geoff,
You can access the activex controls on another sheet using the OLEObjects collection. The trick is knowing the properties of the control and how you access the correct properties.
Example:
activesheets("Sheet1").OLEObjects("CommandButton1").Object.Caption = "Kilroy was here!"
This changes the caption property. Now consider the following code:
activesheets("Sheet1").OLEObjects.Visible = False
This accomplishes the same type of thing, however you are "talking" to the OLEObject, not the OLEObject.Object. Subtle I know, but depending on what control you are accessing and what you want it to do. I've had to experiment to figure out the properties I want.
One Caveat - if you are copying the worksheets, and there are controls on the worksheet, then the controls will be renamed back to their original name in Excel 97. It probably would not affect you, but I tend to be either thorough or anal in my explainations. You can choose the adjective you prefer. I'd be interested in seeing what properties you encounter. Please tell us of your solutions and problems you encounter.
Tuc

Hey Tuc,

Thanks for such a speedy response!

Here is what I tried (using my actual project variable and Excel sheet names):

Dim sheet as Worksheet
Dim testString As String

Set sheet = Worksheets("Pipe 1")

testString = sheet.OLEObjects(cmbPerfType).Object.Value

Then during execution, it said that last call failed but I did manage to find help on it within the VBA help files and it had EXACTLY what you had wrote but it's not working for me for some reason.

I'm actually saving that value to a file so it's actually located within a Write statement like:

Write #FileNumber, sheet.OLEObjects(cmbPerfType).Object.Value

and that's where the error occurs. Maybe I'm missing something. Any ideas perhaps? If not, that's cool. I appreciate the help you've given already. It's steering me in the right direction :)

Geoff

Geoff,

What you are missing is the index that you are using. It looks to me like you are trying to pass the object as the index value. That will not work the way you presented it. Try this:

testString = sheet.OLEObjects("cmbPerfType").Object.Value where "cmbPertType" is the name of the control. Another way would be testString = sheet.OLEObjects(1).Object.Value where the control is the first object drawn on the worksheet. You are dealing with a collection and the index is either a numeric value or a string indicating the item name.
Tuc

Tuc,

Ok, I think I understand perfectly what's going on now. Let me show you my EXACT code and tell you what's happening:

'Pipe 1 Inputs
Set sheet = Worksheets("Pipe 1")

Write #FileNumber, sheet.Range("G20").Value, sheet.Range("G21").Value, sheet.Range("G22").Value, _
sheet.Range("G23").Value, sheet.Range("G24").Value, sheet.Range("G25").Value, _
sheet.Range("J23").Value, sheet.Range("J24").Value, sheet.OLEObjects("cmbPerfType").Object.Value, _
sheet.OLEObjects("cmbOutletType").Object.Value

When it hits that segment of code during runtime, here is the error I receive:

Run-time error '1004':

Method 'OLEObjects' of object '_Worksheet' failed

I triple checked the names of those combo boxes and they are correct. I also made sure to make them strings. I wanted to try and use their index numbers instead but I didn't know where to look to find their index.

Thanks for putting up with me :)

Geoff

Geoff,
No sweat. It's just that I've already made these mistakes. Experience is what you get when you don't get what you want.

So you are trying to get to the items in the combo list. I don't think the value property will give you what you want. Now you are using another collection to get to the list of items that are contained in the list. In "normal" VB I would access the first item in the listbox/combobox using yet another index. Something like comboBox1.list(comboBox1.listindex) which would return the item that was currently selected. I'll have to look around for some examples. I can not at the moment. I'll try to post an example later today.
Tuc

Hi Geoff
Tuc gave a good explaination.
Your code as given should have worked !?
Things to check why it didn't.............
1) I know you said you checked the spelling
but are you sure this is the exact name ?
An easy way to get the name is to select the
combobox then select the namebox - next to the
formulaBar.....Press Ctrl + C to copy then
in your code press Ctrl V to paste.
2) Are you using the ActiveX control ComboBox ??
or the Forms Combobox ??
Tucs suggestions are for ActiveX controls.

Easy way to check is when you select the control
the Formulabar will have something like this;
=EMBED("Forms.ComboBox.1","") & right clicking on it will give you an option to view it's properties.

HTH

Ivan

gave a good explaination.

Ivan,
Right on the money! I completely forgot about the forms controls. Thanks for reminding all of us! I also appreciate the kind words.

Tuc

Geoff,
Okay here is some sample code that I wrote then when I got stuck I found a MSDN Q article which I referenced.

Now start with a worksheet and draw three controls on it. One listbox (ListBox1) and two command buttons (CommandButton1, CommandButton2). Listboxes and Comboboxes have essentially the same properties and methods that I think of them the same. Add the following code:

Option Explicit

Private Sub CommandButton1_Click()
Dim intCounter As Integer
' Load the list box
For intCounter = 1 To 10
ListBox1.AddItem intCounter & Chr(96 + intCounter)
Next intCounter

End Sub

Private Sub CommandButton2_Click()

Dim intCounter2 As Integer
Dim strHolder As String
Dim strHolder2 As String

' Note that I started with zero and looped to the ListCount property less 1.
For intCounter2 = 0 To Worksheets("Sheet1").OLEObjects("ListBox1").Object.ListCount - 1
strHolder = strHolder & Worksheets("Sheet1").OLEObjects("ListBox1").Object.List(intCounter2) & vbCrLf
Next intCounter2

MsgBox "The values are " & vbCrLf & strHolder

' If Multi Select Property is turned on then you will have to examine each item to see if it is selected.
' I set the property to 3 - fmMultiSelectExtended
' See Knowledge Base Article q161346.

For intCounter2 = 0 To Worksheets("Sheet1").OLEObjects("ListBox1").Object.ListCount - 1
If Worksheets("Sheet1").OLEObjects("ListBox1").Object.Selected(intCounter2) = True Then

strHolder2 = strHolder2 & Worksheets("Sheet1").OLEObjects("ListBox1").Object.List(intCounter2) & vbCrLf
End If
Next intCounter2

MsgBox "The selected values are " & vbCrLf & strHolder2

End Sub

'Code Ends Here.

CommandButton1 loads the listbox. Commandbutton2 reads the information from the listbox in it's entirety and displays the first messagebox, then it reads the listbox for any selected items and then displays those selected items in the second messagebox. Now for that to work, first you have to set the Multi-Select property for the listbox/combobox control, and then something has to be selected. I got stuck on the selected part and that's when I looked it up on MSDN.

I think this is enough to get you started. Let me know how it turns out.

Tuc

Tuc,
Sorry, I have been gone for 2 days but I haven't forgotten about this :)

I have discovered something that I've been doing incorrectly. I'm not entirely sure how to state this but I'll give it a shot.

I have 2 drop down lists that I need to pull values from that are on the same sheet. They have different names in the VB side of things, but they have the same name in the Excel side. In this call:

sheet.OLEObjects("cmbOutletType").Object.Value

I was using the list's VB name instead of the Excel name which is where I was going wrong. So I managed to get it working. I believe that all I need to do now is change the name of the other drop down list and I will be able to access its value as well.

Many many thanks to you and Ivan for all the help. I'm sure I will run across this issue in the future only this time I'll know exactly what to do :) Thanks for sharing the knowledge!

Geoff ,