Excel 2010 Check Filename & Show or Hide a Button 1

[PWD]

Good afternoon, I thought I should hide a "CopyButton" unless the workbook has already been saved and copied to a "_TEMP" file. But my code just results in:

"Excel 2010 Run-time error '438' Object doesn't support this property or method"

If InStr(ActiveWorkbook.Name, "_TEMP") = 0 Then
Worksheets("Control Panel").CopyButton.Visible = False
End If

It's not such a big deal as I've put some code on the copy button but just wanted it to be a bit smarter.

If InStr(ActiveWorkbook.Name, "_TEMP") = 0 Then
strPrompt = "This is the original workbook, save copy first"
strTitle = "Can't Copy Workbook"
MsgBox strPrompt, vbCritical, strTitle
Exit Sub
End If

How could I hide this button?

Many thanks,
D€$

 

[SkipVought]

So is this an ActiveX or Forms control?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, that's a bit technical for me! It's just a button on a worksheet with a macro assigned to it.

Many thanks,
D€$

 

[SkipVought]

So how did you put the button on the sheet?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Oh, it's another one of those workbooks that I've inherited; my guess is that the originator just drew a shape and then assigned the macro.

Many thanks,
D€$

 

[SkipVought]

Assuming that the control is a Forms Control Object, that is Named CopyButton...

Sub Button1_Click()
    If InStr(ActiveWorkbook.Name, "_TEMP") = 0 Then
        Worksheets("Control Panel").Shapes("CopyButton").OLEFormat.Object.Visible = False
    End If
End Sub

No doubt the CAPTION of the control is also CopyButton. This worked on my sheet.

Actually, I'd code it this way...

Sub Button1_Click()
    Dim bVisible As Boolean
    
    If InStr(ActiveWorkbook.Name, "_TEMP") = 0 Then
        bVisible = False
    Else
        bVisible = True
    End If

    ActiveSheet.Shapes("CopyButton").OLEFormat.Object.Visible = bVisible
End Sub

...whatever sheet this button is on, is the sheet the the button is on!
...and the control can be made visible, after being made not visible.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Ah, terrific; I like your code snippet too!

Many thanks,
D€$

 

[SkipVought]

Yes, I've been know to be snippety at times ;-) thank you!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Could it be snipped to just:

Sub Button1_Click()

ActiveSheet.Shapes("CopyButton").OLEFormat.Object.Visible = _
InStr(ActiveWorkbook.Name, "_TEMP")

End Sub

?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.