Can I create buttons using VB code? 1

[cc5305]

Hi, does anyone know if I can create command buttons using VB Code instead of drawing the buttons in design view? Thanks in advance.

 

[jebry]

Hi!

You can create buttons from code by using the CreateControl function. But the form you want to create the control on needs to be in design view for it to work so you can run the code from the same form that you want to create the control on. I hope that made sense!

hth Jeff Bridgham
bridgham@purdue.edu

 

[logius]

Do you want to add this to a sheet or a form? ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[cc5305]

I want to add command buttons onto a form in my Access db. Thanks.

 

[logius]

See if this helps you.

Dim NewButton As MSForms.CommandButton

'   Add a CommandButton
    Set NewButton = MyForm.Designer.Controls _
      .Add("forms.CommandButton.1")
    With NewButton
        .Caption = "Click Me"
        .Left = 60
        .Top = 40
    End With
'   Add an event-hander sub for the CommandButton
    With MyForm.CodeModule
'   ** Delete This: TextLocation = .CreateEventProc("Click","CommandButton1")

'   ** Add/change next 5 lines
'   This code adds the commands/event handlers to the form
        X = .CountOfLines
        .InsertLines X + 1, "Sub CommandButton1_Click()"
        .InsertLines X + 2, "MsgBox ""Hello!"""
        .InsertLines X + 3, "Unload Me"
        .InsertLines X + 4, "End Sub"
    End With

----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[cc5305]

thanks logius. Just one question, do I have to Dim the X that is set to the .CountOfLines? Thanks again.

 

[logius]

Only if you have the

Option Explicit

at the top of the code. If you want to, it's just an integer value. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[cc5305]

logius, I tried the code you gave me. Now I know I'm missing something, but don't know what (since i'm not an expert in this area). I get the error of 'User defined-type not found' and it highlights the declaration line for the command button. Is there a component I need to add? Keep in mind that I'm trying to do all these in VB Editor in Access, not in VB application. Thanks.

 

[logius]

Aw, darn it. I'm sorry, yes, there's an add-in you need to include. It's the Micrsoft Visual Basic for Applications Extensibility X.X (mine was 5.3 when I wrote this). That should give you access to the MSForms data type. If you have any problems with this email me. Better email me twice, though, because for some reason hotmail has been dropping emails.

----------------------------------------
If you are reading this, then you have read too far...

 

[cc5305]

I've gone as far as creating the button. Haven't been able to figure out how to create the event handler yet. For some reason, it doesn't recognize the line:

With MyForm.CodeModule

even if i declare MyForm as Form and set it to Me.Form.

Also, can I change the name of the button to something I desire? Thanks?

 

[logius]

I thought this would work with a predefined UserForm. Apparently this isn't the case, so you need to add in this code before the rest of it will work.

Dim MyForm As Object
Dim FormName As String

'   Create the UserForm
Set MyForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    
'Set Properties for MyForm
With MyForm
    .Properties("Caption") = "My Form"
    .Properties("Width") = 200
    .Properties("Height") = 100
      'Set all properties to your wishes
End With
FormName = MyForm.Name
.
.
.
'To disply the form
VBA.UserForms.Add(FormName).Show

As far as changing the name of the CommandButton to something more reasonable... No! Just kidding. All you have to do is set the

.Name

property to whatever you want and remember to refer to the Event Handlers according to it's proper name (change

Sub CommandButton1_Click()

to

Sub MyButton_Click()

or whatever you decide to call it). For a full listing of the properties, just create a dummy Userform and check the properties listing for all the available options. This works well to see what Event Handlers you can use also.
----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com