making a button invisible from vb6 (resolved)

[Goh]

is it possible to make a button in an excel spreadseet to become invisible from vb6 after calling out the excel template?

[agmorgan]

Dont post the same question in multiple forums.
http://www.vbforums.com/showthread.php?threadid=287931

[RobDog888]

Here is a small demo I made for you to see how to control the
command buttons in Excel from VB. The demo creates a new
workbook and creates a command button in Excel. Then on the
click of the vb button it will change the visibility of the Excel
command button.

'Add a reference to MS Excel xx.x Object Library
'Add a reference to MS Office xx.x Object Library
'Add one command button (Command1)
Option Explicit
 
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSHT As Excel.Worksheet
Dim oSHP As Excel.Shape
 
Private Sub Command1_Click()
    For Each oSHP In oSHT.Shapes
        If oSHP.Type = msoOLEControlObject And oSHP.Name = "CommandButton1" Then
            'Toggle the visibility property of the Excel command button on each click of the vb button
            oSHP.Visible = Not oSHP.Visible
        End If
    Next
End Sub
 
Private Sub Form_Load()
    Set oXL = New Excel.Application
    'Add a new workbook for the demo
    Set oWB = oXL.Workbooks.Add
    oWB.Activate
    oXL.Visible = True
    Set oSHT = oWB.Worksheets("Sheet1")
    'Add a command button to sheet1
    oSHT.Shapes.AddOLEObject Left:=100, Top:=100, Width:=100, Height:=25, ClassType:="Forms.CommandButton.1"
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    'Clean up
    Set oSHP = Nothing
    Set oSHT = Nothing
    oWB.Close False
    Set oWB = Nothing
    oXL.Quit
    Set oXL = Nothing
End Sub

Enjoy

[Goh]

wow! thanks alot man!

[RobDog888]

Your welcome.

Dont forget to resolve your other thread.

[medison]

FWIW:

The button will still be visible in design mode. This is great for the developer since it's hard to change something that you can't see. However, if a user opens the file with macros disabled, the button will be there.

If this is something that you don't want then it might be better to delete the button instead of just hiding it.