Command buttons not working when on different workbook

[Alan_CT06]

I have my VBA code and a ActiveX command button which runs the code in one workbook. I need the code to run against data in different workbooks which will have different names.

The code works fine against data in a different workbook if I run it from the VBA application window.

The code can also run when I start it using a command button if I copy the data onto the same worksheet where the command button is

The problem seems to be that the code doesn't start the first time I click on the command button. All that first click does it make the worksheet where the command button is the current worksheet. When I then click the second time, the code runs, but it fails because if is looking for the data on the worksheet where the button is and not in the workbook where the data is.

I tried changing the button property "Take Focus on Click" to False, but it makes no difference.

I've used command buttons before without problem but am at a new company and perhaps there is a different setting on this version of Excel or at this company. We use Microsoft 365 here.

Thanks for any help.

 

[6StringJazzer]

I don't think the command button itself is the issue. Can you share your code here? (I recommend marking code with code tags)

 

[Alan_CT06]

Thank you for responding. The code is very long but even if I use the most simple code, I can't make a command button in one work book execute the code the first time I click the command button.

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.

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 run, 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.

Is this clear?

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

 

[Alan_CT06]

I have found an answer to my question. It has been explained to me that for a command button to work, the workbook holding the command button has to be the active workbook.

And if I want to code to run against another file, that has to be coded into the sub to switch the active workbook status to that other file.