Command Button doesn't work until its Workbook is Active

Question

I am unable to make a command button that is located on one workbook execute the code to affect data on a different workbook.

The problem occurs it seems because to activate the Command Button to kick off the code, the Workbook where the Command Button is located has to be active. And then the code runs against the workbook where the Command Button is, not on the other workbook where my data is that I want to manipulate.

For example in workbook named "Workbook A" in Module 1, I created this sub.

Sub Test1()
Range("A5").Value = 6
End Sub

Then in Workbook A, I created a Command Button, and created the code for the button,

Private Sub CommandButton1_Click()
Test1
End Sub

Now I would like to use my sub Test1 to enter "6" in Cell B5 in some other workbook that I happen to be working on at the time. This would not be a specific workbook, just whatever workbook I have active.

I make that other workbook active, then click on the Command Button on Workbook A to kick off the Sub Test1, and nothing happens. The only thing that seems to happen on that first click is that Workbook A becomes the active workbook.

Then if I click the button again, the sub runs, and enters "6" in cell A5 in Workbook A. And this isn't what I want because I want to enter 6 in the other workbook I am working on.

To say another way, if I am working in another workbook, and I click the command button located on Workbook A, I want that first click to kick off my sub, not make WorkbookA the active workbook.

Answer 1

You may try this one.

=========================

Sub GetWorkbookName()

Dim wb As Workbook

For Each wb In Application.Workbooks

    If wb.Name <> ThisWorkbook.Name Then

        MsgBox wb.Name

        Workbooks(wb.Name).Activate

        Range("A5").Value = 6

    End If

Next wb

End Sub

=======================

Answer 2

Hi Alan

I would suggest storing the macro in your Personal Macro Workbook file. The following video (1 of 4) will illustrate my point.

Do let me know if you need more help

Regards

Jeovany

https://youtu.be/rD2-mSbTuL0?list=PLKbOx3gUV_E87DSMLQFKdYy2XxwrcBA19