Solved Try to transmit a combination of keys to a module by sendkeys

[Superpat]

Hello, in a module NumForm, I try to send a combinaison of keys to another module or a form, my form here is form_f_equilibrage . The code is +^M (Smart Indent) to indent a module.
It is possible to do that ?
Thank for your help

Sub essaiBoucle1()
        Dim wshshell As Object
        Dim sModuleName As String
        Dim oComponent As Object                                                         'VBComponent
        sModuleName = "form_f_equilibrage"
        'Set wshshell = CreateObject("WScript.Shell")
        'Set oComponent = Application.VBE.ActiveVBProject.VBComponents(sModuleName)
        wshshell.SendKeys "(+^M)", True
        Set wshshell = Nothing
        Set oComponent = Nothing
End Sub

 

[Josef P.]

There is also VBA.SendKeys. But I do not like sendkeys.
Probably the code module must be active.

Is there perhaps also a possibility to do this with a text and then write the whole text back into the code module?
Which add-in are you using?

Another thought:
Why do you have to indent the code afterwards at all?
Is it possible to eliminate this cause?

 

[Superpat]

Do you want to write text into a codemodule?
I think sendkeys are a bad choice for that.
There are methods for that in VBE.

Check out these methods:
CodeModule.Find
CodeModule.InsertLines

No, I numbered a module and I just want to indent a module with Smart Indent.

 

[Josef P.]

Had already corrected my text before.

"I numbered a module" ... means: line numbers are appended?

 

[Superpat]

means: line numbers are appended

Yes.

 

[Josef P.]

Just tried with RubberDuck:

VBE.ActiveVBProject.VBComponents("MyModule").Activate
SendKeys "+^M"

.. works.

 

[Superpat]

Thanks @Josef P. It open forms but it does not.

 

[Josef P.]

Ok, I can recreate this.
It works for a normal code module, but not for a form.
Problem is that the code module of the form does not get the focus.

 

[Superpat]

Thanks, exactly, I try with a module and it work, but not in a form.

 

[Josef P.]

But we will not be defeated so easily:

Const FormName As String = "frmTest"

DoCmd.OpenForm FormName, acDesign
DoCmd.OpenModule "Form_" & FormName
SendKeys "+^M"

 

[Superpat]

All are good. Thanks you very much

 

[Superpat]

I am sorry, I come back. Its different if I call from NumForm to NumForm or I call to Form_f_NumerotationFormModule to NumForm

My code in NumForm, all are right and works perfectly :

Sub essaiNumForm()
        Dim sNomForm As String
        sNomForm = "f_equilibrageEssai"
        SmartFormulaire (sNomForm)
End Sub

Sub essaiNumModule()
        Dim sNomMod As String
        sNomMod = "Inutiles2"
         Debug.Print "SmartModule(sNomMod) : " & sNomMod
        SmartModule (sNomMod)
End Sub

Function SmartFormulaire(sFormName As String)
        Dim wshshell As Object
        Set wshshell = CreateObject("WScript.Shell")
        DoCmd.OpenForm sFormName, acDesign
        Debug.Print "sFormName : " & sFormName
        DoCmd.OpenModule "Form_" & sFormName
        wshshell.SendKeys "(+^M)", True
        DoCmd.Close acForm, sFormName, acSaveYes
        Set wshshell = Nothing
End Function

Function SmartModule(sModuleName As String)
        Dim wshshell As Object
        Set wshshell = CreateObject("WScript.Shell")
        VBE.ActiveVBProject.VBComponents(sModuleName).Activate
        wshshell.SendKeys "(+^M)", True
        Set wshshell = Nothing
End Function

My code in Form_f_NumerotationFormModule, the indent is not working ???

