Change cell value from an Add-In

**gdaniels** · 04-27-2017, 02:41 PM

I have a macro that runs OK when placed in a worksheet. This code will do the following in cell A1 of the “testx” worksheet: If cell A1 is empty when clicked, an “x” will become the value of cell A1, and if the value of cell A1 is “x” when clicked, the value of cell A1 will become “” (empty).

I need to have this work from an Add-In so that when any workbook that references the Add-In and contains the worksheet “testx” cell A1 will either be empty or contain an “x” when clicked.

I’ve checked the info at http://www.cpearson.com/excel/AppEvent.aspx, Pearson Software Consulting, and I did get the example to work, but I cannot get my code to work when I follow the example steps. I appreciate any help available.

My code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Address = Worksheets("testx").Range("A1").Address Then
Select Case Target.Value
Case "x"
Target.Value = ""
Case Else
Target.Value = "x"
End Select
End If

Application.EnableEvents = True
End Sub

**cytop** · 04-28-2017, 05:29 AM

runs OK when placed in a worksheet

Of course. The event is local to the worksheet class so the event is fired, but only for that worksheet, There is a corresponding event in the ThisWorkbook class which will respond to events on any worksheet in that workbook.

So the events are scoped to the Workbook or worksheet. An Add-in cannot respond to events in another workbook (generally speaking) so your Add-in knows nothing about the selection change on the worksheet.

General answer as the comment

workbook that references the Add-In

is not strictly correct. No workbook 'references' an add-in, they are simply 2 workbooks loaded in the same instance of Excel and each knows nothing about the other to start with.

It is possible to have an Add-in respond to events in another workbook, but you need some way to set that up. I've no idea why you want the code in an Add-in when it seems to refer specifically to one particular worksheet (testx) so perhaps you might explain the logic behind this.

For info, between your posting this and me replying the Pearson page you linked replies with "The resource you are looking for has been removed, had its name changed, or is temporarily unavailable."

**gdaniels** · 04-28-2017, 08:39 AM

Cytop, thanks for the reply. The reason I want the code in an Add-In is because we have users that all have the same Excel file as a "template" for their work which includes the "testx" worksheet. I cannot place the code in the "testx" worksheet because the VBA code for it is password-protected and our Company will not allow me access. However, I can include an Add-In to run my code. As far as the Pearson site, try http://www.cpearson.com/excel/AppEvent.aspx. I think the link I previously provided included a comma at the end of the URL which was giving the error message.

**cytop** · 04-28-2017, 09:42 AM

OK, try the attached.

Some assumptions have been made. Obviously the real worksheet is not called 'TestX' but that will do to start with. Also, for simplicity at first, you must have the sheet 'TestX' as the active sheet when you load the add-in. In this version you have to load the Add-in rather than have it loaded and monitoring for a sheet called 'TestX' every time a workbook is loaded (That is probably where this will end up...).

While your code has been rewritten slightly it has the same functionality and essentially just flips the value of cell A1 on TextX between 'X' and blank each time you select the cell, moving to A2 after.

Just something to play with to see how you trap events in another worksheet as detailed on CP's page.

The forum does not allow uploads of XLAM files so it has been renamed to testxcatcher.csv. I think you'll realise what you have to do here

**gdaniels** · 05-01-2017, 07:15 AM

Thanks again, Cytop. For your benefit and the forum members, I am posting this solution. In the "ThisWorkbook" module of the Add-In, this code was placed:

Please Login or Register  to view this content.

In the Class Module named "CExcelEvents" of the Add-In, this code was placed:

Please Login or Register  to view this content.

After entering the code, you may have to save and close Excel, then open it.

Change cell value from an Add-In

LinkBack

Thread Tools

Rate This Thread

Display

Change cell value from an Add-In

Re: Change cell value from an Add-In

Re: Change cell value from an Add-In

Re: Change cell value from an Add-In

[Solved] Re: Change cell value from an Add-In

Thread Information

Users Browsing this Thread

Similar Threads

Worksheet change event with multiple column cell value change

[SOLVED] Change On_Open macro to a cell change in a range of cells

If range value change, active cell equal date and time of change.

auto change cell formula on condition of worksheet change in other cells

Change Event doesn't change until I return to the Target Cell

[SOLVED] making copied cells change with change in original cell

[SOLVED] Cell value change to trigger macro (worksheet change event?)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions