Create Event for every Command Button

[Zigzag]

Hi All,

I am looking for help to create some code that will run through an application and add an Event VBA code for every command button found.

The code I wish to add is identical for each Command button.

I've never done this before, so am not sure where to start.

Could someone please point me in the right direction.

Garry

 

[ChrisO]

G’day Garry.

I think you may need to supply more information before anyone will be able to help.

Regards,
Chris.

 

[Zigzag]

Cheers Chris,

But its just like the post says,

I have just picked up a DB that a client wants a face lift on.
One of the changes she wants is something she has seen on other applications which is that the cursor turns into a hand when over a command button.

"Not a problem"

However, I am trying to find an easy way of going through every form (Over 70) and create an "On mouse Move" event with the following as VBA Code for each command button.

Call MouseCursor(32649)

So I would like to create some VBA that will run through each control and create the event if it is a Command Button.

Hope this has helped explain a little more.

Garry

 

[ChrisO]

Perhaps something like this: -

Sub SetMouseMove()
    Dim ctl As Control

    With CurrentDb.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type = -32768;", 2)
        Do Until .EOF
            DoCmd.OpenForm !Name, acDesign
            For Each ctl In Forms(!Name)
                If ctl.ControlType = acCommandButton Then
                    ctl.OnMouseMove = "=MouseCursor(32649)"
                End If
            Next ctl
            DoCmd.Close acForm, !Name, acSaveYes
            .MoveNext
        Loop
        .Close
    End With
    
End Sub

Hope that helps.

Regards,
Chris.

 

[Zigzag]

Hey Chris,

That worked like a dream, I was focusing on trying to create an event procedure for it instead of just calling the module straight out. Great!!!!!!!!

Just to satisfy my own curiosity, is it possible to create an actual event producure in a simillar manner as you have demonstrated?

Thanks for you solution, it has just saved me lots of work.

Garry

 

[ChrisO]

Well it depends on how similar ‘similar’ means.

In an MDB file it is possible to open a module and modify the source code but it can become rather complex with the string manipulation.

There is an A97 demo attached if you would like to play with it.

Personally, I think it is best left alone except for practicing string manipulation.

Regards,
Chris.

 

[Zigzag]

Thanx for the example Chris,

It took me a couple of attempts but I nailed it in the end, here is my working example using your first sugestion with some alterations.

Sub SetMouseMove()
    Dim ctl As Control
Dim ControlName As String
    With CurrentDb.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type = -32768;", 2)
        Do Until .EOF
            DoCmd.OpenForm !Name, acDesign
            For Each ctl In Forms(!Name)
                If ctl.ControlType = acCommandButton Then
                    'ctl.OnMouseMove = "=MouseCursor(32649)"
                    
                    If IsNull(ctl.OnMouseMove) Or (ctl.OnMouseMove) = "" Then
                    ctl.OnMouseMove = "[Event Procedure]"
                    Set mdlThisFormsModule = Forms(!Name).Module
                    'replace spaces with underscore
                    ControlName = Replace((ctl.Name), " ", "_")
                    
                    'Create the code for the mouse over event procedure
        strSub = "Private Sub " & ControlName & "_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)" & vbNewLine & _
        "Call MouseCursor(32649)" & vbNewLine & "End Sub"
    
        '   Create the event.
        mdlThisFormsModule.InsertLines mdlThisFormsModule.CountOfLines + 1, strSub
            End If
          
                End If
            Next ctl
            DoCmd.Close acForm, !Name, acSaveYes
            .MoveNext
        Loop
        .Close
    End With
    
End Sub

I will however be sticking to your first sugestion, but its nice to know for the future that I can change code using code.

Once again Chris thanx for your help.

Garry