Private Sub btnNumeroter_Click()
        Dim lComptageModules As String
        Dim lComptageFormulaires As String
        Dim lSuppNom As String
        Application.Echo False
        If Me.cmb_NumModules.Enabled = True Then
                lSuppNom = Me.cmb_NumModules.Value
                Call ExportOneModuleOuFormParLigne(Me.cmb_NumModules.Value)
                Call AjouterNumerotation(Me.cmb_NumModules.Value)
                Call createTableModules
                Call ListNumAllModulesDeroulantes(lComptageModules)
                'Debug.Print lComptageModules
                Me.eti_NumNbModules.Caption = lComptageModules
                Me.eti_NumNbModules.Visible = False
                Me.cmb_NumModules.Requery
                Me.cmb_NumModules = cmb_NumModules.ItemData(0)                           'Se positionner sur le premier élément et pas sur du vide
                Me.cmb_NumModules.Enabled = False
                Call createTableFormulaires
                Call ListAllFormulairesDeroulantes(lComptageFormulaires)
                'Debug.Print lComptageFormulaires
                Me.eti_NumNbFormulaires.Caption = lComptageFormulaires
                Me.eti_NumNbFormulaires.Visible = True
                Me.cmb_NumFormulaires.Requery
                Me.cmb_NumFormulaires = cmb_NumFormulaires.ItemData(0)                   'Se positionner sur le premier élément et pas sur du vide
                Me.cmb_NumFormulaires.Enabled = True
                Me.cad_ChoixNum = 2
                Debug.Print "Call SmartModuleNum(lSuppNom) : " & lSuppNom
                Call SmartModule(lSuppNom)
                'Call SmartModule("Essai2")
        Else
                lSuppNom = right(Me.cmb_NumFormulaires.Value, Len(Me.cmb_NumFormulaires.Value) - 5)
                Call ExportOneModuleOuFormParLigne(Me.cmb_NumFormulaires.Value)
                Call AjouterNumerotation(Me.cmb_NumFormulaires.Value)
                Call createTableFormulaires
                Call ListNumAllModulesDeroulantes(lComptageFormulaires)
                'Debug.Print lComptageFormulaires
                Me.eti_NumNbModules.Caption = lComptageModules
                Me.eti_NumNbModules.Visible = False
                Me.cmb_NumModules.Requery
                Me.cmb_NumModules = cmb_NumModules.ItemData(0)                           'Se positionner sur le premier élément et pas sur du vide
                Me.cmb_NumModules.Enabled = False
                Call createTableFormulaires
                Call ListAllFormulairesDeroulantes(lComptageFormulaires)
                Me.eti_NumNbFormulaires.Caption = lComptageFormulaires
                Me.eti_NumNbFormulaires.Visible = True
                Me.cmb_NumFormulaires.Requery
                Me.cmb_NumFormulaires = cmb_NumFormulaires.ItemData(0)                   'Se positionner sur le premier élément et pas sur du vide
                Me.cmb_NumFormulaires.Enabled = True
                Me.cad_ChoixNum = 1
                Debug.Print "Call SmartFormulaireNum(lSuppNom) : " & lSuppNom
                Call SmartFormulaire(lSuppNom)
        End If
        'Debug.Print "lSuppNom : " & lSuppNom
        DoCmd.Close acForm, lSuppNom, acSaveYes
        Application.Echo True
End Sub

 

[ebs17]

Smart Indent offers the possibility via menu to do the formatting for the whole VBA project. This is what I do when I work directly in the VBE. Somehow formatting and viewing the code also has a connection.

What special reason is there to have to do something here by code and module by module or even procedure by procedure?

 

[Josef P.]

Just tested:

Public Sub IdentFormModule(ByVal FormName As String)

    DoCmd.OpenForm FormName, acDesign
    DoCmd.OpenModule "Form_" & FormName

    SendKeys "+^M"
    
End Sub

Code in a form:

Private Sub cmdTest_Click() 'cmdTest = CommandButton

    IdentFormModule "frmTest"
    
End Sub

... works

 

[Superpat]

Hello @Josef P. , I try It, for Indent a module it do. For a form it do not. I create also a button in my form :

Private Sub cmdTest_Click()
' SmartModule "Inutiles2"      'OK
SmartFormulaire "f_EquilibrageEssai"       'Not Ok
End Sub

In the same module as Function SmartFormulaire it works

Sub essaiNumFormulaire()
        Dim sNomForm As String
        sNomForm = "f_equilibrageEssai"
        SmartFormulaire (sNomForm)
End Sub

Function SmartFormulaire(ByVal sFormName As String)
        Dim wshshell As Object
        Set wshshell = CreateObject("WScript.Shell")
        DoCmd.OpenForm sFormName, acDesign
        Debug.Print "sFormName : " & sFormName
        DoCmd.OpenModule "Form_" & sFormName
        wshshell.SendKeys "(+^M)", True
        Set wshshell = Nothing
End Function

Function SmartModule(ByVal sModuleName As String)
        Dim wshshell As Object
        Set wshshell = CreateObject("WScript.Shell")
        VBE.ActiveVBProject.VBComponents(sModuleName).Activate
        wshshell.SendKeys "(+^M)", True
        Set wshshell = Nothing
End Function

 

[Josef P.]

Is the VBE add-in already loaded at this point (before sendkeys)?
VBA add-ins are started only at the 1st switch to the VBA editor (loading the editor), if they are set to load automatically.

Note: I always test with RubberDuck, since I don't have SmartIdent installed.

BTW: I would avoid the problem and make sure already when customizing the code modules that the indentation does not need to be adjusted.

 

[Superpat]

Is the VBE add-in already loaded at this point (before sendkeys)?
VBA add-ins are started only at the 1st switch to the VBA editor (loading the editor), if they are set to load automatically.

Note: I always test with RubberDuck, since I don't have SmartIdent installed.

Yes, the VBE add-in already is loaded.
In my "Sub essaiNumFormulaire()", it never works.
In my Private Sub cmdTest_Click(), I have to click two time for it works. It is very curious.

 

[Josef P.]

Can you provide a test file? Only with the sendkey code.

 

[Superpat]

Yes, I will do (after eaten). Good appétit !

 

[Superpat]

Here he is. Form to open is f_NumerotationFormModules and thanksto try.