editing control/form objects on a worksheet
 

I have Control command buttons on the excel sheet itself (not Form buttons).

I wish to edit the properties of these from vba. When assign the sheet to a variable and look at it in the watch list, I see what should work.... is ActiveWorkbook.Worksheets("BOM Mgmt").CommandButton_1.Caption

Essentially I should be able to change the caption from my module1 code (I don't want to change the caption from the worksheet code) with:

Public Sub ShowHideSht(Shtname As String)
Dim IsShown As Integer
Dim Sheet As Worksheet
Dim MgmtSheet As Worksheet

Shtname = "BOM-" & Shtname & " Sub"

Set Sheet = ActiveWorkbook.Worksheets(Shtname)
Set MgmtSheet = ActiveWorkbook.Worksheets("CAD Mgmt")

IsShown = Sheet.Visible
If IsShown = xlSheetHidden Or IsShown = xlSheetVeryHidden Then
Sheet.Visible = xlSheetVisible
With MgmtSheet
.CADListButton_1.Caption = "Hide"
End With
Exit Sub
End If
If IsShown = xlSheetVisible Then
Sheet.Visible = xlSheetVeryHidden
MgmtSheet.CADListButton_1.Caption = "Show"
Exit Sub
End If
End Sub

The red code prevents a compile of "Method or data member not found."
I'm confused, because I'm looking at the data member in the variable Watch "MgmtSheet"?? How am I to change the caption of this button.

Thanks in advance
Ricky

editing control/form objects on a worksheet
 

Ricky M. Medley wrote:
The red code prevents a compile of "Method or data member not
found."
I'm confused, because I'm looking at the data member in the variable
Watch "MgmtSheet"?? How am I to change the caption of this button.


Try
MgmtSheet.OLEObjects("CommandButton1").Object.Capt ion = "Hide"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

editing control/form objects on a worksheet
 

Thanks Bill....I'm trying though to understand (hehe) the reasoning behind
it being in that group....!!?
oh well...This has been too long of a search, and thank you

ricky


"Bill Manville" wrote in message
...
Ricky M. Medley wrote:
The red code prevents a compile of "Method or data member not
found."
I'm confused, because I'm looking at the data member in the variable
Watch "MgmtSheet"?? How am I to change the caption of this button.


Try
MgmtSheet.OLEObjects("CommandButton1").Object.Capt ion = "Hide"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup