How to apply my VBA application to any Excel workbooks?

[AlexExcel2021]

I have created some application on VBA and export it as XLAM addins.
If I open new blank workbooks my application don't accessible. How to apply my application in VBA-code to any workbooks?

 

[Skyybot]

Save your template in the following location on your machine: (change YourUserName to suit your file structure)
C:\Users\"YourUserName"\AppData\Roaming\Microsoft\Excel\XLSTART

 

[AlexExcel2021]

Yes, this possible. But what problem I have? My soft handle events from Sheet1 from my Workbook, I need handle events from user's sheet. Export XLAM or Excel-Start don't solve this problem. My soft stlii handle events from my Sheet instead any user's sheets.

 

[Skyybot]

Try changing your references. Small example:

Sub example()

Dim mywb As Workbook, userwb As Workbook
Dim mysht As Worksheet, usersht As Worksheet
Set mywb = ThisWorkbook 'Depending on where code is stored reference may change
Set userwb = Application.Workbooks.Open("C:\Users\UserName\Folder\File.extension")
Set mysht = mywb.Sheets("SheetName")
Set usersht = userwb.Sheets("SheetName")

End Sub

 

[AlexExcel2021]

Maybe you are right, I have no enough experience to VBA. My soft is handle events of sheet, now I handle events from my test sheet, of course end-user can handle events from their own sheets.

Is my soft will be working in this approach?

 

[Skyybot]

Perhaps I'm misunderstanding your requirements. If you create an Excel Macro Enabled Template and send it to a user, once they open it the VBA will work just the same. The only thing that may change is the File Directory if you're using any GetSaveFilename methods. What is your ultimate end goal?

 

[Jaafar Tribak]

To trap events in the addin for all the workbooks , you will need to use the WithEvents keyword to hook the application events.

Do a search for *WithEvents* to learn more

 

[AlexExcel2021]

@Skyybot , My finally goal is take software I developed to end-user. Maybe I need installation of my VBA-code. Main my problem that current VBA algorithm working only to concrete sheet, because than algorithm need to create Combobox dynamically and than handle events from dynamically created Combobox. And algorithm started by selection-change event of current sheet. I don't know how to move this algorithm from Sheet to Workbook or module, this is first point. For example. I meed to create Combobox with name "ManCombo" dynamically than handle events ManCombo_Change. This algorithm working only Sheet I develop software, but I need to place this code to end-user sheet. And I don't know how to move events from my Sheet1 to end-user Sheet.
And second point. I can not understand how to create installation package of my VBA code to end-user computer, correct way to transfer code to end-user.

@Jaafar Tribak , it's possible you are right, but WithEvents keyword is static declaration of Combobox. My Code has no static element placed to Excel form at all. My algorithm is placing Combobox to page dynamically than fill it something data what I need to calculate by my code. One data I need to calculate to column "A", another data I need to calculate to column "B". So, I have no static ActiveX control at all, any Combobox is creating dynamically in various place on sheet (now in my own sheet, but finally this will be end-user sheet of course). I'm sorry, I don't understand how to static definition "WithEvents" related to dynamically created Combobox. Maybe, current worksheet it's possible to declare with WithEvents in Module (in order to capture events SelectionChange from end-user Sheet)?

 

[AlexExcel2021]

This is example of my algorithm - there are nothing static element on form at all, any elements appear dynamically depends on place, for example. And now this algorithm working on my own Sheet1, but finally can be working in end-user Sheet.

 

[Jaafar Tribak]

I am not sure I understand what you are trying to achieve.

If you just want to add a combobox to a worksheet dynamically, you can use something as follows:

The following code performs the following actions :

1- Adds a combobox dynamically to any worksheet in any workbook whenever you select a cell from column A.
2- The newly created combobox will be located on top of the selected cell in column A
3- The combobbox will be populated and its events will be hooked (Try selecting a value from the combobox to see the change event fire)
4- When you select a new cell in Column A, the previous combobox will be deleted and a new one will be created on top of the new activecell in column A.

If you you add the code to an addin, it will be available throughout the entire application whenever you start excel.

Workbook Demo:
AddComboBoxDynamicallyToSheet.xlsm


Code goes in the ThisWorkbook Module:

Option Explicit

Private WithEvents xlApp As Application
Private WithEvents Combbox As MSForms.ComboBox

#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDest As Any, pSrc As Any, ByVal ByteLen As LongPtr)
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDest As Any, pSrc As Any, ByVal ByteLen As LongPtr)
#End If

Private Sub Workbook_Open()
    Set xlApp = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.Names("PrveSh").Delete
End Sub

Private Sub Combbox_Change()
    MsgBox "You selected :  " & Combbox.Value
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    With Application
        If Target.Column = 1& Then
            On Error Resume Next
                .Sheets([PrveSh]).OLEObjects("Dynamic_ComboBox").Delete
                .Names("PrveSh").Delete
            On Error GoTo 0
            Set Combbox = Sh.OLEObjects.Add( _
                          ClassType:="Forms.ComboBox.1", _
                          Left:=Target.Left, _
                          Top:=Target.Cells(1).Top, _
                          Width:=150&, Height:=20&).Object
            Combbox.Name = "Dynamic_ComboBox"
            .Names.Add "PrveSh", Sh.Name, False
            .OnTime Now, "'" & Me.CodeName & ".Populate_And_Hook_ComboBox " & ObjPtr(Combbox) & "'"
        End If
    End With

End Sub

Private Sub Populate_And_Hook_ComboBox(ByVal Ptr As LongPtr)
    #If Win64 Then
        Const PTR_SIZE = 8&
    #Else
        Const PTR_SIZE = 4&
    #End If
    Dim oTmpObj As Object, i As Long

    Set xlApp = Application
    Call CopyMemory(oTmpObj, Ptr, PTR_SIZE)
    Set Combbox = oTmpObj
    Call CopyMemory(oTmpObj, 0&, PTR_SIZE)
    For i = 1 To 10&
        Combbox.AddItem i
    Next i
  
End Sub