R
Ricky M. Medley
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
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