Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Embedded Excel Objects in PowerPoint resize after editing | Excel Discussion (Misc queries) | |||
2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
Editing problems with embedded Excel objects | Excel Discussion (Misc queries) | |||
How do I create a form in a worksheet with control option buttons. | New Users to Excel | |||
Including control toolbox objects in tab order | Excel Programming |