Open worksheet and create form button if it doesn't exist

Byron · Jul 18, 2003

Upon opening a Excel Workbook I need to display a form button that will run a macro if the form button does not exist.

I know how to assign the macro to the form button. But how do I make sure it exist and is visible upon opening the workbook?

Ken Puls · Jul 18, 2003

Re: Open worksheet and create form button if it doesn't exis

Hi there,

First things first, I need to give credit to Nate Oliver for writing 90% of this code (and teaching me how it all works.) I've modified it to fit what you need...

Code:

Sub Add_Command_Buttons()
'Macro created 07/06/03 by Nate Oliver, modified 07/14/03 by Ken Puls
'Macro purpose: To create acommand buttons on the worksheet if necessary

'SECTION 1
'Declare required variables and working environment
    Dim cl As Range, Ctrl As OLEObject
    Application.ScreenUpdating = False

'SECTION 2
'Change name of button, if it exists.  If not, error will occur and send to errortrap
    On Error GoTo Createbutton
        Set Ctrl = ActiveSheet.OLEObjects("CommandButton1")
    On Error GoTo 0
        Application.ScreenUpdating = True
    Exit Sub

'SECTION 3
'Create button if necessary (this code will not fire if any button exists)
Createbutton:
    Set cl = Range("A1")
    Set Ctrl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Left:=cl.Left + 1, Top:=cl.Top + 1, Width:=cl.Width * 2, Height:=cl.Height * 2)
    With Ctrl
        .Name = "CommandButton1" 'you can call it anything you like here
        .Placement = xlMove
        .PrintObject = False
        With .Object   'sets what is displayed on the button
            .Caption = "Click me!"
            .Enabled = True
            .Font.Name = "Times New Roman"
            .Font.Size = 10
            .Font.Bold = True
            .TakeFocusOnClick = False
            .WordWrap = True
        End With
    End With

Application.ScreenUpdating = True
End Sub

You would want to include this code in the Workbook_Open event.

FYI, this only sets up the button, it does not add the code. That is something I haven't had time to try yet, but I did find a MKBA on how to do this: http://support.microsoft.com/default.aspx?scid=kb;en-us;194611

HTH,

NateO · Jul 19, 2003

Re: Open worksheet and create form button if it doesn't exis

Hmmm, not sure why you wouldn't know or not whether a button was there, but not nice proc. Ken.

Note this ActiveX versus a form button, better in my estimation. Also note, one enters the twilight zone regarding ActiveX controls and VBA, there is some fairly odd business resulting from this, and it hasn't been fixed in XP. Never-the-less...

FYI, this only sets up the button, it does not add the code. That is something I haven't had time to try yet, but I did find a MKBA on how to do this: http://support.microsoft.com/default.aspx?scid=kb;en-us;194611

Let's add to it so that it adds a worksheet event, tieing a procedure to the button:

Sub Add_Command_Buttons()
'Macro created 07/06/03 by Nate Oliver, modified 07/14/03 by Ken Puls _
 Modified 7/19/03 by Nate Oliver :D

'Macro purpose: To create acommand buttons on the worksheet if necessary

'SECTION 1
'Declare required variables and working environment
 Dim cl As Range, Ctrl As OLEObject
 Application.ScreenUpdating = False

'SECTION 2
'Change name of button, if it exists. If not, error will occur and send to errortrap
 On Error GoTo Createbutton
 Set Ctrl = ActiveSheet.OLEObjects("CommandButton1")
 On Error GoTo 0
 Application.ScreenUpdating = True
 Exit Sub

'SECTION 3
'Create button if necessary (this code will not fire if any button exists)
Createbutton:
 Set cl = Range("A1")
 Set Ctrl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
 Left:=cl.Left + 1, Top:=cl.Top + 1, Width:=cl.Width * 2, Height:=cl.Height * 2)
 With Ctrl
 .Name = "CommandButton1" 'you can call it anything you like here
 .Placement = xlMove
 .PrintObject = False
 With .Object 'sets what is displayed on the button
 .Caption = "Click me!"
 .Enabled = True
 .Font.Name = "Times New Roman"
 .Font.Size = 10
 .Font.Bold = True
 .TakeFocusOnClick = False
 .WordWrap = True
 End With
 End With

'SECTION 4
'Tie a procedure to the ActiveX Control, Based on Chip Pearson's Example

 With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
 LineNum = .CountOfLines + 1
 .InsertLines LineNum, _
 "Private Sub CommandButton1_Click()" & vbLf & _
 " Msgbox ""Here is the new procedure"" " & vbLf & _
 "End Sub"
 End With

Application.ScreenUpdating = True
End Sub

Obviously modify the string to suit your needs. You might want to add a worksheet object to define the sheet you're working with, this code will fire on the activesheet, by design.

Ken Puls · Jul 24, 2003

Re: Open worksheet and create form button if it doesn't exis

Hi Nate,

I just tried to use your modified code of my modified code of your unmodified code...

After dim'ng LineNum as an integer, (it didn't like it without,) I ran the code, but received an error that kind of threw me off. Can you tell me how to deal with this one?

Run Time Error '1004'
Programmatic access to Visual Basic Project is not trusted.

(Maybe my computer is just smarter than I thought and knows I'm about to do something dangerous!)

Thanks,

NateO · Jul 24, 2003

Re: Open worksheet and create form button if it doesn't exis

Ah yes, I forgot to explicitly state that variable, how silly of me, sounds like you have option explicit going in your workbook.

In any case, I imagine you're using XP. Go into Tools->Macro->Security. There should be something there about accessing the VBE. I don't have XP in front of me at the moment and am at a bit of loss to recall what it exactly looks like, I think it's obvious though.

Ken Puls · Jul 24, 2003

Re: Open worksheet and create form button if it doesn't exis

Thank you!

Not only does that let me run the afformentioned code, but it also allows me to run the VB HTML maker and Colo's HTMLMaker runs without telling me to manually set some VBIDE thing!

You've cleared up 3 problems in one shot!

ray:

For the record, I do use option explicit in all my workbooks (that way I get closer to understanding what I'm trying to accomplish!)

Also, the VBE thing is obvious when you know where to look. It's on the "Trusted Sources" tab of the "Macro Security" settings.

Thanks again!

NateO · Jul 24, 2003

Re: Open worksheet and create form button if it doesn't exis

kpuls said:
Thank you!

You've cleared up 3 problems in one shot! ray:

For the record, I do use option explicit in all my workbooks (that way I get closer to understanding what I'm trying to accomplish!)

You're welcome! Phew, I remembered it being apparent once you were in the correct dialog.

Re: O.E., good habbit. I do it on a case by case basis, but I should all the time, bad habbits die hard.

Have a good one eh.

judas · Mar 4, 2009

Re: Open worksheet and create form button if it doesn't exis

In Excel 2007 the road to the setting is:

Excel Options >> Trust Center >> Trust Center Settings >> Macro Settings >> Check "Trust access to the VBA Project Object Model"

Open worksheet and create form button if it doesn't exist

Byron

New Member

Excel Facts

Ken Puls

Active Member

NateO

Legend

Ken Puls

Active Member

NateO

Legend

Ken Puls

Active Member

NateO

Legend

judas

Board Regular

Similar threads

Forum statistics

Share this page

Open worksheet and create form button if it doesn't exist

New Member

Excel Facts

Active Member

Legend

Active Member

Legend

Active Member

Legend

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock