Prev: Random Running of a Script?
Next: find sheet
From: cate on 3 Apr 2010 11:14
I have a main sheet that I replicate as 'what if' versions. There are
no collisions with any names on these copies since all code refers to
them in the context of the sheet.

Except for buttons and their attribute Caption.

There is one these buttons that has it's caption changed ('Show',
'Hide') but even within the context of the current sheet, it fails.

wS.Buttons("button_show_hide").Caption = "Hide"

What is REALLY strange is that the caption seems to be changing
somewhere, because the decision to Show or Hide is based on the
following. Again, this is successful:

StrComp(wS.Buttons("button_show_hide").Caption, "Hide")

The main sheet, from which all these 'What if's are copied, it's
button continues to work just fine. The caption toggles.

Anyone have an idea how to fix this?
From: Dave Peterson on 3 Apr 2010 11:54
Maybe your current sheet isn't what you expect. You didn't share how ws was
set.

This may help (or not!) you debug...

Option Explicit
Sub testme()

Dim ws As Worksheet
Dim BTN As Button

Set ws = Worksheets("SomeSheetNameHere")

For Each BTN In ws.Buttons
With BTN
MsgBox .Name & vbLf _
& .TopLeftCell.Address & vbLf _
& .Visible
End With
Next BTN

End Sub

You may even want to loop through the worksheets in the workbook, too:

Option Explicit
Sub testme2()

Dim ws As Worksheet
Dim BTN As Button

For Each ws In ActiveWorkbook.Worksheets
For Each BTN In ws.Buttons
With BTN
MsgBox .Parent.Name & vbLf _
& .Name & vbLf _
& .TopLeftCell.Address & vbLf _
& .Visible
End With
Next BTN
Next ws

End Sub

cate wrote:
>
> I have a main sheet that I replicate as 'what if' versions. There are
> no collisions with any names on these copies since all code refers to
> them in the context of the sheet.
>
> Except for buttons and their attribute Caption.
>
> There is one these buttons that has it's caption changed ('Show',
> 'Hide') but even within the context of the current sheet, it fails.
>
> wS.Buttons("button_show_hide").Caption = "Hide"
>
> What is REALLY strange is that the caption seems to be changing
> somewhere, because the decision to Show or Hide is based on the
> following. Again, this is successful:
>
> StrComp(wS.Buttons("button_show_hide").Caption, "Hide")
>
> The main sheet, from which all these 'What if's are copied, it's
> button continues to work just fine. The caption toggles.
>
> Anyone have an idea how to fix this?

--

Dave Peterson
From: cate on 3 Apr 2010 13:45
A close and open the wb did the trick. I guess even with -->edit --
>move or copy sheet

some things are not completed.

Thanks
 | 
Pages: 1
Prev: Random Running of a Script?
Next: find sheet