Automatically activate macro when data is inputed instead of manually activating macro

[sonny.thind]

I have a spreadsheet(tool.xls) I have created for my associates which helps them to send emails to different managers in the cities.

all they do is input the different ID# into column B and then click the command button "run" which activates macro "deter" that transposes the emails with a ";" into cell "output"

I am trying to eliminate the need to use the "run" command and directly activate the macro as soon as the user inputs data into the column B which will automatically transposes the addresses with the ";"

I am just trying to figure out if there is a way to do this with vba code in the existing macro or use a macro to activate another macro??

[abousetta]

Hi,

I clean up your code a bit along the way:

ThisWorkbook:

Option Explicit

Dim cControl As CommandBarButton

Private Sub Workbook_AddinInstall()
    
    On Error Resume Next 'Just in case
    
    With Application
        .CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete 'Delete any existing menu item that may have been left.
        Set cControl = .CommandBars("Worksheet Menu Bar").Controls.Add    'Add the new menu item and Set a CommandBarButton Variable to it
    End With
    
      ' Work with the Variable
        With cControl
            .Caption = "Super Code"
            .Style = msoButtonCaption
            .OnAction = "Module1"            'Macro stored in a Standard Module
        End With
    
    On Error GoTo 0
End Sub

Private Sub Workbook_AddinUninstall()
    On Error Resume Next 'In case it has already gone.
        Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Worksheets("Main form").Range("B2:B220")) Is Nothing Then Mail
End Sub

Module1:

Sub Deter()
Dim vData As Range, buf As String
    
    For Each vData In Range("C:C").SpecialCells(xlFormulas, 2)
        buf = buf & ";" & vData
    Next vData

    Range("E8").Value = Mid(buf, 2, Len(buf))

End Sub

Sub Clear()
    Range("B2:B22", "E8").ClearContents
    Range("B2").Select
End Sub

Sub Mail()
    Dim OutlookApp As Object
    Dim Mess As Object, Recip
    Recip = Range("E8")
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Range("E10")
        .Body = Range("E12")
        .To = Recip
        .Display
    End With
End Sub

Sub Clearing()
    Range("B2, E8, E10, E12:I17").ClearContents
    Range("B2").Select
End Sub

Sub Clearingd()
    Range("B2:B220, E8, E10, E12:I17").ClearContents
    Range("B2").Select
End Sub

Test and let me know if you need any tweaks.

[sonny.thind]

Thanks for your response but I am still having problems with the Addin feature--I just cant get it to work ,
Also
I tried the code only with this on "this worksheet' but still doesn't work how I want it to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Worksheets("Main form").Range("B2:B220")) Is Nothing Then Mail
End Sub

I just want the 'determacro to run' as soon as the user types the ID# so they dont have to use the Run command.
Not sure if I am saying this correctly ,please correct me if I am wrong..

Ok so I figured out this part after doing some more testing.. and I just changed the [CODE]Private Sub Workbook_SheetChange[CODE] to [CODE]Private Sub Workbook_SelectionChange[CODE] and [CODE]If Not Intersect(Target, Worksheets("Main form").Range("B2:B220")) Is Nothing Then Mail[CODE] to the deter macro instead of the mail macro. and it works.

BUT

Thanks for your response but I am still having problems with the Addin feature--I just cant get it to work ,

I am still having issues with it just cant figure it out.

[abousetta]

Hi Sonny,

I don't work much with addins so I can't help much in that area. Maybe someone else with more experience will pick up this second part of the problem. If the thread goes cold then you might want to start a thread with just issue.

Good luck.

[sonny.thind]

No problem I thank you for your help ..